Stored procedure returns duplicates

[yin_n_yang74@yahoo.com]

I am trying to create a report in Crystal Reports (v 8.5). I have a
stored procedure to pull data from two databases and parameters.
There are multiple one-to-many relationships and the stored procedure
returns duplicates; e.g., one schedule may have multiple resources,
supplies, and/or orders (and one order may have multiple foods). Is
there a way to stop the duplication?

The stored procedure looks like this:

*************** *************** *************** *************** *************** *********
SET QUOTED_IDENTIFI ER OFF
GO

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE usp_rpt1 (
@start_date smalldatetime,
@end_date smalldatetime,
@rpt_type varchar(3),
@rpt_id int
)
AS

set nocount on

--Set up some string variables to build the selection query for the
parameters supplied

declare @fields varchar(255)
declare @tables varchar(255)
declare @where varchar(2000)

CREATE TABLE #tmp_sched(sche d_id int, rpt_type_desc varchar(100),
rpt_id int)

set end_date = midnight of next day
SELECT @end_date = DATEADD(day,1,@ end_date)
SELECT @end_date = CONVERT(smallda tetime,
CONVERT(varchar (4),YEAR(@end_d ate)) + '-'
+
CONVERT(varchar (2),MONTH(@end_ date)) + '-'
+
CONVERT(varchar (2),DAY(@end_da te))

IF @rpt_type = 'LOC'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, l.loc_desc, l.loc_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_d ate srd ON s.sched_id = srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
LEFT JOIN tbl_loc l ON g.loc_id = l.loc_id
WHERE l.loc_id = CONVERT(varchar (12),@rpt_id)
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
ANd l.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_d ate_local >=
CONVERT(varchar (20), @start_date, 1)
AND srd.mtg_start_d ate_local <
CONVERT(varchar (20), @end_date+1,1)
IF @rpt_type = 'GRP'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, g.grp_desc, g.grp_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_d ate srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
WHERE (g.grp_id = CONVERT(varchar (12),@rpt_id)
OR g.parent_grp_id =
CONVERT(varchar (12),@rpt_id))
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_d ate_local >=
CONVERT(varchar (20), @start_date, 1)
AND srd.mtg_start_d ate_local <
CONVERT(varchar (20), @end_date+1,1)
IF @rpt_type = 'RES'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, r.res_desc, r.res_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_d ate srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
WHERE r.res_id = CONVERT(varchar (12),@rpt_id)
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_d ate_local >=
CONVERT(varchar (20), @start_date, 1)
AND srd.mtg_start_d ate_local <
CONVERT(varchar (20), @end_date+1, 1)
IF @rpt_type = 'REG'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, reg.region_desc ,
reg.region_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_d ate srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
LEFT JOIN tbl_loc l ON g.loc_id = l.loc_id
LEFT JOIN tbl_region reg ON l.loc_id = reg.region_id
WHERE reg.region_id = CONVERT(varchar (12),@rpt_id)
AND reg.obsolete_fl ag = 0
AND l.obsolete_flag = 0
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_d ate_local >=
CONVERT(varchar (20), @start_date, 1)
AND srd.mtg_start_d ate_local <
CONVERT(varchar (20), @end_date+1, 1)
IF @rpt_type NOT IN ('LOC','GRP','R ES','REG')
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, g.grp_desc, g.grp_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_d ate srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
WHERE (g.grp_id = 0 OR g.parent_grp_id = 0)
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_d ate_local >=
CONVERT(varchar (20), @start_date, 1)
AND srd.mtg_start_d ate_local <
CONVERT(varchar (20), @end_date+1,1)

--This is the selection for our report
SELECT Description = ts.rpt_type_des c,
Date = CONVERT(varchar (12),srd.mtg_st art_date_local, 101),
StartTime = srd.mtg_start_d ate_local,
EndTime = srd.mtg_end_dat e_local,
SchedID = s.sched_id,
MeetingTitle = s.sched_desc,
ResourceUsed = r.res_desc,
ResourceSetup = su.setup_desc + ' (' +
CONVERT(varchar (10),rs.capacit y) + ')',
NumberOfAttende es = Attendees.strin g_value,
OrderID = ord.order_id,
FoodQty = CONVERT (int,oi.order_q ty),
FoodDesc = i.item_name,
Side = sidei.item_name ,
MeetingDesc = ord.order_desc,
Supplies = suppliesudf.udf _desc,
SuppliesVal = supplies.value,
AccountCode = ord.order_user_ acct_code,
host.string_val ue as MeetingHost,
CateringNotes = ord.order_notes ,
FoodNotes = oi.order_notes

FROM #tmp_sched ts
JOIN tbl_sched s ON ts.sched_id = s.sched_id
JOIN tbl_sched_res_d ate srd ON ts.sched_id = srd.sched_id
JOIN tbl_res r ON srd.res_id = r.res_id
JOIN tbl_sched_res_s etup srs ON s.sched_id = srs.sched_id and
r.res_id = srs.res_id
LEFT JOIN tbl_res_setup rs ON srs.setup_id = rs.setup_id AND
srs.res_id = rs.res_id
LEFT JOIN tbl_setup su ON rs.setup_id = su.setup_id
LEFT JOIN tbl_sched_reque st_tab_val supplies ON s.sched_id =
supplies.sched_ id
AND ((supplies.requ est_tab_id =
(SELECT request_tab_id FROM tbl_request_tab WHERE
(request_tab_hd r = 'A) Meeting Supplies')))
OR (supplies.reque st_tab_id =
(SELECT request_tab_id FROM tbl_request_tab WHERE
(request_tab_hd r = 'Mtg Supplies-PEMC'))))
AND (CONVERT(varcha r, supplies.value) NOT IN ('0', ''))
LEFT JOIN tbl_udf suppliesudf ON supplies.udf_id =
suppliesudf.udf _id
JOIN tbl_sched_udf_v al attendees ON attendees.sched _id = s.sched_id
AND attendees.udf_i d =
(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'Number of
Attendees') --UDF For No of Attendees
JOIN tbl_sched_udf_v al host ON host.sched_id = s.sched_id
AND host.udf_id =
(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'Meeting
Host') --UDF For meeting host name
LEFT JOIN RSCatering.dbo. tbl_Order ord ON ord.order_sched _id =
s.sched_id --Our link to table in other database
JOIN RSCatering.dbo. tbl_order_item oi ON ord.order_id =
oi.order_id
LEFT JOIN RSCatering.dbo. tbl_menu_item mi ON oi.menu_item_id =
mi.menu_item_id
LEFT JOIN RSCatering.dbo. tbl_item i ON mi.item_id = i.item_id
LEFT JOIN RSCatering.dbo. tbl_order_item_ sides side ON
oi.order_item_i d = side.order_item _id
LEFT JOIN RSCatering.dbo. tbl_item sidei ON side.item_id =
sidei.item_id

WHERE ord.deleted_fla g = 0 AND oi.deleted_flag = 0
ORDER BY
ts.rpt_type_des c,srd.mtg_start _date_local,srd .mtg_end_date_l ocal,
r.res_desc

DROP TABLE #tmp_sched
GO

SET QUOTED_IDENTIFI ER OFF
GO

SET ANSI_NULLS ON
GO
*************** *************** *************** *************** *************** ***************

The simplified result looks like:

Sched2 Resource1 Supply1 Order5
Sched2 Resource1 Supply1 Order6
Sched2 Resource1 Supply3 Order5
Sched2 Resource1 Supply3 Order6
Sched2 Resource2 Supply1 Order5
Sched2 Resource2 Supply1 Order6
Sched2 Resource2 Supply3 Order5
Sched2 Resource2 Supply3 Order6

However, I want the result to look like:

Sched2 Resource1 Supply1 Order5
Sched2 Resource2 Supply3 Order6

Any suggestion is greatly appreciated.

[Ed Murphy]

Re: Stored procedure returns duplicates

yin_n_yang74@ya hoo.com wrote:

--Set up some string variables to build the selection query for the
parameters supplied
>
declare @fields varchar(255)
declare @tables varchar(255)
declare @where varchar(2000)

These are unused and should be removed. (Unless they're used in code
that you edited out because it wasn't relevant to the problem at hand.)

IF @rpt_type = 'GRP'

IF @rpt_type NOT IN ('LOC','GRP','R ES','REG')

The blocks following these appear to be identical. I recommend removing
the latter, and adding the following above the first INSERT INTO block:

IF @rpt_type NOT IN ('LOC','GRP','R ES','REG') THEN
SET @rpt_type = 'GRP'

The simplified result looks like:
>
Sched2 Resource1 Supply1 Order5
Sched2 Resource1 Supply1 Order6
Sched2 Resource1 Supply3 Order5
Sched2 Resource1 Supply3 Order6
Sched2 Resource2 Supply1 Order5
Sched2 Resource2 Supply1 Order6
Sched2 Resource2 Supply3 Order5
Sched2 Resource2 Supply3 Order6

Oversimplified. Are these SchedID, ResourceUsed, SuppliesVal, and
OrderID?

However, I want the result to look like:
>
Sched2 Resource1 Supply1 Order5
Sched2 Resource2 Supply3 Order6

We need more information about all the tables involved in the stored
procedure's final query, specifically

a) whether their relationships are 1:1 or 1:N or M:N

b) what criteria tell us that Resource1 should associate only with
Supply1 (not Supply3) and Order5 (not Order6), and similarly
for Resource2 and Supply3 (not Supply1) and Order6 (not Order5)

[yin_n_yang74@yahoo.com]

Re: Stored procedure returns duplicates

Thank you for your feedback.

The final query has all the columns specified in SELECT. Yes, your
guess is correct - SchedID, ResourceUsed, SuppliesVal, and OrderID
were what was on my mind as I typed the example.

Their relations are as follows:

Schedule to Resource is 1:N
Schedule to Supply is 1:N
Schedule to Order is 1:N
Order to Food is 1:N

Resource, Supply and Order are not directly related to each other.
There is no association that you were asking about in b).

b) what criteria tell us that Resource1 should associate only with
Supply1 (not Supply3) and Order5 (not Order6), and similarly
for Resource2 and Supply3 (not Supply1) and Order6 (not Order5)

