Posted by Erland Sommarskog on 07/05/06 22:22

wgblackmon@yahoo.com (wgblackmon@yahoo.com) writes:
> I'm currently running the following statement that is used in a Crystal
> Report. Basically, a record is returned when the T_PAYMENT.amount
> has a record in the database based on the value of the T_MULTILIST.code
> field. Currently, if there is no record returned, there is no listing
> in the report for the given T_MULTILIST.code.
>
>
> The user now wants a record to be displayed on the report when there is
> no record in the database - she wants it to display a value of '$0'
> for the given T_MULTILIST.code record. I tried to explain the fact that
> is not possible the way things stand at the moment. Basically I need
> some type
> of case statement that says 'if there is no record returned, create a
> single record and set T_PAYMENT.amount = 0' AFTER each query has been
> executed.

Sounds like you are looking for an outer join. Now, since I don't know
your tables, or what is being presented, the below may not be the
exact match, but you may be able to get the drift.


SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
amount = coalesce(T_PAYMENT.amount, 0)
FROM T_MULTILIST T_MULTILIST
LEFT JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transaction_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code)
INNER JOIN T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Permit me that the query is quite messy with it's mix of ANSI-join operators
and comma-style cross-join with the join conditions in the WHERE clause.
With outer joins in the mix, you should rewrite all to use ANSI joins.

> Up to 40 more UNION statements follow the above 2.

40? Ouch! But why? From the two segments you posted, it appears to me
that all that differs is the condition on T_MULTILIST.code.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]