Select statement inside NVL function 1

[ekta22]

Hi,

I am trying to use a select statement inside the NVL function but keep getting a Missing Expression error. Cannot figure out what I am doing wrong. Below is my code

SELECT
  F.TYPE,
  L.CODE,
  L.ID,
  L.STATUS,
  L.START_DATE,
  L.END_DATE,
  NVL(SELECT ID
   FROM LOG LY
   WHERE LY.CODE = 51
   AND LY.TYPE = L.TYPE
   AND LY.IDENT = L.IDENT
   AND LY.STATUS = 'X'   
   AND LY.END_DATE BETWEEN L.START_DATE AND DECODE(L.CODE, 58, L.END_DATE + 10/24, L.END_DATE)
   AND ROWNUM = 1, 0) CERTIFIED_LOG
  FROM FAC F 
  JOIN LOG L ON L.ID = F.ID

Thanks for taking a look at it.

-E

 

[carp]

I think what you are doing wrong is you are putting a query into a NVL function. If you make your query an inline view, I think it will go better for you:

SELECT
  F.TYPE,
  L.CODE,
  L.ID,
  L.STATUS,
  L.START_DATE,
  L.END_DATE,
  NVL(iv.id,0) CERTIFIED_LOG
  FROM FAC F 
  JOIN LOG L ON L.ID = F.ID
  JOIN (SELECT ID, ident, type, end_date
          FROM LOG LY
          WHERE LY.CODE = 51
                AND LY.STATUS = 'X') iv
   ON iv.IDENT = L.IDENT
      AND iv.TYPE = L.TYPE
      AND iv.END_DATE BETWEEN L.START_DATE AND DECODE(L.CODE, 58, L.END_DATE + 10/24, L.END_DATE);

You may need to play with this a little to get iv constrained to a single value, but this should get you pretty close to what you want.

 

[Beilstwh]

You have to incase the embedded select inside a case statement. see below


raydw@rayora1>SELECT NVL(SELECT SYSDATE FROM DUAL,TRUNC(SYSDATE)) FROM DUAL;
SELECT NVL(SELECT SYSDATE FROM DUAL,TRUNC(SYSDATE)) FROM DUAL
*
ERROR at line 1:
ORA-00936: missing expression


raydw@rayora1>EDIT
Wrote file afiedt.buf

1* SELECT NVL((SELECT SYSDATE FROM DUAL),TRUNC(SYSDATE)) FROM DUAL
raydw@rayora1>/

NVL((SELE
---------
25-MAY-10

Bill
Lead Application Developer
New York State, USA

 

[Beilstwh]

Sorry meant parens, not case..lol

Bill
Lead Application Developer
New York State, USA

 

[carp]

Nice addition, Bill. I'll need to add this to my bag o' tricks!