2 Replies Latest reply: Jun 7, 2013 8:26 AM by dhalek RSS

    case condition is not working in attribute

    user605933
      I need the output for the below case like if attribute10 is not there ther attribute9 value need to show but it is not working. pls. advise

      {
      SELECT CASE WHEN ATTRIBUTE10 IS NULL THEN
      ATTRIBUTE9
      WHEN ATTRIBUTE9 IS NULL THEN
      ATTRIBUTE8
      WHEN ATTRIBUTE8 IS NULL THEN
      ATTRIBUTE7
      WHEN ATTRIBUTE7 IS NULL THEN
      ATTRIBUTE6
      WHEN ATTRIBUTE6 IS NULL THEN
      ATTRIBUTE5
      WHEN ATTRIBUTE5 IS NULL THEN
      ATTRIBUTE4
      WHEN ATTRIBUTE4 IS NULL THEN
      ATTRIBUTE3
      WHEN ATTRIBUTE3 IS NULL THEN
      ATTRIBUTE2
      WHEN ATTRIBUTE2 IS NULL THEN
      ATTRIBUTE1
      -- WHEN ATTRIBUTE1 is nulL then
      -- TO_CHAR(need_by_date,'DD-MON-YY')
      ELSE
      ATTRIBUTE1
      -- TO_CHAR(need_by_date,'DD-MON-YY')
      -- TO_CHAR(promised_date,'DD-MON-YY')
      END TEST
      FROM po_line_locations_all WHERE po_header_id=724372;
      }
        • 1. Re: case condition is not working in attribute
          ThAn
          Hi,

          if you are trying to achieve something like a "top down" select in the manner like this:

          if Attribute10 null then pick Attribute9 when Attribute9 is null pick Attribute8... when attribute2 is null then attribute1

          your statement will not work.

          As you did not nest the case statements, the first condition met (e.g. Attribute10 is null) will return the value of this
          case -> Attribute9, no matter if it's null or not.
          To achieve the situation described above, you'll have to do something like this:

          SELECT CASE
          WHEN ATTRIBUTE10 IS NULL
          THEN
          (SELECT CASE
          WHEN ATTRIBUTE9 IS NULL
          THEN
          (SELECT CASE WHEN attribute8 IS NULL THEN
          (
          select case when attribute7 is null then
          (
          select case when attribute6 is null then
          (
          select case when attribute5 is null then
          (
          select case when attribute4 is null then
          (
          select case when attribute3 is null then
          (
          select case when attribute2 is null then
          attribute1
          else
          attribute2
          end
          from po_line_locations_all
          )
          else
          attribute3
          end
          from po_line_locations_all
          )
          else attribute4
          end
          from po_line_locations_all
          )
          else
          attribute5
          end
          from po_line_locations_all
          )
          else attribute6 end
          from po_line_locations_all
          )
          else attribute7
          end
          from po_line_locations_all
          )
          ELSE attribute8 END
          FROM po_line_locations_all)
          ELSE
          attribute9
          END
          FROM po_line_locations_all)
          ELSE
          ATTRIBUTE1
          END
          TEST
          FROM po_line_locations_all;

          Regards
          • 2. Re: case condition is not working in attribute
            dhalek
            You simply could use the coalesce function which is kind of generalized nvl:
            select
               coalesce(attribute10,attribute9,attribute8,attribute7,attribute6,attribute5,attribute4,attribute3,attribute2,...) 
            from po_Line_locations_all;
            BTW this is the wrong forum for such questions, since this one is only for querstions about the Oracle SQL Developer tool.

            HTH,
            dhalek