Problem With Sysdate

Hi All,

I have a cursor with ten fields, nine of which could have values or could be null.

I have to find records in the same table that match the nine fields in each row of the cursor, so
for each row, if Field1 has a value and Field2 has a value but the rest of the fields are null,
I have to find every other record that matches the exact values of all nine fields.

To do this I am filling a temporary table using a Group By on all nine fields to get the single row of records
that I need to compare.  I then read that temporary table into a cursor and am reading
line by line to do the comparison.  

In the temporary table I am placing an X into fields that are null using NVL and sysdate for the date field.

Here is how I am inserting into the temporary table using variables with values or null:

INSERT INTO BISWORKSPACE.WS_FACTORS_TEMP
          (IND_COMP_CONSOL_HEADER,
           crew_number,
           customer_number,
           bill_to_address,
           foreman_name,
           po_number,
           requisition_number,
           location,
           week_ending_date,
           tracking_number)
        VALUES
          (v_IND_COMP_CONSOL_HEADER,
           NVL(v_Crew_Number, 'X'),
           NVL(v_Customer_Number, 'X'),
           NVL(v_Bill_To_Address, 'X'),
           NVL(v_Foreman_Name, 'X'),
           NVL(v_Po_Number, 'X'),
           NVL(v_Requisition_Number, 'X'),
           NVL(v_Location, 'X'),
           NVL(v_Week_Ending_Date, sysdate),
           NVL(v_Tracking_Number, 'X'));
           
The problem is that the Week_Ending_Date field is a Date and the rest are VARCHAR2.  
What I need to do is figure out how I can fill the temporary table's Week_Ending_Date field
with something, using the same idea of the X's for the VARCHAR2 fields.  In other words, I wish to be able to deal with null Week_Ending_Date field values or match the date if a date matches.

Here is where I am having the problem.  This sql is supposed to find all rows that match the criteria but it doesn't find any records because of the Week_Ending_Date line.


SELECT t.ref_preinvoicenumber,
             NVL(t.crew_number, 'X') as Crew_Number,
             NVL(t.customer_number, 'X') as Customer_Number,
             NVL(t.bill_to_address, 'X') as bill_to_address,
             NVL(t.foreman_name, 'X') as foreman_name,
             NVL(t.po_number, 'X') as po_number,
             NVL(t.requisition_number, 'X') as requisition_number,
             NVL(t.location, 'X') as location,
             nvl(t.week_ending_date, trunc(sysdate)) as week_ending_date,
             NVL(t.tracking_number, 'X') as tracking_number
        FROM BISPOST.Post_Inv_Consol_Factor_Values t
       WHERE t.ind_comp_consol_header = v_Ind_Comp_Consol_Header
         AND t.timesheet_type <> 2
         and NVL(t.crew_number, 'X') = v_Crew_Number
         and NVL(t.customer_number, 'X') = v_Customer_Number
         and NVL(t.bill_to_address, 'X') = v_bill_to_address
         and NVL(t.foreman_name, 'X') = v_foreman_name
         and NVL(t.po_number, 'X') = v_po_number
         and NVL(t.requisition_number, 'X') = v_requisition_number
         and NVL(t.location, 'X') = v_location
         and NVL(t.week_ending_date, trunc(sysdate)) = to_date(v_week_ending_date)
         and NVL(t.tracking_number, 'X') = v_tracking_number;
         
         
         If I were to plug in the values below from the variables, I get records.
         
         The date field works when I plug in the actual sysdate value as shown below.
         The v_Week_Ending_Date field has a value of 03-DEC-08, so it should work in the code above
         but it doesn't.
         
         Can anyone help me out here?
         
         Thanks in advance.
         
         RichW
         
         SELECT t.ref_preinvoicenumber,
                NVL(t.crew_number, 'X') as Crew_Number,
                NVL(t.customer_number, 'X') as Customer_Number,
                 NVL(t.bill_to_address, 'X') as bill_to_address,
                 NVL(t.foreman_name, 'X') as foreman_name,
                 NVL(t.po_number, 'X') as po_number,
                 NVL(t.requisition_number, 'X') as requisition_number,
                 NVL(t.location, 'X') as location,
                 NVL(t.week_ending_date, trunc(sysdate)) as week_ending_date,
                 NVL(t.tracking_number, 'X') as tracking_number
               FROM BISPOST.Post_Inv_Consol_Factor_Values t
              WHERE t.ind_comp_consol_header = 217
                AND t.timesheet_type <> 2
                and NVL(t.crew_number, 'X') = 'X'
                and NVL(t.customer_number, 'X') = '001540'
                and NVL(t.bill_to_address, 'X') = 'X'
                and NVL(t.foreman_name, 'X') = 'WHEELER,JOHN W'
                and NVL(t.po_number, 'X') = 'X'
                and NVL(t.requisition_number, 'X') = 'X'
                and NVL(t.location, 'X') = 'JAMISON'
                and NVL(t.week_ending_date, trunc(sysdate)) = to_date('03-DEC-08')
                                and NVL(t.tracking_number, 'X') = 'X'

Start Free Trial