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) ;

CodePudding user response：

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:

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...