How do I combine this query?

How can I combine these two SQL queries into one select statement or subquery? It needs to subtract ic_qty where ic_type_ab = Actual from ic_qty where ic_type_ab = Book. Here is the table inv_counts ic_prft_ctr smallint Profit Center Number ic_prodlnk integer Product Link ic_date date Count date ic_qty decimal(10) count quantity ic_type_ab char(6) count type: actual, book These two select statements give me the results I want, but I would like to have them combined into one select. select * from inv_counts where ic_type_ab = "book" and ic_date = "04/30/03" andic_prodlnk = 92 into temp a1 with no log; select (inv_counts.ic_qty - a1.ic_qty), a1.ic_prft_ctr from inv_counts, a1 where a1.ic_prft_ctr = inv_counts.ic_prft_ctr and a1.ic_prodlnk = inv_counts.ic_prodlnk and a1.ic_date = inv_counts.ic_date and inv_counts.ic_type_ab = "actual" and inv_counts.ic_date = "04/30/03" ~

Chad Lemmen wrote: > How can I combine these two SQL queries into one select statement > or subquery? It needs to subtract ic_qty where ic_type_ab = Actual from > ic_qty where ic_type_ab = Book. Here is the table > > inv_counts > > ic_prft_ctr smallint Profit Center Number > ic_prodlnk integer Product Link > ic_date date Count date > ic_qty decimal(10) count quantity > ic_type_ab char(6) count type: actual, book > > These two select statements give me the results I want, but I would > like to have them combined into one select. > > select * from inv_counts > where ic_type_ab = "book" and ic_date = "04/30/03" and> ic_prodlnk = 92 into temp a1 with no log; > > select (inv_counts.ic_qty - a1.ic_qty), a1.ic_prft_ctr > from inv_counts, a1 where > a1.ic_prft_ctr = inv_counts.ic_prft_ctr and a1.ic_prodlnk = > inv_counts.ic_prodlnk and a1.ic_date = inv_counts.ic_date and > inv_counts.ic_type_ab = "actual" and inv_counts.ic_date = "04/30/03" Table aliases: SELECT (T1.ic_qty - T2.ic_qty) AS qty_diff, T2.ic_prft_ctr FROM inv_counts T1, inv_counts T2 WHERE T1.ic_prft_ctr = T2.ic_prft_ctr AND T1.ic_prodlnk = T2.ic_prodlnk AND T1.ic_date = T2.ic_date AND T1.ic_type_ab = "actual" AND T1.ic_date = MDY(4,30,2003) AND(T2.ic_type_ab = "book" AND T2.ic_date = MDY(4,30,2003) AND T2.ic_prodlnk = 92); The parenthesized part of the WHERE clause corresponds to the first SELECT statement; the rest to the second. One of the MDY() clauses is redundant, but no harm is done by including both. Why would you still be using 2-digit years for dates? Did you forget Y2K already? Using MDY like that is also guaranteed to work regardless of the setting of DBDATE - unlike the original code. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler <jleffler@earthlink.net> wrote: > Chad Lemmen wrote: >> How can I combine these two SQL queries into one select statement >> or subquery? It needs to subtract ic_qty where ic_type_ab = Actual from >> ic_qty where ic_type_ab = Book. Here is the table >> >> inv_counts >> >> ic_prft_ctr smallint Profit Center Number >> ic_prodlnk integer Product Link >> ic_date date Count date >> ic_qty decimal(10) count quantity >> ic_type_ab char(6) count type: actual, book >> >> These two select statements give me the results I want, but I would >> like to have them combined into one select. >> >> select * from inv_counts >> where ic_type_ab = "book" and ic_date = "04/30/03" and>> ic_prodlnk = 92 into temp a1 with no log; >> >> select (inv_counts.ic_qty - a1.ic_qty), a1.ic_prft_ctr >> from inv_counts, a1 where >> a1.ic_prft_ctr = inv_counts.ic_prft_ctr and a1.ic_prodlnk = >> inv_counts.ic_prodlnk and a1.ic_date = inv_counts.ic_date and >> inv_counts.ic_type_ab = "actual" and inv_counts.ic_date = "04/30/03" > Table aliases: > SELECT (T1.ic_qty - T2.ic_qty) AS qty_diff, T2.ic_prft_ctr > FROM inv_counts T1, inv_counts T2 > WHERE T1.ic_prft_ctr = T2.ic_prft_ctr > AND T1.ic_prodlnk = T2.ic_prodlnk > AND T1.ic_date = T2.ic_date > AND T1.ic_type_ab = "actual" > AND T1.ic_date = MDY(4,30,2003) > AND(T2.ic_type_ab = "book" > AND T2.ic_date = MDY(4,30,2003) > AND T2.ic_prodlnk = 92); > The parenthesized part of the WHERE clause corresponds to the first > SELECT statement; the rest to the second. One of the MDY() clauses is > redundant, but no harm is done by including both. Why would you still > be using 2-digit years for dates? Did you forget Y2K already? Using > MDY like that is also guaranteed to work regardless of the setting of > DBDATE - unlike the original code. Jonathan, Thanks, your code works great and is just what I was looking for. Cathy Kipp's book "Programming Informix SQL/4GL" didn't mention table aliases. Using table aliases will help me with several other SQL queries I'm working on also. I am using 4-digit years, I have DBCENTURY=C set. Using MDY() works much better for me also and solved another problem I was having. My database stores dates as MM/DD/YYYY, but when I connect to my SE 7.23 database via JDBC the date needed to be in the form YYYY-MM-DD or ic_date = "2003-04-30" or no records would be found. Using MDY() has solved that problem.