Error in execution case when nvl in oracle

Question

SELECT cast(ID as number) AS ID,
 cast(MARKETER_ID as NUMBER) AS MARKETER_ID,
 CAST(NEW_MARKETING_BANKS_ID AS NUMBER) AS NEW_MARKETING_BANKS_ID_ASSIGNE,
 To_char(To_date(REQUEST_DATE,'YYYY/MM/DD','NLS_CALENDAR=''Gregorian''')) as TARIKH_DARKHAST,
 NEW_MARKETING_BANKS_NAME,
 STATE,
 VIN AS SHASI,
 cast(SUBSCRIPTION_CODE as number) as ESHTERAKID,
 PRODUCT_NAME,
 MARKETING_GROUP,
 PERSON_TYPE,
 TO_DATE_STR,
 case when NVL(SUBSTR(FROM_DATE,1,10) = 'NULL' then (select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH GROUP BY TARIKHIJAD,ESHTERAKID))
 else (SUBSTR(FROM_DATE,1,10) end AS TARIKH_ENGHEZA,
 IS_IKE,
 CANCELABLE,
 CAST(MARKETING_GROUP_ID AS NUMBER) AS MARKETING_GROUP_ID
 
FROM WH.VIW_WH_MARKETING_ASSIGNEE where (PERSON_ID is not null) 
and (MARKETER_ID is not null) AND (SUBSCRIPTION_CODE IS NOT NULL) and (REQUEST_DATE is not null)
> ORA-00909: invalid number of arguments
SELECT 
 CAST(NEW_MARKETING_BANKS_ID AS NUMBER) AS NEW_MARKETING_BANKS_ID_ASSIGNE,
 To_char(To_date(REQUEST_DATE,'YYYY/MM/DD','NLS_CALENDAR=''Gregorian''')) as TARIKH_DARKHAST,
 STATE,
 cast(SUBSCRIPTION_CODE as number) as ESHTERAKID,
 PRODUCT_NAME,
 MARKETING_GROUP,
 PERSON_TYPE,
 TO_DATE_STR,
-- Note below CASE
case when NVL(SUBSTR(FROM_DATE,1,10) = 'NULL' then (select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH GROUP BY TARIKHIJAD,ESHTERAKID))
else (SUBSTR(FROM_DATE,1,10) end AS TARIKH_ENGHEZA
 
--
FROM WH.VIW_WH_MARKETING_ASSIGNEE where (PERSON_ID is not null) 
and (MARKETER_ID is not null) AND (SUBSCRIPTION_CODE IS NOT NULL) and (REQUEST_DATE is not null) ;

• Chris Albert: Your question needs some work so the community can better help you. Take a look at how to ask a good question and give it another try.
• JO OMIDI: My question was the error in using case when
• Alex Poole: You nvl doesn’t have a second argument, or a closing parenthesis. I guess it should nvl(substr(...), ‘NULL’) = ... but why not just check for null directly?
• JO OMIDI: The nvl argument selects a replacement for each field. I want the case when (NVL to use a query with another select to replace in NVL. for example: case when (NVL(SUBSTR(FROM_DATE,1,10)), then (select max(date_modif),ID FROM QV_JOZEAT_RIALI_FROSH GROUP BY date_modif,ID)) else (SUBSTR(FROM_DATE,1,10) end AS exp_date,
• JO OMIDI: I try this: (NVL(SUBSTR(FROM_DATE,1,10), (select max(TARIKHIJAD) FROM QV_JOZEAT_RIALI_FROSH GROUP BY ESHTERAKID)) ) and answer was: ORA-01427: single-row subquery returns more than one row

Answer 1

The ORA-01427: single-row subquery returns more than one row error iz caused by your sql (from the question code)

select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH GROUP BY TARIKHIJAD,ESHTERAKID

If you use aggregate function on one column [ max(TARIKHIJAD) ] and do the grouping on the same column you will get all the different rows and not the Max one.
Lets suppose that your data looks like this:

TARIKHIJAD	ESHTERAKID
01-OCT-22	AAA
15-OCT-22	BBB
16-OCT-22	AAA
28-OCT-22	AAA

The above sql will result like below:

select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH GROUP BY TARIKHIJAD,ESHTERAKID
-- R e s u l t :
-- MAX(TARIKHIJAD) ESHTERAKID
-- --------------- ----------
-- 16-OCT-22 AAA 
-- 01-OCT-22 AAA 
-- 28-OCT-22 AAA 
-- 15-OCT-22 BBB 

In your comment you have corrected this but there is still the same error because it looks like here:

select max(TARIKHIJAD) FROM QV_JOZEAT_RIALI_FROSH GROUP BY ESHTERAKID
-- MAX(TARIKHIJAD)
-- --------------- --> needs filtering 
-- 15-OCT-22 --> this one is for 'BBB' 
-- 28-OCT-22 --> this one is for 'AAA' 

If this select statement is after THEN keyword in CASE expression it should result in one and only one value. The same rule stands if it is within Nvl function as the 2nd argument. In either case you want to assign a value to the column under certain condition.
This means that you have to filter the resulting value. What you need would look something like here:

-- 1.
...
CASE WHEN FROM_DATE Is Null Then (select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH WHERE ESHTERAKID = 'BBB' GROUP BY ESHTERAKID) ELSE FROM_DATE END
...
-- or 2.
...
Nvl(FROM_DATE, (select max(TARIKHIJAD),ESHTERAKID FROM QV_JOZEAT_RIALI_FROSH WHERE ESHTERAKID = 'AAA' GROUP BY ESHTERAKID))
...

Expression like NVL(SUBSTR(FROM_DATE,1,10)) is wrong primarily because if FROM_DATE is null then it is null and any substring within will be null too - meaning that SUBSTR does not do anything. It should be just NVL(FROM_DATE, some_value).
Regards...