Empty recordsets and artificial records

[wgblackmon]

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.transac tion_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.transac tion_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.

[Erland Sommarskog]

wg********@yahoo.com (wg********@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.transac tion_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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 5 Jul 2006 09:41:41 -0700, wg********@yahoo.com wrote:

>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.

(snip)

Hi wgblackmon,

The statement you gave is horrible, and I'd be surprised if it shows
anything even resembling performance.

If I may assume that the other 40 UNION'ed SELECT statements all
resemble the first two, you have 42 almost equal SELECT statements with
only one code in the WHERE clause and some constants in the SELECT list
different. Why don't you include the 42 relevant codes and the
accompanying values for Rec_Type, Super_Type, Other_Type, and Proc_Type
in a table? Then, yoou can rewrite the complete monster with one single
query.

There are many other things at fault with your query too:
- Think about readability and maintainability: Why do you supply aliases
that are exactly equal to the table's name? Why do you mix "new style"
infixed joins with "old style" joins (using a comma-seperated list of
tables)? Why do you use nested INNER JOINs? Why don't you stick to one
style for using newlines and indentation to make your queries more
readable?
- Think about performance: Why do you use >'2006' + '0601' << to
denote a fixed date? If you use >'20060601' <<, it can be converted at
compile-time. Now, you're forcing string concatenation and conversion at
execution time. It gets even worse in the complicated datetime formula
that can be replaced with '20070601'. Also, why do you use "UNION"
instead of "UNION ALL", forcing SQL Server to search for duplicates if
the constants in the 42 SELECT lists are distinct anyway? And do you
really need a DISTINCT on the individual queries? It's often a token of
bad design.

>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.

If you add the extra table suggested above, fulfilling this requirement
is as easy as changing an INNER JOIN to an OUTER JOIN and adding some
COALESCE functions in the SELECT list!

Here's a quick stab (retaining the DISTINCT for now, but do check if you
can leave it out). I have already added the user's wish.

First, set up and fill a table of codes and type descriptions:

CREATE TABLE dbo.CodeList
(Code char(4) NOT NULL PRIMARY KEY,
Rec_Type varchar(40) NOT NULL,
Super_Type varchar(40) NOT NULL,
Other_Type varchar(40) NOT NULL,
Proc_Type varchar(40) NOT NULL)
go
INSERT INTO CodeList (Code, Rec_Type, Super_Type, Other_Type, Proc_Type)
SELECT '1040', 'English Language Arts, Grade 1',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
UNION ALL
SELECT '0040', 'English Language Arts, Kindergarten',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
-- etc for the other codes
go

Now, attempt to rewrite your query.

SELECT DISTINCT c.Rec_Type, c.Super_Type, c.Other_Type, c.Proc_Type,
mg.grade, m.description, m.code, p.amount
FROM T_PAYMENT AS p
INNER JOIN T_SHIPPING_DETAIL AS sd
ON p.transaction_id = sd.transaction_id
INNER JOIN T_MULTILIST AS m
ON m.code = sd.multilist_code
AND m.expiration_year >= '2006'
INNER JOIN T_MULTILIST_GRADE mg
ON m.code = mg.multilist_code
INNER JOIN T_ORDER AS o
ON o.id = sd.order_id
AND o.latest_record_flag = 1
INNER JOIN T_REQUISITION AS r
ON r.id = o.requisition_id
AND r.latest_record_flag = 1
AND r.requested_shipment_date >= '20060601'
AND r.requested_shipment_date < '20070601'
INNER JOIN T_REQUISITION_DETAIL AS rd
ON rd.requisition_id = r.id
AND rd.latest_record_flag = 1
RIGHT OUTER JOIN dbo.CodeList AS c
ON c.Code = m.code

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[Roy Harvey]

On Thu, 06 Jul 2006 00:26:27 +0200, Hugo Kornelis
<hu**@perFact.REMOVETHIS.info.INVALIDwrote:

>The statement you gave is horrible, and I'd be surprised if it shows
anything even resembling performance.

From the original post, I would wonder if it was generated by Crystal
Reports. Code that bad does often come from such sources.

Roy Harvey
Beacon Falls, CT