Is is possible to use a Case in a where statement? If
not how can I achieve having not having multiple ifs
to check the values of the where.. This is the DBPROC
that I have created. I want to be able to just use the
case so that I do not need to have all the Ifs?
Thanks.. any help is appreciated.
DROP DBPROC DBA.EMAYA_GET_SIITEMSBYREP
//
CREATE DBPROC DBA.EMAYA_GET_SIITEMSBYREP
(
IN SALES_PERSON_ID INTEGER,
IN CLIENT_ID INTEGER,
IN CLIENT_SITE_ID INTEGER,
IN FROM_DATE DATE,
IN TO_DATE DATE,
IN DOC_STATUS_ID INTEGER,
IN PROCESS_BY_D INTEGER
)
RETURNS CURSOR AS
$CURSOR = 'GET_SIITEMSBYREP';
if (SALES_PERSON_ID = 0) AND (CLIENT_ID = 0)
THEN
BEGIN
DECLARE :$CURSOR CURSOR FOR
SELECT SI.SALESREP_ID
, EE.FULLNAME, SI.SITE_NAME
, SIITEM.DESCR
, PC.CAT_NAME
, SIITEM.PROD_ID
, SIITEM.PROD_CODE
, SUM(SIITEM.QUANTITY) AS PROD_QTY
, SUM(SIITEM.QTY_DELIVERED) AS
PROD_DEL_QTY
, SUM( SIITEM.QTY_DELIVERED *
SIITEM.NET_PRICE) AS TOT_PRICE
FROM DBA.EMAYA_SALES_INVOICE SI
LEFT OUTER JOIN DBA.EMAYA_SI_ITEM SIITEM
ON SI.SALES_INVOICE_ID = SIITEM.SALES_INVOICE_ID
LEFT OUTER JOIN DBA.EMAYA_EMPLOYEE EE ON
SI.SALESREP_ID = EE.EMP_ID
LEFT OUTER JOIN DBA.EMAYA_PRODUCT PR ON
SIITEM.PROD_ID = PR.PROD_ID
LEFT OUTER JOIN
DBA.EMAYA_PRODUCT_CATEGORY PC ON PR.PROD_CATEGORY_ID
= PC.PROD_CAT_ID
WHERE SI.DELIVERY_DATE BETWEEN :FROM_DATE AND
:TO_DATE
GROUP BY SI.SALESREP_ID, EE.FULLNAME ,
SI.SITE_NAME, SIITEM.DESCR, PC.CAT_NAME,
SIITEM.PROD_ID, SIITEM.PROD_CODE, SIITEM.QUANTITY,
SIITEM.QTY_DELIVERED;
END
ELSE IF (SALES_PERSON_ID <> 0) AND (CLIENT_ID <>
0) then
begin
DECLARE :$CURSOR CURSOR FOR
SELECT SI.SALESREP_ID
, EE.FULLNAME, SI.SITE_NAME
, SIITEM.DESCR
, PC.CAT_NAME
, SIITEM.PROD_ID
, SIITEM.PROD_CODE
, SUM(SIITEM.QUANTITY) AS PROD_QTY
, SUM(SIITEM.QTY_DELIVERED) AS
PROD_DEL_QTY
, SUM( SIITEM.QTY_DELIVERED *
SIITEM.NET_PRICE) AS TOT_PRICE
FROM DBA.EMAYA_SALES_INVOICE SI
LEFT OUTER JOIN DBA.EMAYA_SI_ITEM SIITEM
ON SI.SALES_INVOICE_ID = SIITEM.SALES_INVOICE_ID
LEFT OUTER JOIN DBA.EMAYA_EMPLOYEE EE ON
SI.SALESREP_ID = EE.EMP_ID
LEFT OUTER JOIN DBA.EMAYA_PRODUCT PR ON
SIITEM.PROD_ID = PR.PROD_ID
LEFT OUTER JOIN
DBA.EMAYA_PRODUCT_CATEGORY PC ON PR.PROD_CATEGORY_ID
= PC.PROD_CAT_ID
WHERE SI.DELIVERY_DATE BETWEEN :FROM_DATE AND
:TO_DATE AND SI.SALESREP_ID = :SALES_PERSON_ID AND
SI.CLIENT_ID = :CLIENT_ID
GROUP BY SI.SALESREP_ID, EE.FULLNAME ,
SI.SITE_NAME, SIITEM.DESCR, PC.CAT_NAME,
SIITEM.PROD_ID, SIITEM.PROD_CODE, SIITEM.QUANTITY,
SIITEM.QTY_DELIVERED;
END
ELSE IF (SALES_PERSON_ID <> 0) AND (CLIENT_ID =
0) then
begin
DECLARE :$CURSOR CURSOR FOR
SELECT SI.SALESREP_ID
, EE.FULLNAME, SI.SITE_NAME
, SIITEM.DESCR
, PC.CAT_NAME
, SIITEM.PROD_ID
, SIITEM.PROD_CODE
, SUM(SIITEM.QUANTITY) AS PROD_QTY
, SUM(SIITEM.QTY_DELIVERED) AS
PROD_DEL_QTY
, SUM( SIITEM.QTY_DELIVERED *
SIITEM.NET_PRICE) AS TOT_PRICE
FROM DBA.EMAYA_SALES_INVOICE SI
LEFT OUTER JOIN DBA.EMAYA_SI_ITEM SIITEM
ON SI.SALES_INVOICE_ID = SIITEM.SALES_INVOICE_ID
LEFT OUTER JOIN DBA.EMAYA_EMPLOYEE EE ON
SI.SALESREP_ID = EE.EMP_ID
LEFT OUTER JOIN DBA.EMAYA_PRODUCT PR ON
SIITEM.PROD_ID = PR.PROD_ID
LEFT OUTER JOIN
DBA.EMAYA_PRODUCT_CATEGORY PC ON PR.PROD_CATEGORY_ID
= PC.PROD_CAT_ID
WHERE SI.DELIVERY_DATE BETWEEN :FROM_DATE AND
:TO_DATE AND SI.SALESREP_ID = :SALES_PERSON_ID
GROUP BY SI.SALESREP_ID, EE.FULLNAME ,
SI.SITE_NAME, SIITEM.DESCR, PC.CAT_NAME,
SIITEM.PROD_ID, SIITEM.PROD_CODE, SIITEM.QUANTITY,
SIITEM.QTY_DELIVERED;
END
ELSE IF (SALES_PERSON_ID = 0) AND (CLIENT_ID <>
0) then
begin
DECLARE :$CURSOR CURSOR FOR
SELECT SI.SALESREP_ID
, EE.FULLNAME, SI.SITE_NAME
, SIITEM.DESCR
, PC.CAT_NAME
, SIITEM.PROD_ID
, SIITEM.PROD_CODE
, SUM(SIITEM.QUANTITY) AS PROD_QTY
, SUM(SIITEM.QTY_DELIVERED) AS
PROD_DEL_QTY
, SUM( SIITEM.QTY_DELIVERED *
SIITEM.NET_PRICE) AS TOT_PRICE
FROM DBA.EMAYA_SALES_INVOICE SI
LEFT OUTER JOIN DBA.EMAYA_SI_ITEM SIITEM
ON SI.SALES_INVOICE_ID = SIITEM.SALES_INVOICE_ID
LEFT OUTER JOIN DBA.EMAYA_EMPLOYEE EE ON
SI.SALESREP_ID = EE.EMP_ID
LEFT OUTER JOIN DBA.EMAYA_PRODUCT PR ON
SIITEM.PROD_ID = PR.PROD_ID
LEFT OUTER JOIN
DBA.EMAYA_PRODUCT_CATEGORY PC ON PR.PROD_CATEGORY_ID
= PC.PROD_CAT_ID
WHERE SI.DELIVERY_DATE BETWEEN :FROM_DATE AND
:TO_DATE AND SI.CLIENT_ID = :CLIENT_ID
GROUP BY SI.SALESREP_ID, EE.FULLNAME ,
SI.SITE_NAME, SIITEM.DESCR, PC.CAT_NAME,
SIITEM.PROD_ID, SIITEM.PROD_CODE, SIITEM.QUANTITY,
SIITEM.QTY_DELIVERED;
END;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