Since we are pulling from two databases and using parameters, our
solution has been to use a stored procedure. Crystal Reports (v 8.5)
allows only one stored procedure.


On Apr 9, 6:47 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

yin_n_yan...@ya hoo.com wrote:

--Set up some string variables to build the selection query for the
parameters supplied

>

declare @fields varchar(255)
declare @tables varchar(255)
declare @where varchar(2000)

>
These are unused and should be removed. (Unless they're used in code
that you edited out because it wasn't relevant to the problem at hand.)
>

IF @rpt_type = 'GRP'

>

IF @rpt_type NOT IN ('LOC','GRP','R ES','REG')

>
The blocks following these appear to be identical. I recommend removing
the latter, and adding the following above the first INSERT INTO block:
>
IF @rpt_type NOT IN ('LOC','GRP','R ES','REG') THEN
SET @rpt_type = 'GRP'
>

The simplified result looks like:

>

Sched2 Resource1 Supply1 Order5
Sched2 Resource1 Supply1 Order6
Sched2 Resource1 Supply3 Order5
Sched2 Resource1 Supply3 Order6
Sched2 Resource2 Supply1 Order5
Sched2 Resource2 Supply1 Order6
Sched2 Resource2 Supply3 Order5
Sched2 Resource2 Supply3 Order6

>
Oversimplified. Are these SchedID, ResourceUsed, SuppliesVal, and
OrderID?
>

However, I want the result to look like:

>

Sched2 Resource1 Supply1 Order5
Sched2 Resource2 Supply3 Order6

>
We need more information about all the tables involved in the stored
procedure's final query, specifically
>
a) whether their relationships are 1:1 or 1:N or M:N
>
b) what criteria tell us that Resource1 should associate only with
Supply1 (not Supply3) and Order5 (not Order6), and similarly
for Resource2 and Supply3 (not Supply1) and Order6 (not Order5)

