1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

subquery problem

Discussion in 'SQL PL/SQL' started by nicky_river, Oct 19, 2010.

  1. nicky_river

    nicky_river Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am getting an error when i execute this query "single query subquery returns more than one row" how can i modify this query to solve this error ?

    SELECT MEMBER_CODE TRADING_MEMBER_ID,
    (SELECT MEMBER_CLIENT_REFERENCE
    FROM LEDGER_ACCOUNTS
    WHERE MEMBER_CLIENT_REFERENCE NOT IN (SELECT MEMBER_CLIENT_REFERENCE
    FROM CLIENTS
    WHERE LEDGER_ACCOUNTS.MEMBER_CODE=CLIENTS.MEMBER_CODE))UNREGISTERED_CLIENT,
    TO_CHAR(MIN(DATE_TRADE), 'DD-MM-YYYY') FIRST_DATE_OF_TRADE,
    TO_CHAR(SYSDATE, 'DD-MM-YYYY') CURRENT_DATE,
    ROUND(SYSDATE - (MIN(DATE_TRADE)))NUMBER_OF_DAYS,
    ((ROUND(SYSDATE - (MIN(DATE_TRADE))) * 100)) PENALTY_AMOUNT

    FROM UN_CLEARED_TRADE_BROKERS
    GROUP BY MEMBER_CODE, MEMBER_CLIENT_REFERENCE
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    Code (SQL):
     
    SELECT c.MEMBER_CODE TRADING_MEMBER_ID,l.MEMBER_CLIENT_REFERENCE UNREGISTERED_CLIENT,
    TO_CHAR(MIN(DATE_TRADE), 'DD-MM-YYYY') FIRST_DATE_OF_TRADE,
    TO_CHAR(SYSDATE, 'DD-MM-YYYY') CURRENT_DATE,
    ROUND(SYSDATE - (MIN(DATE_TRADE)))NUMBER_OF_DAYS,
    ((ROUND(SYSDATE - (MIN(DATE_TRADE))) * 100)) PENALTY_AMOUNT
    FROM UN_CLEARED_TRADE_BROKERS u, client c, ledger_accounts l
    WHERE l.MEMBER_CLIENT_REFERENCE NOT IN (SELECT MEMBER_CLIENT_REFERENCE
    FROM LEDGER_ACCOUNTS
    WHERE MEMBER_CODE=C.MEMBER_CODE)
    GROUP BY c.MEMBER_CODE, l.MEMBER_CLIENT_REFERENCE ;
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Error tells that the query returns more than one row for the sql. This is becuase you missed out to join some condition.

    Code (SQL):
    SELECT MEMBER_CLIENT_REFERENCE
    FROM LEDGER_ACCOUNTS
    WHERE MEMBER_CLIENT_REFERENCE NOT IN (SELECT MEMBER_CLIENT_REFERENCE
    FROM CLIENTS
    WHERE LEDGER_ACCOUNTS.MEMBER_CODE=CLIENTS.MEMBER_CODE)

    1. It seems the above subquery doesnot have reference to UN_CLEARED_TRADE_BROKERS.
    Or

    2. The subquery returns more than one records for every out main (outer) query.

    Btw, there is difference between query OP tried and query give by zargon

    OP's query will return all the records from UN_CLEARED_TRADE_BROKERS (if there is no error). while zargon's query will return only those records which satistfies where condition.