Subquery returns more than one row - where clause -decode [message #679868] |
Wed, 08 April 2020 09:18 |
|
New50
Messages: 4 Registered: April 2020
|
Junior Member |
|
|
SELECT *
FROM AR_CASH_RECEIPTS_V acr
WHERE 1 =1
AND acr.receipt_date BETWEEN '01-JAN-19' AND '31-JAN-19'
AND ACR.CURRENCY_CODE IN
DECODE ('NOINR','NOINR',(SELECT CURRENCY_CODE FROM fnd_currencies WHERE CURRENCY_CODE <>'INR'),ACR.CURRENCY_CODE)
;
Please help!
|
|
|
|
|
|
|
|
|
|
|
Re: Subquery returns more than one row - where clause -decode [message #679886 is a reply to message #679882] |
Thu, 09 April 2020 10:11 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
EdStevens wrote on Thu, 09 April 2020 08:43
the above line should be:
and acr.receipt_date between to_date('01-JAN-2019','dd-MON-yyyy') and to_date(31_JAN_2019','dd-MON-yyyy')
Nop. Otherwise you can get:
SQL> select * from dual where sysdate between to_date('09-APR-2020','dd-MON-yyyy') and to_date('10-APR-2020','dd-MON-yyyy');
select * from dual where sysdate between to_date('09-APR-2020','dd-MON-yyyy') and to_date('10-APR-2020','dd-MON-yyyy')
*
ERROR at line 1:
ORA-01843: not a valid month
It should be:
SQL> select * from dual where sysdate between to_date('09-APR-2020','dd-MON-yyyy','nls_date_language=english') and to_date('10-APR-2020','dd-MON-yyyy','nls_date_language=english');
D
-
X
if you want to use TO_CHAR. But best solution is date literals:
SQL> select * from dual where sysdate between date '2020-04-09' and date '2020-04-10';
D
-
X
SY.
[Updated on: Thu, 09 April 2020 10:11] Report message to a moderator
|
|
|
|
Re: Subquery returns more than one row - where clause -decode [message #679892 is a reply to message #679891] |
Thu, 09 April 2020 14:40 |
|
Michel Cadot
Messages: 68648 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Trying to force the subquery to return one row using ROWNUM completely defeats the purpose of the query.
Using DISTINCT does in any way allow to get only one row, do you think there is only on currency?
Without speaking about the "acr.receipt_date BETWEEN '01-JAN-19' AND '31-JAN-19'" which is wrong as I showed it in the first answer.
A correct query has been given, please refer to it.
|
|
|