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.amoun t
has a record in the database based on the value of the T_MULTILIST.cod e
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.cod e.
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.cod e 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.amoun t = 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_GRA DE.grade, T_MULTILIST.des cription, T_MULTILIST.cod e,
T_PAYMENT.amoun t
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETA IL
T_SHIPPING_DETA IL ON
T_PAYMENT.trans action_id=T_SHI PPING_DETAIL.tr ansaction_id)
ON T_MULTILIST.cod e=T_SHIPPING_DE TAIL.multilist_ code) INNER JOIN
T_MULTILIST_GRA DE T_MULTILIST_GRA DE ON
T_MULTILIST.cod e=T_MULTILIST_G RADE.multilist_ code,
T_ORDER, T_REQUISITION, T_REQUISITION_D ETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETA IL.order_id AND
T_REQUISITION.i d = T_ORDER.requisi tion_id AND
T_REQUISITION_D ETAIL.requisiti on_id = T_REQUISITION.i d AND
T_REQUISITION_D ETAIL.latest_re cord_flag = 1 AND
T_REQUISITION.l atest_record_fl ag = 1 AND
T_ORDER.latest_ record_flag = 1
AND (T_MULTILIST.co de='1040')
AND (T_MULTILIST.ex piration_year >= '2006' )
AND (T_REQUISITION. requested_shipm ent_date >= '2006' + '0601'
AND T_REQUISITION.r equested_shipme nt_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_GRA DE.grade, T_MULTILIST.des cription,
T_MULTILIST.cod e, T_PAYMENT.amoun t
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETA IL T_SHIPPING_DETA IL
ON T_PAYMENT.trans action_id=T_SHI PPING_DETAIL.tr ansaction_id)
ON T_MULTILIST.cod e=T_SHIPPING_DE TAIL.multilist_ code) INNER JOIN
T_MULTILIST_GRA DE T_MULTILIST_GRA DE ON
T_MULTILIST.cod e=T_MULTILIST_G RADE.multilist_ code,
T_ORDER, T_REQUISITION, T_REQUISITION_D ETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETA IL.order_id AND
T_REQUISITION.i d = T_ORDER.requisi tion_id AND
T_REQUISITION_D ETAIL.requisiti on_id = T_REQUISITION.i d AND
T_REQUISITION_D ETAIL.latest_re cord_flag = 1 AND
T_REQUISITION.l atest_record_fl ag = 1 AND
T_ORDER.latest_ record_flag = 1
AND (T_MULTILIST.co de='0040')
and (T_MULTILIST.ex piration_year >= '2006' )
AND (T_REQUISITION. requested_shipm ent_date >= '2006' + '0601'
AND T_REQUISITION.r equested_shipme nt_date < dateadd(YY, 1,
'2006' + '0601' ) )

Up to 40 more UNION statements follow the above 2.

[Erland Sommarskog]

wg********@yaho o.com (wg********@yah oo.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.amoun t
has a record in the database based on the value of the T_MULTILIST.cod e
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.cod e.
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.cod e 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.amoun t = 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_GRA DE.grade, T_MULTILIST.des cription, T_MULTILIST.cod e,
amount = coalesce(T_PAYM ENT.amount, 0)
FROM T_MULTILIST T_MULTILIST
LEFT JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETA IL T_SHIPPING_DETA IL
ON T_PAYMENT.trans action_id=T_SHI PPING_DETAIL.tr ansaction_id)
ON T_MULTILIST.cod e=T_SHIPPING_DE TAIL.multilist_ code)
INNER JOIN T_MULTILIST_GRA DE T_MULTILIST_GRA DE ON
T_MULTILIST.cod e=T_MULTILIST_G RADE.multilist_ code,
T_ORDER, T_REQUISITION, T_REQUISITION_D ETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETA IL.order_id AND
T_REQUISITION.i d = T_ORDER.requisi tion_id AND
T_REQUISITION_D ETAIL.requisiti on_id = T_REQUISITION.i d AND
T_REQUISITION_D ETAIL.latest_re cord_flag = 1 AND
T_REQUISITION.l atest_record_fl ag = 1 AND
T_ORDER.latest_ record_flag = 1
AND (T_MULTILIST.co de='1040')
AND (T_MULTILIST.ex piration_year >= '2006' )
AND (T_REQUISITION. requested_shipm ent_date >= '2006' + '0601'
AND T_REQUISITION.r equested_shipme nt_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.cod e.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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********@yaho o.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.amoun t
has a record in the database based on the value of the T_MULTILIST.cod e
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.cod e.

(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.cod e 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_DETA IL AS sd
ON p.transaction_i d = sd.transaction_ id
INNER JOIN T_MULTILIST AS m
ON m.code = sd.multilist_co de
AND m.expiration_ye ar >= '2006'
INNER JOIN T_MULTILIST_GRA DE mg
ON m.code = mg.multilist_co de
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_i d
AND r.latest_record _flag = 1
AND r.requested_shi pment_date >= '20060601'
AND r.requested_shi pment_date < '20070601'
INNER JOIN T_REQUISITION_D ETAIL AS rd
ON rd.requisition_ id = r.id
AND rd.latest_recor d_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.R EMOVETHIS.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