problem with the date parameter in sql script 
Author Message
 problem with the date parameter in sql script

I need to write a sql*plus script that will retrieve all the records that have a date parameter null and not null.
In my sql statement, I have four paramenters: 1) &1 , from po number 2) &2, to po number 3) &3, from promised date 4) &4 , to promised date.  In the data base, the promised date contains a null value.  I need to retrieve all the po with the promise date is null and not null.
****************************
Select poh.segment1 || '?',mtldd.segment1 || '?',
  pl.quantity || '?',  pll.promised_date || '?',
 substr(pll.attribute1,1,1) || '?', substr(pll.attribute1,2,2) || '?', '???0'
 From po_headers_All poh, po_lines_all pl, po_line_locations_all pll,
 mtl_system_items mtldd
 Where  poh.po_header_id = pl.po_header_id
 and mtldd.inventory_item_id = pl.item_id
 and mtldd.organization_id = 303
 And pll.po_header_id = poh.po_header_id
 And pll.po_line_id = pl.po_line_id
 and (poh.segment1 >= nvl('&1', poh.segment1)
 and poh.segment1 <= nvl('&2', poh.segment1))
AND (PLL.PROMISED_date >= to_date('&3','DD-MON-YYYY')
OR (pll.promised_date is null OR (pll.promised_date is  not null)))
AND (PLL.PROMISED_date <= to_date('&4','DD-MON-YYYY')
OR (pll.promised_date is null or (pll.promised_date is  not null)))

If I enter the value for     &1 = (NULL)
                                          &2  (NULL)
                                        &3 = 05 MAR-99
                                       & 4 = 06-MAR-99
   the query retrieves all the po included 01-DEC-98, NOV-23-99, 02-FEB-99 etc..
  it will not retrives the records according to the promise date criteria.

if I change the promised date paramenter to include the (or with only the null value )
then I have a different problem
AND (PLL.PROMISED_date >= to_date('&3','DD-MON-YYYY')
OR (pll.promised_date is null ))
AND (PLL.PROMISED_date <= to_date('&4','DD-MON-YYYY')
or (pll.promised_date is  null))

                     &1 = 10001992
                     &2 =  10002002
                      &3 =   (null)
                    &4 = (null)
the query will retrieve all records base on the search criteria that have a promise date that is null.
But I need to create a query that would retrive all the records that have a date is null and not null.

Please advice!


Thanks
Christine



Sat, 11 Aug 2001 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Use shell script parameter in sql script?!

2. Batch Scripts How to pass parameters back to calling script

3. Having parameter problems scripting DTS execution from ASP

4. Script Parameter Passed to Ontape Restore script

5. Script Parameter Passed to Ontape Restore script

6. Script Parameter Passed to Ontape Restore script

7. SQL-DMO (Active script within job step) Problems with Script statement

8. Date Parameter Problem

9. Passing Date Parameter Problem

10. PROBLEM USING DATE FOR INPUT PARAMETER

11. Repost: Problem with Date Parameter

12. Query date parameter problem


 
Powered by phpBB® Forum Software