在oracle中执行nvl时出错

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

Answer 1

ORA-01427:单行子查询返回由sql(来自问题码)引起的多行错误

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

如果你在一列[max(TARIKHIJAD)]上使用聚合函数，并在同一列上进行分组，你将得到所有不同的行，而不是max。
让我们假设您的数据看起来像这样:

tbody> <<tr>

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