Posted by yin_n_yang74 on 04/10/07 15:59

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...@socal.rr.com> wrote:
> yin_n_yan...@yahoo.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','RES','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','RES','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)

[Back to original message]