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
Sample contents of each:
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?