[Ed Murphy]

Re: Stored procedure returns duplicates

yin_n_yang74@ya hoo.com wrote:

Resource, Supply and Order are not directly related to each other.
There is no association that you were asking about in b).

> b) what criteria tell us that Resource1 should associate only with
> Supply1 (not Supply3) and Order5 (not Order6), and similarly
> for Resource2 and Supply3 (not Supply1) and Order6 (not Order5)

Then it sounds like, instead of this monstrosity of a stored
procedure, what you really want is three Crystal subreports
side by side.

Since we are pulling from two databases and using parameters, our
solution has been to use a stored procedure. Crystal Reports (v 8.5)
allows only one stored procedure.

You can do this with views instead, e.g.
create view vTable2 as select * from other_database. dbo.Table2

[Erland Sommarskog]

Re: Stored procedure returns duplicates

(yin_n_yang74@y ahoo.com) writes:

The final query has all the columns specified in SELECT. Yes, your
guess is correct - SchedID, ResourceUsed, SuppliesVal, and OrderID
were what was on my mind as I typed the example.
>
Their relations are as follows:
>
Schedule to Resource is 1:N
Schedule to Supply is 1:N
Schedule to Order is 1:N
Order to Food is 1:N
>
Resource, Supply and Order are not directly related to each other.
There is no association that you were asking about in b).

