Create an artificial record based on an empty recordset?

wgblackmon@yahoo.com · *nix forums beginner Joined: 27 Mar 2006 Posts: 6

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.
Anyone have any idea how to accomplish this?

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,
T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER 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' ) )

UNION
SELECT DISTINCT
'English Language Arts, Kindergarten' 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, T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER 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='0040')
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' ) )

Up to 40 more UNION statements follow the above 2.

Google