CODEWITHSUNDEEP
sqljasper-reports
BuZZ-dEE
BuZZ-dEE

Reputation: 6949

Get 0 for column select if where clause does not match

I have the following SQL query in a subreport:

select ifnull(sum (commissionDocument.netto), 0) as commissionCreditNoteNetValue,
   ( select ifnull (sum (commissionDocument.netto ), 0)
     from tckopf as commissionDocument
     where commissionDocument.referenzid = $P{document_id} 
       and commissionDocument.btyp = 7) as commissionInvoiceNetValue
from tckopf as commissionDocument
where commissionDocument.referenzid = $P{document_id} 
  and commissionDocument.btyp = 8

The problem is that I get an empty report if the where clause:

where commissionDocument.referenzid = $P{document_id} 
  and commissionDocument.btyp = 8

does not match and the other where clause match.

where commissionDocument.referenzid = $P{document_id} 
  and commissionDocument.btyp = 7

  1. SQL Fiddle example if the inner where clause match

  2. SQL Fiddle example if both where clauses match

  3. SQL Fiddle example if the outer where clause match

I forgot to mention, that FoxPro DBF contains the data and is connected via a JDBC driver to iReport.

The subreport has only one field with the following expression.:

($F{commissionInvoiceNetValue} != null && $F{commissionCreditNoteNetValue} != null)
? ($F{commissionInvoiceNetValue} - $F{commissionCreditNoteNetValue})
: ($F{commissionInvoiceNetValue} != null) ? $F{commissionInvoiceNetValue}
: ($F{commissionCreditNoteNetValue} != null) ? $F{commissionCreditNoteNetValue}
: ""

How can I bypass that?

Upvotes: 0

Views: 129

Answers (2)

BuZZ-dEE
BuZZ-dEE

Reputation: 6949

I changed the query to:

SELECT
(   SELECT IFNULL (SUM (commissionInvoice.netto ), 0) AS commissionInvoiceNetValue
    FROM tckopf AS commissionInvoice
    WHERE commissionInvoice.referenzid = 1
        AND commissionInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (commissionCreditNote.netto), 0) AS commissionCreditNoteNetValue
    FROM tckopf AS commissionCreditNote
    WHERE commissionCreditNote.referenzid = 1
        AND commissionCreditNote.btyp = 6 )
 AS commissionResult

and the field to the the following expression:

$F{commissionResult}

and now I get the desired result.

Upvotes: 1

PeterRing
PeterRing

Reputation: 1797

I thin you can fix this by an outer join:

select ifnull(sum (cDoc.netto), 0) as cCNValue, CInvNoteVal.Val
from tckopf as cDoc
FULL OUTER JOIN
  (select ifnull (sum (cDoc.netto ), 0) AS Val, referenzid 
  from tckopf as cDoc
  where cDoc.referenzid = $P{document_id} and cDoc.btyp = 7) as CInvNoteVal
  ON CInvNoteVal.referenzid = cDoc.referenzid
  where cDoc.referenzid = $P{document_id} and cDoc.btyp = 8

Upvotes: 0

Related Questions