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) ;
1条答案
按热度按时间gr8qqesn1#
ORA-01427：单行子查询返回多行错误iz，这是由sql引起的（来自问题代码）
如果对一列使用聚合函数[ max（TARIKHIJAD）]，并对同一列进行分组，则会得到所有不同的行，而不是Max行。
假设您的数据如下所示：
| 塔里基贾德|埃什特拉克|
| - -|- -|
| 2022年10月1日|美国汽车协会|
| 2022年10月15日|血脑屏障|
| 2022年10月16日|美国汽车协会|
| 2022年10月28日|美国汽车协会|
上述sql将产生如下结果：
在您的评论中，您已经更正了这一点，但仍然存在相同的错误，因为它看起来像这里：
如果此select语句位于CASE表达式中的THEN关键字之后，则它应生成一个且仅一个值。如果它作为第二个参数位于Nvl函数中，则规则相同。无论是哪种情况，您都希望在特定条件下为列赋值。
这意味着您必须筛选结果值。您需要的内容如下所示：
类似NVL（SUBSTR（FROM_DATE，1，10））的表达式是错误的，主要是因为如果FROM_DATE为空，则它也为空，并且其中的任何子字符串也将为空-这意味着SUBSTR不执行任何操作。它应该只是NVL（FROM_DATE，some_value）。
此致