1 Reply Latest reply on Mar 23, 2016 3:15 PM by rp0428

    Evaluate Column Not in Group By

    3020522

      I'd like to somehow evaluate a column called GL when it is not in the GROUP BY statement. I don't want to include it in GROUP BY because it will output a row for each different number in GL. However, I need to be able to tell if a list of GLs is in the output. Here's an example:

       

      These are sample tables containing the GLs I need to compare against the results:

      select asset_appv_acct, purch_accts, legal_accts
      from ap_settings

       

      Sample contents of each:

      ASSET_APPV_ACCT: 17101,17102,17112,17151,17161,52602,52467,18005,18010,18011,18014,18015,18041,18042,18043,18044,18045,18046,18047,18048,18049,18050,18052,18053,18054,18055,18056,18057,18058,18059,18060
      PURCH_ACCTS: 52402,52407,52460,52639
      LEGAL_ACCTS: 52608,52615,52616,52617,52618,52621,52626,52635,52636,52659,52662,52663,52664,52680,52682,52683,52684,52685

       

      Here's a sample of the query:
      SELECT h.h_spa_id as spa_id,h.submit_dt,h.oa_ap_date,SomeAggregateFunction()
                  FROM table1 h,table2 d, table3
                  WHERE h.next_apprv= 'approverID'
                  and h.table1_id = d.table2_id
                  and h.table1_id = table3_id
                  group by h.h_spa_id,h.submit_dt,h.oa_ap_date,d.invoice_number
                  order by h.h_spa_id

       

      And here's an example of the result:
      1627005 1/25/2008 10:11:53 AM 1/25/2008 11:15:56 AM (Result of Aggregate Function Here)

       

      I'd like to evaluate the list above, fed in dynamically from a sub-query or something, and output an indicator field. For example, outputting a 1 if the GL is in the list and a 0 if it is not. The trick is that I need to check the GL field in table2 but not include it in the GROUP BY clause. Note: I tried CASE and DECODE. DECODE was not comparing the items in the list, just the entire list. So I was always getting the 'no match' scenario. CASE seems unwieldy because I'd have to extract each GL separately from the list and create a new case for syntax's sake, like WHEN '18327' THEN '*' | WHEN '52407' THEN '*' | WHEN '52460' THEN '*'. What's the easy way to query those lists as part of the same result set and evaluate the GLs?