|
|
Posted by Kart on 10/18/06 21:39
Hi,
Cant you use Derived tables? I havent gone through the queries though.
But generally when I need to query on results from a query, I use a
derived table.
Its something like this....
select * from
(select * from Employee) A
Dont know whether it will work in your case....
Kart
MackTheKnife wrote:
> I have the following 3 SQL statements that need to be combined, if
> possible. The output of one feeds the input of the next. I need to view
> all of the defined output fields (the output needs to be used in a
> Crystal Report).
>
> The SQL Follows:
> /* Input is ISBN (vendor_part_number) */
> QUERY_1 - returns 1 record
> select p.product_id, v.name, m.description, p.author, p.title,
> p.revision_number, p.copyright_edition, p.vendor_part_number,
> p.conforming_flag,
> m.code, mp.unit_price_product, mm.quota_price
> from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
> T_MULTILIST_PRODUCT mp, T_MULTILIST m,
> T_MULTILIST_MEMBERSHIP mm where
> /* p.vendor_part_number == input */
> p.vendor_part_number = '0153364475' and p.medium_type =
> 'TEXTBOOK' and
> p.product_id = pv.product_id and pv.type = 'CONTRACT' and
> pv.vendor_id = v.id and
> p.product_id = mp.product_id and
> m.code = mp.multilist_code and m.proclamation_year =
> mp.proclamation_year
> and m.proclamation_seq_id = mp.proclamation_seq_id and
> m.code = mm.multilist_code and m.proclamation_year =
> mm.proclamation_year
> and m.proclamation_seq_id = mm.proclamation_seq_id
> /* The above should return a single record */
>
> QUERY_2 - returns 2 records
> select p.product_id, p.consumable, p.title, p.copyright_edition,
> p.vendor_part_number, p.product_type,
> p.item_type, p.hardware_required
> from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
> 'AID'
> or pr.relationship_type = 'KIT') and
> p.product_id = pr.child_product_id and pr.parent_product_id =
> 90321
> /* 90321 = result from above: pr.parent_product_id = p.product_id
> */
>
> QUERY_3 - returns 18 records
> select p.product_id, p.consumable, p.title, p.copyright_edition,
> p.vendor_part_number, p.product_type,
> p.item_type, p.hardware_required
> from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
> 'AID'
> or pr.relationship_type = 'KIT') and
> p.product_id = pr.child_product_id and pr.parent_product_id in
> (90322, 90323)
> /* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
> p.product_id */
>
>
> Only 21 records are returned from these combined queries. I need access
> to all of them even though there are 3 different resultsets, 2 of which
> contain the same fields. Is there a way to simplify this into a stored
> procedure or a view that can take 1 input parameter? It needs to be
> used in a Crystal Report, which is limited in its handling of these
> types of complex queries.
Navigation:
[Reply to this message]
|