SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

Ok for some reason i cant do this in Foxpro

I need to select the data from a table that doesnt exists in another for example, i want to filter the users that came on January and only in January even if they did come back later (the users that came back later should be filtered)
Ive tried with EXISTS and ANY but i cant do this :{{{{

# ? Sep 5, 2008 19:26

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 02:52

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

Stupid question thread, perfect, I'm an idiot so this will work.

I have an access database I created. The whole intent was to get it hosted as a webpage for a small group of users to access. I am familiar with how the logic of databases work but overall am a novice in this field. This is my first attempt to create a database and think I did a 'modest' job but definitely know I am still only utilizing 2% of the potential for it. That being said, I'm trying to find the simplest way to get this accomplished. At this point glitz and glamor is a distant second to function.

I picked up a hosting plan from 1and1.com under the impression that I could simply 'publish' the database and be on my way. As always with all things tech it doesn't seem to be working out that way. Their recommendations on what to do can be found here (http://faq.1and1.com/scripting_languages_supported/access_database/ ) and from the looks of it I would need to create several .html pages to run the queries I want. Is my understanding correct on this? Not knowing SQL this is starting to look a bit daunting as I wanted to 'publish' and have my login screen I created pop up and function mostly like it does locally.

Please spare a newb some help and point me in the right direction for the most simple method to accomplish my task. Did I pick the wrong provider? Is there another piece of software (gasp Frontpage?) that can help tie this all together?

Thanks!

# ? Sep 10, 2008 14:29

moww: Jan 16, 2005; Can Type the Hype and Post the Most.

I don't really know what the person above me is trying to do, but I'll say what I can about it in hopes of making my question respectable...

It sounds like you are going to need to do some research on web scripting. http://www.w3schools.com/PHP/php_db_odbc.asp might be a good place to start. Are you trying to get one of your Access forms to become a web form? If so, you will need to reprogram the form using a web scripting language (I think).

Anyway, my question is hopefully an easy one to answer. I need to transfer data from an Access database into an MSSQL one. I've gotten the Access tables into my MSSQL database, but now I need to pick data from the imported tables and move it into an existing table.

For instance:
tbl_old has columns (id, name, commission, new_id)
tbl_new has columns (new_id, new_name, commission)

new_id is a column that matches the rows in tbl_old with the associated rows in tbl_new. This might be still confusing, but here's the query I'm trying to run to accomplish this:

code:

UPDATE		tbl_old
SET		tbl_old.commission = tbl_new.commission
FROM		tbl_old
INNER JOIN	tbl_new
	ON	tbl_old.new_id = tbl_new.id

According to the error I receive on this query, the FROM clause is not allowed in this context... I've tried some other queries that seemed to do the equivalent thing, but since they didn't work I only posted the one that seems the most appropriate. Any ideas?

Edit: Moments after posting this I figured out the solution (of course). For those curious, the proper SQL is:

code:

UPDATE		tbl_old
SET		tbl_old.commission = tbl_new.commission
FROM		tbl_old, tbl_new
WHERE		tbl_old.new_id = tbl_new.id

moww fucked around with this message at 21:55 on Sep 11, 2008

# ? Sep 10, 2008 17:23

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

moww posted:

I don't really know what the person above me is trying to do, but I'll say what I can about it in hopes of making my question respectable...

It sounds like you are going to need to do some research on web scripting. http://www.w3schools.com/PHP/php_db_odbc.asp might be a good place to start. Are you trying to get one of your Access forms to become a web form? If so, you will need to reprogram the form using a web scripting language (I think).

Thanks for the reply. I suppose I am trying to recreate what I believed to be a very turnkey solution that many companies do. Have a database created, then publish it on their IIS server for the employees to access. From my understand, it is as easy as clicking 'Publish' and from the research I have found I would need to code each query as it's own web page, which is not what I had originally thought would have been the case.

To that effect, I am seeing many more references to having Frontpage (now expression web?) do this all for you so I am looking down that route. I would love to learn how to program properly to get this up and running, but currently lack the skill set so was hoping to replicate the 'ease' of the corporate environment on the internet.

# ? Sep 10, 2008 17:27

moww: Jan 16, 2005; Can Type the Hype and Post the Most.

TraderStav posted:

Thanks for the reply. I suppose I am trying to recreate what I believed to be a very turnkey solution that many companies do. Have a database created, then publish it on their IIS server for the employees to access. From my understand, it is as easy as clicking 'Publish' and from the research I have found I would need to code each query as it's own web page, which is not what I had originally thought would have been the case.

To that effect, I am seeing many more references to having Frontpage (now expression web?) do this all for you so I am looking down that route. I would love to learn how to program properly to get this up and running, but currently lack the skill set so was hoping to replicate the 'ease' of the corporate environment on the internet.

Somebody could come in here and give you a super easy solution, but as far as I know reprogramming your forms is the only way. If you already have your tables in place and you are comfortable writing your own SQL, learning how to program your forms with a simple scripting language like PHP should be relatively easy. If you were able to dedicate a week to learning PHP, I believe you would become qualified to do what I think you're trying to do.

# ? Sep 10, 2008 17:36

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

moww posted:

Somebody could come in here and give you a super easy solution, but as far as I know reprogramming your forms is the only way. If you already have your tables in place and you are comfortable writing your own SQL, learning how to program your forms with a simple scripting language like PHP should be relatively easy. If you were able to dedicate a week to learning PHP, I believe you would become qualified to do what I think you're trying to do.

I am not comfortable writing my own SQL. However, access does generate it's own (efficient or not, it seems to work!), perhaps I could copy/paste that into an acceptable PHP format. I'll look into this as well. Thanks a lot for your responses.

# ? Sep 10, 2008 17:41

LightI3ulb: Oct 28, 2006; Standard pleasure model.

I want to build a query that will use three tables, and I know the speed of each table individually, so I want to maximize the speed with the joins. That being said, does the order of events in the query have an effect on which one is completed first?

So, lets say table A is super fast, B is not so fast, and C is dead slow. I want A to execute first and knock out as many rows as possible before it tries to join with B, and then B to C.

As an example:
select * from A a, B b, C c where a.this > # and a.date > curdate() and a.element=b.element and b.id=c.id

Would this execute in the order I want it to?

# ? Sep 10, 2008 18:29

var1ety: Jul 26, 2004

LightI3ulb posted:

I want to build a query that will use three tables, and I know the speed of each table individually, so I want to maximize the speed with the joins. That being said, does the order of events in the query have an effect on which one is completed first?

So, lets say table A is super fast, B is not so fast, and C is dead slow. I want A to execute first and knock out as many rows as possible before it tries to join with B, and then B to C.

As an example:
select * from A a, B b, C c where a.this > # and a.date > curdate() and a.element=b.element and b.id=c.id

Would this execute in the order I want it to?

It depends on your database vendor and version. Many modern databases leverage index and table statistics to generate their own query plan for answering your question in the way they believe will be the most efficient, such that the order in which you specify your tables and predicates does not matter.

# ? Sep 10, 2008 18:51

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

TraderStav posted:

I am not comfortable writing my own SQL. However, access does generate it's own (efficient or not, it seems to work!), perhaps I could copy/paste that into an acceptable PHP format. I'll look into this as well. Thanks a lot for your responses.

SQL is actually pretty simple. Also, Access is really good at taking what should be simple easy to read SQL and making it complex, unreadable, and uneditable.

I suggest reading up on SQL and then get an understanding of third normal form. I think that with about 8 hours of messing around creating tables and writing SQL statements you will surprise yourself at what you can accomplish.

# ? Sep 11, 2008 13:51

Squashy Nipples: Aug 18, 2007

Begby posted:

SQL is actually pretty simple. Also, Access is really good at taking what should be simple easy to read SQL and making it complex, unreadable, and uneditable.

I suggest reading up on SQL and then get an understanding of third normal form. I think that with about 8 hours of messing around creating tables and writing SQL statements you will surprise yourself at what you can accomplish.

Quoted for truth. The sooner you can start writing free-hand SQL the better off you will be... People who get too reliant on the Access Query builder sometimes have a hard time transitioning over.

Although, I have to say, at first I would avoid table manipulation with SQL, and just use the GUI to make, drop and modify your tables. Just concentrate on getting the Selects and joins down.

I like "Sams Teach Yourself SQL in 10 Minutes". Its a fairly quick read, and I still keep it around for reference.

# ? Sep 11, 2008 15:16

Zoracle Zed: Jul 10, 2001

moww posted:

Edit: Moments after posting this I figured out the solution (of course). For those curious, the proper SQL is:
code:
UPDATE		tbl_old
SET		tbl_old.commission = tbl_new.commission
FROM		tbl_old, tbl_new
WHERE		tbl_old.spid = tbl_new.id

I'm surprised that works, I had no idea you could use a FROM clause in an update statement.

Just for the record, you also should be able to write:

code:

UPDATE  tbl_old INNER JOIN tbl_new
          ON tbl_old.new_id = tbl_new.id
SET	tbl_old.commission = tbl_new.commission

# ? Sep 11, 2008 18:40

Just-In-Timeberlake: Aug 18, 2003

I'll try to make it clear as to what I want to do here. I want to get a result set of all the stores in our system, with their total sales for each month for 2008. This is what I have so far

code:

SELECT DISTINCT STORE_NUMBER,
                (SELECT SUM(INVOICE.INVOICE_TOTAL_AMOUNT)  AS EXPR1
                 FROM   INVOICE
                        INNER JOIN SITE
                          ON INVOICE.SITEID = SITE.SITEID
                 WHERE  (MONTH(INVOICE.INVOICE_DATE) = 1)
                        AND (YEAR(INVOICE.INVOICE_DATE) = 2008)) AS JANUARY08,
                (SELECT SUM(INVOICE_1.INVOICE_TOTAL_AMOUNT)  AS EXPR2
                 FROM   INVOICE AS INVOICE_1
                        INNER JOIN SITE AS SITE_2
                          ON INVOICE_1.SITEID = SITE_2.SITEID
                 WHERE  (MONTH(INVOICE_1.INVOICE_DATE) = 2)
                        AND (YEAR(INVOICE_1.INVOICE_DATE) = 2008)) AS FEBRUARY2008
FROM   SITE AS SITE_1

But instead of this:

code:

Store     January     February
00001      125.36      175.36
00002      563.23      600.25

I am getting this

code:

Store     January     February
00001      688.59      775.61
00002      688.59      775.61
etc.

So what I am getting is the total for January for all stores being displayed. Is there a way to have the total in the subquery be only for the store in the row being displayed? I suppose I could have a view for each month, but that seems like a kind of half-assed way to go about it.

# ? Sep 11, 2008 22:26

MoNsTeR: Jun 29, 2002

The immediate problem is that the SITE table in your subqueries is a different copy of the table than the one in your outer query. Refer to SITE_1 instead. But, that still might not work properly... I don't understand your data model from what you've shown. Why the DISTINCT? What's the relationship between a store and a site, and why do invoices have a site id but not a store number?

edit:
Maybe this will help... If you wanted to select sites, and each site's sales, you could do:

code:

select s.site_id
     , sum(case when i.invoice_date >= '01-JAN-2008' 
                 and i.invoice_date < '01-FEB-2008'
                then i.invoice_total_amount
                else 0
                 end) as JANUARY_08
     , sum(case when i.invoice_date >= '01-FEB-2008' 
                 and i.invoice_date < '01-MAR-2008'
                then i.invoice_total_amount
                else 0
                 end) as FEBRUARY_08
  from site s
     , invoice i
 where s.siteid = i1.siteid
 group by s.siteid

or...

code:

select s.site_id
     , (select sum(i.invoice_total_amount)
          from invoice i
         where i.siteid = s.siteid
           and i.invoice_date >= '01-JAN-2008'
           and i.invoice_date <  '01-FEB-2008') as JANUARY_08
     , (select sum(i.invoice_total_amount)
          from invoice i
         where i.siteid = s.siteid
           and i.invoice_date >= '01-FEB-2008'
           and i.invoice_date <  '01-MAR-2008') as FEBRUARY_08
  from site s

And then you can adjust that as needed for however it is that sites and stores relate.

MoNsTeR fucked around with this message at 00:13 on Sep 12, 2008

# ? Sep 12, 2008 00:00

Just-In-Timeberlake: Aug 18, 2003

MoNsTeR posted:

The immediate problem is that the SITE table in your subqueries is a different copy of the table than the one in your outer query. Refer to SITE_1 instead. But, that still might not work properly... I don't understand your data model from what you've shown. Why the DISTINCT? What's the relationship between a store and a site, and why do invoices have a site id but not a store number?

edit:
Maybe this will help... If you wanted to select sites, and each site's sales, you could do:
code:
select s.site_id
     , sum(case when i.invoice_date >= '01-JAN-2008' 
                 and i.invoice_date < '01-FEB-2008'
                then i.invoice_total_amount
                else 0
                 end) as JANUARY_08
     , sum(case when i.invoice_date >= '01-FEB-2008' 
                 and i.invoice_date < '01-MAR-2008'
                then i.invoice_total_amount
                else 0
                 end) as FEBRUARY_08
  from site s
     , invoice i
 where s.siteid = i1.siteid
 group by s.siteid
or...
code:
select s.site_id
     , (select sum(i.invoice_total_amount)
          from invoice i
         where i.siteid = s.siteid
           and i.invoice_date >= '01-JAN-2008'
           and i.invoice_date <  '01-FEB-2008') as JANUARY_08
     , (select sum(i.invoice_total_amount)
          from invoice i
         where i.siteid = s.siteid
           and i.invoice_date >= '01-FEB-2008'
           and i.invoice_date <  '01-MAR-2008') as FEBRUARY_08
  from site s
And then you can adjust that as needed for however it is that sites and stores relate.

I'll give this a try in the morning, but to answer your questions:

The DISTINCT probably isn't necessary, probably typed it in without thinking.

There are 2 tables of interest, SITE and INVOICE. The SITE table holds the store information, ie. Store Number, Address, State, Zip, etc. The INVOICE table joins on the SiteID attaching that invoice to that store/site. Sorry about the confusion, I neither created nor named the SITE table.

# ? Sep 12, 2008 01:08

Victor: Jun 18, 2004

golgo13sf, what RDBMS are you using?

# ? Sep 12, 2008 02:04

Just-In-Timeberlake: Aug 18, 2003

Victor posted:

golgo13sf, what RDBMS are you using?

MSSQL.

Also, that solution above worked aces. Thanks.

# ? Sep 12, 2008 02:49

Victor: Jun 18, 2004

If you have SQL2005, this will run faster and be much easier to maintain:

code:

select  *
from (
    select  s.store_number,
            month = month(i.invoice_date),
            total = sum(i.invoice_total_amount)
    from    invoice i
    inner join site s on s.siteid = i.siteid
    where   i.invoice_date >= '1/1/08' and i.invoice_date <= '1/1/09'
    group by s.store_number, month(i.invoice_date)
) by_month
pivot (sum(total) for month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) p

# ? Sep 12, 2008 04:20

Just-In-Timeberlake: Aug 18, 2003

Victor posted:

If you have SQL2005, this will run faster and be much easier to maintain:

code:

select  *
from (
    select  s.store_number,
            month = month(i.invoice_date),
            total = sum(i.invoice_total_amount)
    from    invoice i
    inner join site s on s.siteid = i.siteid
    where   i.invoice_date >= '1/1/08' and i.invoice_date <= '1/1/09'
    group by s.store_number, month(i.invoice_date)
) by_month
pivot (sum(total) for month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) p

Son of a bitch, this kicks rear end.

# ? Sep 12, 2008 14:46

MoNsTeR: Jun 29, 2002

I wish I had access to pivot, but we're still on Oracle 10gR1 and it's only available in R2

# ? Sep 12, 2008 16:07

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

I need some help querying some counts from Oracle.
I need to count the records for about 80 tables, but I want to generate that into one query if possible. I don't want the results as one big count, but each table's count preserved.

Is this possible?

Example of this with one table:

code:

select count(*) as Work Order History
from work_order_history

OK, that returns this:

code:

Work Order History
------------------
65,000,000

But I need to have a column for each table return, with their respective counts, like this:

code:

Work Order History | Subscriber Base
------------------   ----------------
65,000,000            123,000

Any ideas?

We generally use Forest&Trees reporting software to interface with the Oracle tables.

# ? Sep 12, 2008 17:05

var1ety: Jul 26, 2004

Uziel posted:

I need some help querying some counts from Oracle.
I need to count the records for about 80 tables, but I want to generate that into one query if possible. I don't want the results as one big count, but each table's count preserved.

Is this possible?

Example of this with one table:
code:
select count(*) as Work Order History
from work_order_history
OK, that returns this:
code:
Work Order History
------------------
65,000,000
But I need to have a column for each table return, with their respective counts, like this:
code:
Work Order History | Subscriber Base
------------------   ----------------
65,000,000            123,000
Any ideas?

We generally use Forest&Trees reporting software to interface with the Oracle tables.

There's no intrinsic way to do this in Oracle until 11g (which adds pivot and unpivot). If you're using an older version you'll need to create a query with 80 columns, or create a filter outside of Oracle that will pivot your data set.

# ? Sep 12, 2008 17:26

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

The server is running Oracle 10.

Do you have an example of the query if it were just two columns instead of 80?

# ? Sep 12, 2008 17:29

var1ety: Jul 26, 2004

Uziel posted:

The server is running Oracle 10.

Do you have an example of the query if it were just two columns instead of 80?

The simplest way is like this:

code:

select (select count(*) from dual) AS sys_dual,
       (select count(*) from SYSTEM_PRIVILEGE_MAP) AS sys_system_privilege_map
  from dual

If you can live with estimates and are auto-analyzing your tables you could query the data dictionary like this:

code:

select max(case
             when owner = 'SYS' and table_name = 'DUAL' then
              num_rows
           end) AS sys_dual,
       max(case
             when owner = 'SYS' and table_name = 'SYSTEM_PRIVILEGE_MAP' then
              num_rows
           end) AS sys_system_privilege_map
  from all_tables

# ? Sep 12, 2008 17:40

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

I tried:

code:

select (select count(*) from dual) AS sys_dual,
       (select count(*) from SYSTEM_PRIVILEGE_MAP) AS sys_system_privilege_map
  from dual

But this has been running for a few minutes now. I guess I figured out another way for the 80 tables, as this seems to work and the results are very returned very quickly.

code:

select a.work_order_history
     , b.customer_base
  from ( 
       select count(*) as work_order_history
         from work_order_history
       ) a
, (
       select count(*) as customer_base
         from customer_base
       ) b

Thanks for the help!

# ? Sep 12, 2008 17:46

Victor: Jun 18, 2004

Have you tried the following?

code:

select (select count(*) from dual) AS sys_dual,
       (select count(*) from SYSTEM_PRIVILEGE_MAP) AS sys_system_privilege_map

Including from dual caused you to run the above for every row in dual, which is clearly not what you wanted.

# ? Sep 12, 2008 18:28

var1ety: Jul 26, 2004

Victor posted:

Have you tried the following?
code:
select (select count(*) from dual) AS sys_dual,
       (select count(*) from SYSTEM_PRIVILEGE_MAP) AS sys_system_privilege_map
Including from dual caused you to run the above for every row in dual, which is clearly not what you wanted.

Dual is a dummy table in Oracle with exactly one row. You must select from a table in Oracle, for better or for worse.

# ? Sep 12, 2008 18:36

Victor: Jun 18, 2004

Ahah, one learns something new every day. I wonder why Oracle choked on your version...

# ? Sep 12, 2008 19:59

Clobbersaurus: Feb 26, 2004

Hey, quick question -

How is postgres on timestamps? There's a possibility my company is going to switch to postgres from mysql (which has great automatic timestamp generation) and I'd like to know whether I have to write it manually or set up a trigger and whatnot.

edit: I found this and I'll trust that it works

quote:

When you want a default timestamp to be the current time, here's how you'd do it:

CREATE TABLE stuff(
entry varchar,
entryCreationTime timestamp default now()
);

Clobbersaurus fucked around with this message at 18:04 on Sep 15, 2008

# ? Sep 15, 2008 17:54

dangerous.hotdog: Feb 29, 2008

I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis.

I don't even know where to begin doing this in SQL (Oracle). I wrote up a VB app, but it's projected to take more than 9 hours to run through 300k records (and this is a very streamlined version of the app). Plus, since I can't create or update tables, I'll have to use that VB app to output some 300k records into an .XLS or .CSV file and then run the actual data analysis on this file (rather than querying the database directly). I imagine this'll just add even more overhead to the entire process.

So, long story short, is there any way I can do this type of query within SQL?

# ? Sep 17, 2008 19:33

Squashy Nipples: Aug 18, 2007

dangerous.hotdog posted:

So, long story short, is there any way I can do this type of query within SQL?

Yep, you sure can. In general, its best to do all of the heavy lifting with SQL, but some things are better done with logic inside your App... When you start out, its not always clear which is which. Getting the latest combination within a time window can be done with GROUP BY and sub-Queries.

The second part of your statement bothers me, though... why go to XLS or CSV? If your truly need to add NEW data to your temp table, then you might have to do that, but otherwise you can synthesize data into a View, and search from that.

If you really do need to build a temp table that big, I would use Access instead of Excel or text files. You don't even have to use the Access GUI or object model: just have a blank MDB file somewhere, and you can MAKE your table(s), INSERT your data, and then SELECT back what you need, all with SQL through ADO. (NOTE: keep the MDB file local to your PC, or it could take a LOOONG time)

Squashy Nipples fucked around with this message at 19:54 on Sep 17, 2008

# ? Sep 17, 2008 19:51

nbv4: Aug 21, 2002; by Duchess Gummybuns

SQL nub here. I have a varchar field in my table that the user can have in many different formats, such as "abc def", "ABD-DEF", "ABC; DEF", "ABC - DEF", etc etc.

So far I've used PHP to do all the "cleaning up" of this data to get into a uniform format to work with, but now I realize the best way is probably to do it with a MySQL function. Something like "SELECT DISTINCT clean_up(`column`) FROM ..." instead of a bunch of PHP loops and crap following it.

Doing a google search for "user defined functions mysql" tells me that there are about 3 or 4 ways to create functions, and they all, as far as I can tell, require compiling C++ code. Is there an easy way to define a simple function without going balls out? All I need is a few REPLACE()'s, and a SUBSTRING() or two...

# ? Sep 18, 2008 06:38

Pento McGreno: May 4, 2004; Cheese.

nbv4 posted:

Doing a google search for "user defined functions mysql" tells me that there are about 3 or 4 ways to create functions, and they all, as far as I can tell, require compiling C++ code. Is there an easy way to define a simple function without going balls out? All I need is a few REPLACE()'s, and a SUBSTRING() or two...

You want Stored Functions, not UDFs.
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

# ? Sep 18, 2008 10:31

stevefukkinc: Oct 10, 2004; TUB-THUMPING,
PAROCHIAL
McKNOB

dangerous.hotdog posted:

I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis.

I don't even know where to begin doing this in SQL (Oracle). I wrote up a VB app, but it's projected to take more than 9 hours to run through 300k records (and this is a very streamlined version of the app). Plus, since I can't create or update tables, I'll have to use that VB app to output some 300k records into an .XLS or .CSV file and then run the actual data analysis on this file (rather than querying the database directly). I imagine this'll just add even more overhead to the entire process.

So, long story short, is there any way I can do this type of query within SQL?

Assuming i've not missed something very obvious... Yes. If you are running on a fairly decent box it would take 5-10 minutes tops with a couple of procedures/functions nested. What if they try again after thirty seconds, you want that kept? Here is how I would do it... Assuming can create a temp table.

code:

-- work table
create table #process (
			id		int identity
			USERNAME	varchar(32)
			SYSTEMDATE	datetime
		       )

-- fill it with your data
insert into #process
select username, systemdatae
from ORIGINAL_TABLE

-- get your poo poo together
declare @x int
		, @y int
		, @currentuser varchar(32)
		, @currentdate datetime
		, @currentdate30 datetime

-- start and end points
select @x = (select min(id) from #process)
select @y = 1+(select max(id) from #process)


while @x < @y

BEGIN

select @currentuser = USERNAME
		,@currentdate = SYSTEMDATE
from #process
where id = @x

select @currentdate30 = dateadd(30, ss, @currentdate)

-- Now, remove the stuff you don't care about from your result set!

delete from #process
where USERNAME = @currentuser
and systemdate between @currentdate and @currentdate30

-- not just the next value, as you may have deleted it!
select @x = (select min(id) from #process where id > @x)

END

Obviously loving around with the precision of the datetime would be expected...

# ? Sep 18, 2008 14:30

Stephen: Feb 6, 2004; Stoned

I'm creating a views table that tracks which items on my site were viewed/clicked etc. and what time. The table has become far too large to efficiently query and results, so I'm creating a view count column in the item table to do efficient sorts by number of views. I guess my real question is about Triggers. Will it be viable/good practice to create a MySQL Trigger that will update the item view_count column on every insert into the views table? Or is there a better way to accomplish this?

# ? Sep 18, 2008 16:01

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Let's say I have a table called goons and each row in the table represents one goon with typical goon attributes like user_id, last_login, etc.

Let's say each goon can be categorized into only one goon_type, like where they hang out most often (TCC, GWS, TFR, etc). In the OOP sense, think subclasses.

Now, what if each subclass has attributes exclusive to that subclass? favorite_weapon, favorite_drug, favorite_food, etc.

How do you model this? Do you put all of those fields in the goons table and NULL them out where they don't apply? Or do you keep the goons table generic and make tables to join off to like goons_tfr, goons_gws, etc that consist only of the goon pk plus the exclusive attributes?

# ? Sep 18, 2008 17:19

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Triple Tech posted:

Let's say I have a table called goons and each row in the table represents one goon with typical goon attributes like user_id, last_login, etc.

Let's say each goon can be categorized into only one goon_type, like where they hang out most often (TCC, GWS, TFR, etc). In the OOP sense, think subclasses.

Now, what if each subclass has attributes exclusive to that subclass? favorite_weapon, favorite_drug, favorite_food, etc.

How do you model this? Do you put all of those fields in the goons table and NULL them out where they don't apply? Or do you keep the goons table generic and make tables to join off to like goons_tfr, goons_gws, etc that consist only of the goon pk plus the exclusive attributes?

I think the main reason no one answered you in the sticky thread is because this is one of those questions that doesn't have one single answer (unless you're just asking people how they personally do it). The problem domain is called Object-Relational Mapping, and you've already laid out the two main means of doing so (IIRC). But the "best way" is definitely open for debate.

# ? Sep 18, 2008 17:38

toby: Dec 4, 2002

Jethro posted:

But the "best way" is definitely open for debate.

Yeah. It is clearly the second way, with tables rather than empty fields.

Anyone who disagrees with me is a momma's boy

# ? Sep 18, 2008 18:07

stevefukkinc: Oct 10, 2004; TUB-THUMPING,
PAROCHIAL
McKNOB

Triple Tech posted:

Or do you keep the goons table generic and make tables to join off to like goons_tfr, goons_gws, etc that consist only of the goon pk plus the exclusive attributes?

Without a shadow of doubt, this is the way to do it.

# ? Sep 18, 2008 18:19

MoNsTeR: Jun 29, 2002

dangerous.hotdog posted:

I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis.

I don't even know where to begin doing this in SQL (Oracle). I wrote up a VB app, but it's projected to take more than 9 hours to run through 300k records (and this is a very streamlined version of the app). Plus, since I can't create or update tables, I'll have to use that VB app to output some 300k records into an .XLS or .CSV file and then run the actual data analysis on this file (rather than querying the database directly). I imagine this'll just add even more overhead to the entire process.

So, long story short, is there any way I can do this type of query within SQL?

Assuming that when there are two records within 30 seconds you want to keep the first one...

code:

select *
  from (
        select t.*
             , (select count(1)
                  from your_table ti
                 where ti.username = t.user_name
                   and ti.systemdate >= t.systemdate
                   and ti.systemdate <  t.systemdate+30/24/60/60) as logins_next_30sec
          from your_table t
       )
 where logins_next_30sec = 0

If you want to keep the last one rather than the first one just change the time frame in the subquery to look back 30 seconds instead of forward. Or, since you have Oracle, you can use analytic functions...

code:

select *
  from (
        select t.*
             , count(1) over(partition by username order by systemdate range between current row and 30/24/60/60 following) as logins_next_30sec
          from your_table t 
       )
 where logins_next_30sec = 1

To reverse that form you would say "between 0.5/24 preceding and current row". And just because I love analytic functions so much, here's yet a third form...

code:

select *
  from (
        select t.*
             , lead(systemdate,1) over(partition by username order by systemdate)
          from your_table t
       )
 where next_login is null
    or next_login > systemdate+30/24/60/60

# ? Sep 18, 2008 20:18

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 02:52

Aredna: Mar 17, 2007; Nap Ghost

PostgreSQL 8.2.6 (Greenplum 3.1.1.3 build 1)

Greenplum is the commercial version of bizgres (https://www.bizgres.org). They use distributed processing and storage and are designed for data warehousing.

I've got a problem with a function that can cause the database to crash and require that some segments are rebuilt. I've turned the exact steps to reproduce this over to our admins, but I didn't know if anyone had seen something similar to this before.

Here's the function:

code:

create or replace function format_address(text) returns text as $$
declare
	str text = $1;
	words text[];
begin
	if length(str) > 0 then
		str = upper(str);
		str = regexp_replace(str,'[^0-9A-Z ]'::text,''::text, 'g'::text);
		str = regexp_replace(str,' {2,}'::text,' '::text, 'g'::text);
		words = string_to_array(str,' '::text);

		for i in array_lower(words,1)..array_upper(words,1) loop 
			words[i] = (select coalesce(max(abbv),words[i]) from tbl_abbv where word = words[i]);
		end loop;
	
		str = array_to_string(words,' '::text); 
	end if;
	
	return str;
end;
$$ language 'plpgsql';

Now, when I run the function on it's own with a simple query it works as expected and finishes in 20-40ms:

code:

select format_addreess('test address here')

When I run a select on a specific table it also works as expected and finishes in 2-4 seconds on the first run, and 400-600ms on subsequent runs:

code:

select rowid, address from shipments limit 1

When I combine them, the database crashes:

code:

select rowid, address, format_address(address) from shipments limit 1

with the error message

code:

ERROR: freeGangsForPortal: Bad primary writer gang (cdbgang.c:3598)

Does anyone know why this could cause problems on Greenplum or even on PostgreSQL? Additionally, does anyone know what error message could be referring to in this context? I've done some googling and can't find anything relevant, other than perhaps this thread soon.

# ? Sep 19, 2008 14:47

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »