Home » SQL & PL/SQL » SQL & PL/SQL » Subquery returns more than one row - where clause -decode (oracle 12c)
Subquery returns more than one row - where clause -decode [message #679868] Wed, 08 April 2020 09:18 Go to next message
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 #679869 is a reply to message #679868] Wed, 08 April 2020 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

The error means that "SELECT CURRENCY_CODE FROM fnd_currencies WHERE CURRENCY_CODE <>'INR'" returns more than one where at most one is expected.

Your query is definitively wrong.
Proof:
SQL> select 'YES, IT IS BETWEEN' "IS IT BETWEEN?" from dual
  2  where '31-DEC-2222' BETWEEN '01-JAN-19' AND '31-JAN-19';
IS IT BETWEEN?
------------------
YES, IT IS BETWEEN
Re: Subquery returns more than one row - where clause -decode [message #679870 is a reply to message #679869] Wed, 08 April 2020 10:33 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems to me the decode should be inside the sub-query rather than surrounding it.

Oracle functions generally will only take a single value for a given parameter.
Re: Subquery returns more than one row - where clause -decode [message #679874 is a reply to message #679870] Wed, 08 April 2020 23:59 Go to previous messageGo to next message
New50
Messages: 4
Registered: April 2020
Junior Member
yes I understand it is wrong.

So please let me know any example how i give it inside the sub-quert ?

Thanks,
Re: Subquery returns more than one row - where clause -decode [message #679875 is a reply to message #679874] Thu, 09 April 2020 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the purpose of the query? what is its specification? what result do you want to get?

Re: Subquery returns more than one row - where clause -decode [message #679876 is a reply to message #679875] Thu, 09 April 2020 00:54 Go to previous messageGo to next message
New50
Messages: 4
Registered: April 2020
Junior Member
I have a parameter for currency

if the user give other than inr, it should display all records other than inr
if inr - only inr
in empty then all currency records

I use this inside the cursor, this is a big query - since i get error in this where clause - i attached this alone.

Many thanks for your concern!
Re: Subquery returns more than one row - where clause -decode [message #679877 is a reply to message #679876] Thu, 09 April 2020 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

INR or NOINR?
What is the difference between both?

"it should display all records " you mean all records of "AR_CASH_RECEIPTS_V".

Why not just use OR between conditions expressing the different cases?

In short (despite the lack of and consistency in specification) I think the query is something like:
SELECT *
FROM AR_CASH_RECEIPTS_V acr
WHERE 1=1
  AND acr.receipt_date 
        BETWEEN TO_DATE('01/01/2019','DD/MM/YYYY')
            AND TO_DATE('31/01/2019','DD/MM/YYYY')
  AND (  ( :param = 'INR' AND ACR.CURRENCY_CODE = 'INR' )
      OR ( :param != 'INR' 
         AND ACR.CURRENCY_CODE IN 
             (SELECT CURRENCY_CODE FROM fnd_currencies WHERE CURRENCY_CODE <> 'INR')
         )
      OR :param IS NULL 
      )
/
Re: Subquery returns more than one row - where clause -decode [message #679882 is a reply to message #679868] Thu, 09 April 2020 07:43 Go to previous messageGo to next message
EdStevens
Messages: 1209
Registered: September 2013
Senior Member
Quote:
AND acr.receipt_date BETWEEN '01-JAN-19' AND '31-JAN-19'
here you are comparing strings, not dates. What is the data type of acr.receipt_date? If this system is properly designed, then it should be DATE. And your usage will force oracle to apply an implicit to_date() to your two string representations of dates. And the success of that will depend on the controlling setting of NLS_DATE_FORMAT.

And you compound your error by using 2-digit years. Does the term 'Y2K' mean nothing to you?

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')
Re: Subquery returns more than one row - where clause -decode [message #679885 is a reply to message #679882] Thu, 09 April 2020 09:38 Go to previous messageGo to next message
New50
Messages: 4
Registered: April 2020
Junior Member
Many Many thanks "Michel Cadot"
Query which you gave worked!

Thanks!
Re: Subquery returns more than one row - where clause -decode [message #679886 is a reply to message #679882] Thu, 09 April 2020 10:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
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 #679891 is a reply to message #679886] Thu, 09 April 2020 13:48 Go to previous messageGo to next message
Ashish Sahay
Messages: 2
Registered: April 2020
Junior Member
You can try with rownum = 1 or distinct
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'
                                                    AND ROWNUM = 1),
                                   acr.currency_code);

For Distinct
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 DISTINCT currency_code
                                               FROM fnd_currencies
                                              WHERE currency_code <> 'INR'),
                                   acr.currency_code);
Re: Subquery returns more than one row - where clause -decode [message #679892 is a reply to message #679891] Thu, 09 April 2020 14:40 Go to previous message
Michel Cadot
Messages: 67163
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.

Previous Topic: Adding a from () to a query made it so slow
Next Topic: Filtering result from 2 PL/SQL UNION queries
Goto Forum:
  


Current Time: Sun Jun 07 02:12:34 CDT 2020