Oracle Query Help

Please provide sample data and expected results.

I think a simple NVL with a simple CASE statement will work but I'm not completely understanding what you want.

Guessing something like:
NVL(consignmenttype , case substr( fid.PO_NUMBER ,1,1) when '4' then 'GOT A 4' when '9' then 'GOT A 9' end)

I'm just not sure what you want to use in place of the 'GOT A' strings.

Vendor PO      Consignment
45106876      Saic Owned
                     Review
95103996      Consignment

 Please see above the sample result set.This is the case for only when the consignment_type is null.
But the first 2 conditions on the case statement remains the same.

Is this what you are after?

select
...
case
    when FID.CONSIGNMENT_TYPE LIKE 'SAIC%'   or (consignmenttype is null and substr( fid.PO_NUMBER ,1,1) = '4' ) THEN 'SAIC OWNED'
    WHEN FID.CONSIGNMENT_TYPE LIKE 'Consignment%' or (consignmenttype is null and substr( fid.PO_NUMBER ,1,1) = '9' ) THEN 'Consignment'
    when fid.CONSIGNMENT_TYPE is null then 'Review'
    end as  CONSIGNMENTTYPE
...

the above did not work.Its putting review on all the consignment_type that has a null.Its not flagging them based on vendor_po beginning with '4' or '9'

Sorry just an update when po# is null we mark it as 'Review'
So for the thrid condition that if the fid.consignmentype is null we check to see if it beigns with 9 or 4 or null(then review)

>>So for the thrid condition that if the fid.consignmentype is null we check to see if it beigns with 9 or 4 or null(then review)

You can never get there.  If consignmentype is null and po_number begins with a 4, you stated to set it to 'SAIC' it null and a 9, set it to 'Consignment'

So you will never get to the third case where it is null and starts with a 4 or 9.

Please add new values to the test case and provide updated expected results.

the third case is the check on null for po_number and not consignmenttype

If the consignment flag field can’t find a consignment or a SAIC owned attribute, then review the PO field…and if PO field number begins with a 4 then “SAIC Owned” or if PO field number begins with a 9 then “consignment”, if no PO #, then make “review”.

>>the third case is the check on null for po_number and not consignmenttype

That doesn't make sense either.

If I replace consignmenttype  with po_number in what you posted I get:
So for the thrid condition that if the po_number is null we check to see if it beigns with 9 or 4 or null(then review)

How can it be null and begin with a 4 or 9?

>>If the consignment flag field can’t find a consignment...

You can keep explaining it all day long.  I'm not understanding.

I want sample data and expected results.  Please update my test case to add/change rows then post the results you want from that data.



I've posted the basic logic.  All you should need to do is move the logic around inside the case statement to whatever your requirements are.

Star79 the best method for getting accurate results quickly when asking technical questions is to:

1. supply "sample data", and based only on that data
2. the "expected result"

It is your responsibility to provide the sample data, if we do this for you we could be making assumptions that are incorrect. Also, because you have the actual tables involved locating  few sample records generally isn't that difficult.

By following this method you clearly communicate your requirements without relying on purely on words. Plus the discipline of following this method helps you identify all requirements.

i used the following:
 case
     when CONSIGNMENT_TYPE LIKE 'SAIC%'   or (consignment_type is null and substr( PO_NUMBER ,1,1) = '4' ) THEN 'SAIC OWNED'
     WHEN CONSIGNMENT_TYPE LIKE 'Consignment%' or (consignment_type is null and substr( PO_NUMBER ,1,1) = '9' ) THEN 'Consignment'
     when CONSIGNMENT_TYPE is null and po_number is null then 'Review'
     end as  CONSIGNMENTTYPE,
as part of my solution