So when you want:

Sched2 Resource1 Supply1 Order5
Sched2 Resource2 Supply3 Order6

That's completely arbitrary, and you could just as well be satisfied
with:

Sched2 Resource1 Supply3 Order6
Sched2 Resource2 Supply1 Order5

Or any other combination? Get data into yet another temp table,
and

SELECT schedule, resource, min(Supply), Min(Order)
FROM #temp
GROUP BY schedule, resource

But arbitrary results sets do not really make sense to me.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Hugo Kornelis]

Re: Stored procedure returns duplicates

On 9 Apr 2007 15:42:24 -0700, yin_n_yang74@ya hoo.com wrote:

>I am trying to create a report in Crystal Reports (v 8.5). I have a
>stored procedure to pull data from two databases and parameters.
>There are multiple one-to-many relationships and the stored procedure
>returns duplicates; e.g., one schedule may have multiple resources,
>supplies, and/or orders (and one order may have multiple foods). Is
>there a way to stop the duplication?

(snip)

Hi yin_n_yang74,

The first time you posted this question, I advised you to find a client
side solution and provided an outline of the algorithm to use. I also
pointed you to my bog entry that details a possible way to solve this
server-side, in case a client-side solution is not possible.

You now reposted the problem, with more detail, but my answer remains
the same. Either have Crystal Reports open three datasets and process
data from all of them at the same time, pairing data from the datasets
to form formatted output lines - or read my blog article at

http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/14/relating-unrelated-rows.aspx

if you really prefer a server-side solution and don't mind the
performance hit this will incur.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[--CELKO--]

Re: Stored procedure returns duplicates

>Any suggestion is greatly appreciated. <<

EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.

This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!

Your code is so awful, you even use the "tbl-" prefixes to tell us you
have no idea about RDBMS! You keep converting dates to strings because
you are writing COBOL in SQL and want strings!

Why do your have "CREATE TABLE #tmp_sched" when view would work?
Answer: because magnetic tape files have to be materialized

Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.

You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way
spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFI ER OFF", etc.?

You need help you cannot get on a newsgroup.