Re: How do I combine this query?

Nothing against Cathy (Ex IIUG BoD Member, etc.) but her book is a bit dated at this point don't you think. I know that 4GL hasn't changed much and what worked in the book probably still works today but certainly some things have been added in the last 5+ years... Rob > 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.