| wgblackmon@yahoo.com 2006-04-06, 8:23 pm |
| OK, here's a sample of one UNION ALL with 2 separate statements.
Thanks!
SELECT
T_MULTILIST.description as Rec_Type, 'Non-Consumables' as Super_Type,
'' as Other_Type, 'Continuing Contracts' as Proc_Type,
"9-12" as grade,
T_MULTILIST.code, T_MULTILIST.description, V_ORDER_DETAIL.unit_price
AS amount
FROM ((((T_MULTILIST T_MULTILIST INNER JOIN T_MULTILIST_PRODUCT
T_MULTILIST_PRODUCT ON
((T_MULTILIST. code=T_MULTILIST_PRO
DUCT.multilist_code)
AND
(T_MULTILIST. proclamation_year=T_
MULTILIST_PRODUCT.proclamation_year))
AND
(T_MULTILIST. proclamation_seq_id=
T_MULTILIST_PRODUCT. proclamation_seq_id)
)
INNER JOIN V_ORDER_DETAIL V_ORDER_DETAIL ON
(T_MULTILIST_PRODUCT
. multilist_code=V_ORD
ER_DETAIL.multilist_code) AND
(T_MULTILIST_PRODUCT
. product_id=V_ORDER_D
ETAIL.product_id))
INNER JOIN T_PRODUCT T_PRODUCT ON
V_ORDER_DETAIL. product_id=T_PRODUCT
.product_id) INNER JOIN
T_REQUISITION T_REQUISITION ON
V_ORDER_DETAIL. requisition_id=T_REQ
UISITION.id)
INNER JOIN V_VENDOR_PRODUCT V_VENDOR_PRODUCT ON
T_PRODUCT. product_id=V_VENDOR_
PRODUCT.product_id,
T_MULTILIST_GRADE
WHERE V_ORDER_DETAIL. ord_latest_record_fl
ag=1 AND
V_ORDER_DETAIL. ordtl_latest_record_
flag=1 AND
V_ORDER_DETAIL.active_flag=1
AND T_MULTILIST. code=T_MULTILIST_GRA
DE.multilist_code
AND (T_REQUISITION. requested_shipment_d
ate >= '05/31/2005' AND
T_REQUISITION. requested_shipment_d
ate <= '06/01/2006')
AND T_REQUISITION. latest_record_flag=1
AND T_MULTILIST. expiration_year[colo
r=darkred]
> '2005' AND V_VENDOR_PRODUCT. product_vendor_type=
'CONTRACT'[/color]
AND (V_ORDER_DETAIL.depository_type<>'SCHOOL')
AND T_PRODUCT. medium_type='TEXTBOO
K'
AND (T_MULTILIST.code IN ('9252','9041','9042
','9043','9044') )
UNION ALL
SELECT
T_MULTILIST.description as Rec_Type, 'English as a Second Language, K -
Grade 2' as Super_Type, '' as Other_Type, 'Proclamation 2001' as
Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
V_ORDER_DETAIL.unit_price AS amount
FROM T_MULTILIST_GRADE, ((((T_MULTILIST T_MULTILIST INNER JOIN
T_MULTILIST_PRODUCT T_MULTILIST_PRODUCT ON
((T_MULTILIST. code=T_MULTILIST_PRO
DUCT.multilist_code)
AND
(T_MULTILIST. proclamation_year=T_
MULTILIST_PRODUCT.proclamation_year))
AND
(T_MULTILIST. proclamation_seq_id=
T_MULTILIST_PRODUCT. proclamation_seq_id)
)
INNER JOIN V_ORDER_DETAIL V_ORDER_DETAIL ON
(T_MULTILIST_PRODUCT
. multilist_code=V_ORD
ER_DETAIL.multilist_code)
AND (T_MULTILIST_PRODUCT
. product_id=V_ORDER_D
ETAIL.product_id)) INNER
JOIN T_PRODUCT T_PRODUCT ON
V_ORDER_DETAIL. product_id=T_PRODUCT
.product_id)
INNER JOIN T_REQUISITION T_REQUISITION ON
V_ORDER_DETAIL. requisition_id=T_REQ
UISITION.id) INNER JOIN
V_VENDOR_PRODUCT V_VENDOR_PRODUCT ON
T_PRODUCT. product_id=V_VENDOR_
PRODUCT.product_id
WHERE
T_MULTILIST. code=T_MULTILIST_GRA
DE.multilist_code AND
V_ORDER_DETAIL. ord_latest_record_fl
ag=1 AND
V_ORDER_DETAIL. ordtl_latest_record_
flag=1
AND V_ORDER_DETAIL.active_flag=1 AND
T_REQUISITION. latest_record_flag=1
AND
V_VENDOR_PRODUCT. product_vendor_type=
'CONTRACT'
AND V_ORDER_DETAIL.depository_type <>'SCHOOL' AND
T_PRODUCT. medium_type='TEXTBOO
K' AND
T_MULTILIST. implementation_year=
'2005' AND
T_MULTILIST. proclamation_year='2
001'
AND (T_MULTILIST.code IN
('0052','0053','0054
','1052','1053','105
4','2052','2053') )
|