jblobner Posted June 29, 2009 Share Posted June 29, 2009 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 More sharing options...
justsomeguy Posted June 29, 2009 Share Posted June 29, 2009 (edited) 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 June 29, 2009 by justsomeguy Link to comment Share on other sites More sharing options...
jblobner Posted June 30, 2009 Author Share Posted June 30, 2009 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 More sharing options...
justsomeguy Posted June 30, 2009 Share Posted June 30, 2009 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 More sharing options...
jblobner Posted June 30, 2009 Author Share Posted June 30, 2009 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 More sharing options...
justsomeguy Posted June 30, 2009 Share Posted June 30, 2009 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now