Jump to content

New To Writing Db Queries And Need Help


jblobner
 Share

Recommended Posts

I have a DB query that returns two data sets.select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617 unionselect count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617;The result looks like this.0000194791925400001 is the column heading9479 & 19245 are data.How can I rewrite the query so the output is in 2 columns?Example9478 19254

Link to comment
Share on other sites

You can have each in its own column, but they're going to be in different rows. This will give you a result set with 2 rows where each row will have one null column and one with a value.

select count (*) AS 'num1', NULL as 'num2' from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617 unionselect count (*) AS 'num2', NULL as 'num1' from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617;

You can do something like this to get them in the same row:

select (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS 'num1', (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS 'num2' FROM pfvcfnh

Edited by justsomeguy
Link to comment
Share on other sites

You can have each in its own column, but they're going to be in different rows. This will give you a result set with 2 rows where each row will have one null column and one with a value.
select count (*) AS 'num1', NULL as 'num2' from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617 unionselect count (*) AS 'num2', NULL as 'num1' from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617;

You can do something like this to get them in the same row:

select (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS 'num1', (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS 'num2' FROM pfvcfnh

Thanks for the help. I ran the second query and it returned the following error. 16:43:41 [sELECT - 0 row(s), 0.110 secs] [Error Code: -104, SQL State: 42601] [sql0104] Token 'num1' was not valid. Valid tokens: <IDENTIFIER>.... 1 statement(s) executed, 0 row(s) affected, execution time 0.110 secI am using DBVisualizer to query an AS400 data base
Link to comment
Share on other sites

I think the quotes were a mistake.

select (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS num1, (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS num2 FROM pfvcfnh

Link to comment
Share on other sites

I think the quotes were a mistake.
select (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS num1, (  select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS num2 FROM pfvcfnh

Wow this is great stuff. Thanks for the help and it's getting closer but the output is in 2 columns, but it is repeated in multiple rows.NUM1 NUM212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 1382212059 13822etc...
Link to comment
Share on other sites

It's giving one row for each row in the table. I believe the table is required for select statements, maybe not for AS400. You can either leave the table out and see if that works:select ( select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS num1, ( select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS num2or give it a limit. The syntax for limit might be different for AS400, I'm not familiar with that.select ( select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617) AS num1, ( select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617) AS num2 FROM pfvcfnh LIMIT 1

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...