Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

adding dynamic where/and condition to the cursor

672589Oct 21 2009 — edited Oct 21 2009
Hi I have a cursor as below

SELECT ihev.invoice_id, MAX (ihev.custname) custname, COUNT (*)
invlncnt,
NVL (SUM (i.itemtotalservices), 0) invamt,
MAX (i.itemcurrency) invcur
FROM invoiceitems i,invoice_header_ebs_v ihev
,invoicetracking it
WHERE ihev.invoicestartdt = '01-FEB-2009'
AND ihev.reporting_region = 'N. America'
AND ihev.invoice_type ! = 'N/A'
AND i.invoice_id = ihev.invoice_id
AND ihev.invoice_id = it.invoice_id
AND it.arloaded = 0
AND ihev.ready_to_print_flg = 1
AND i.status = 1
AND 1 = (SELECT COUNT (DISTINCT i2.itemcurrency)
FROM invoiceitems i2
WHERE i2.invoice_id = ihev.invoice_id AND i2.status = 1)
GROUP BY ihev.invoice_id
HAVING SUM (i.itemtotalservices) > 0
ORDER BY 1

this is used in an interface code.We are using the same code for 2 different programs 1 for loading invoices into stage table and 2 for loading invoices into stage table and publish the details as a report.

the requirement is when we want to use for step 1 it should have the where clause like
AND it.arloaded = 0(posiible values are 0 and 1)
AND ihev.ready_to_print_flg = 1(posiible values are 0 and 1)

when we use for report then it shuld have the where clause as
--AND it.arloaded = 0 this condition not to be included
AND ihev.ready_to_print_flg = 1 this condition not to be included

is there any way to achieve the requirement.

Thanks,
Raghu
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 18 2009
Added on Oct 21 2009
1 comment
467 views