Unmatched Query and/or Exclusion

[n2rmymatrix]

Here is my result set:
Inv1 Txn1 TxnCat=Charges
Inv1 Txn2 TxnCat=BillPrint
Inv1 Txn3 TxnCat=Payments
Inv2 Txn1 TxnCat=Charges
Inv2 Txn2 TxnCat=BillPrint

I do NOT want any invoice that Contains a TxnCat=Payment...How do I get my results to exclude the ENTIRE invoice that contains the payment category and return results that look like Inv2....where the invoice has never had a payment?

In Access, (from result set above)I would simply make a table grouped by Inv and TxnCat where TxnCat=payments....then I'd do a quick unmatched query on the invoice #'s to get my final set of invoices without payment.

I appreciate your feedback.

 

[drlex]

Unless I've misunderstood your question,
then something akin to
SELECT * FROM INV_TABLE WHERE INV_NO NOT IN (SELECT INV_NO FROM INV_TABLE WHERE TXNCAT = 'PAYMENTS')
should do it.

soi la, soi carré

 

[blom0344]

Which brings us on the subject on how to define a uncorrelated subquery in Cognos.
I have never needed one yet, but using a filter that references a second query does not seem to work..

Any ideas drlex?

Ties Blom

 

[n2rmymatrix]

I'm just learning cognos studio...so where exactly would i place this sql statement?

 

[drlex]

Apologies - I don't know Cognos Studio, so can't be specific.
I was thinking of the logical process that if you could return a set of the invoice numbers that have a payment recorded (TXNCAT = 'PAYMENTS' in your DB), then the main report would be invoice numbers NOT in this set.
From what Ties wrote, it sounds as though this might be difficult to implement; I use Impromptu and would either use Prompt manager in the main report to specify a Report picklist (a report that contained the set to exclude) or just amend the SQL of the main report.



soi la, soi carré

 

[blom0344]

Just out of curiosity I tried another approach as an alternative to the subquery:

1. Define an except set first using Q1 and Q2. This yields the complement to the 'not in'.
2. This results in the query Q3.
3. Use Q3 in a equi-join with the 'real' query Q4. The resultset is then Q5 which is used to build the report against.

Basically this yields the same result as defining a NOT IN, though it takes quite some effort within query panel.

From othr sources it looks like the use of a subquery was available in ReportNet, but not there in C8 (?)

Ties Blom