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!

Need Help to solve this query

Discussion in 'SQL PL/SQL' started by Araav, Oct 14, 2010.

  1. Araav

    Araav Guest

    Hi,

    In the code below, when group by is used greater than 1, it should not return any rows, but in the "client_depositories" table there are multiple entries for a member code and local client id. How do I modify query below, to display no records in the "MEMBER_CODE" and "MEMBER_CLIENT_REFERENCE"

    the code is given below, whereas the values of the table "client_depositories" is given below the query for member code 520.

    Please provide me with the changes required.

    Thanks !!!
    --------------------------------------------------------------------------------------------------------
    select c.member_code MEMBER_CODE,
    c.member_client_reference MEMBER_CLIENT_REFERENCE
    /*
    concat(c.first_name, c.surname) FULLNAME,
    ct.description DESCTYPE,
    cs1.description DESCSTATUS,
    c.gender GENDER,
    c.display_pan_id DISPLAY_PAN_ID,
    c.declaration DECLARATION,
    at1.description DESCADDRTYPE,
    caddr.address1 ADDRESS1,
    caddr.address2 ADDRESS2,
    caddr.address3 ADDRESS3,
    caddr.post_code POST_CODE,
    caddr.town TOWN,
    caddr.county COUNTRY,
    caddr.country_code COUNTRY_CODE,
    cba.bank_code BANK_CODE,
    cba.branch_code BRANCH_CODE,
    bnk.bank_name BANK_NAME,
    cat.description DESCRIP_CAT,
    cur.description DESCRIP_CUR,
    cid.id_type ID_TYPE,
    cid.id_number ID_NUMBER,
    cid.place_of_issue PLACE_OF_ISSUE,
    to_char(cid.date_of_issue, 'dd-mm-yyyy') DATE_OF_ISSUE,
    to_char(cid.date_of_expiry, 'dd-mm-yyyy') DATE_OF_EXPIRY,
    cid.registering_authority REGISTERING_AUTHORITY,
    cdepo.beneficiary_account_id BENEFICIARY_ACCOUNT_ID,
    cdepo.depository_name DEPOSITORY_NAME,
    cdepo.depository_participant_id DEPOSITORY_PARTICIPANT_ID,
    cdepo.depository_participant_name DEPOSITORY_PARTICIPANT_NAME,
    cintro.introducer_name INTRODUCER_NAME,
    cintro.dealing_with_other_members DEALING_WITH_OTHER_MEMBERS,
    cintro.introducer_client_reference INTRODUCER_CLIENT_REFERENCE,
    cintro.relationship RELATIONSHOP,
    cintro.relationship_code RELATIONSHIP_CODE
    */
    from clients c
    inner join client_types ct on c.client_type = ct.client_type
    inner join client_status cs1 on c.status = cs1.status
    inner join client_addresses caddr on c.member_code = caddr.member_code
    AND c.MEMBER_CLIENT_REFERENCE = caddr.MEMBER_CLIENT_REFERENCE
    inner join address_types at1 on at1.address_type = caddr.address_type
    left outer join client_bank_accounts cba on cba.member_code = c.member_code
    left outer join banks bnk on bnk.bank_code = cba.bank_code
    left outer join cash_account_types cat on cat.CASH_ACC_TYPE = cba.CASH_ACC_TYPE
    left outer join currency_types cur on cur.currency_type = cba.currency_type
    inner join client_identifications cid on c.member_code = cid.member_code
    AND c.MEMBER_CLIENT_REFERENCE = cid.MEMBER_CLIENT_REFERENCE
    inner join CLIENT_DEPOSITORIES cdepo on c.member_code = cdepo.member_code
    AND c.member_client_reference = cdepo.member_client_reference

    inner join client_introducers cintro on c.member_code = cintro.member_code
    AND c.member_client_reference = cintro.member_client_reference
    where c.member_code = 520

    group by c.member_code,c.member_client_reference

    ---order by c.member_code,c.member_client_reference

    having count(1) > 1
    --------------------------------------------------------------------------------------------------------


    1 520 UC001 NSDL IN110022 32668858 HDFC BANK
    2 520 UC001 CDSL 12060022 12159936 HDFC BANK
    3 520 UC002 NSDL IN110022 32668858 HDFC BANK
    4 520 UC002 CDSL 12060022 12159936 HDFC BANK
    5 520 UC003 NSDL IN110022 32668858 HDFC BANK
    6 520 UC003 CDSL 12060022 12159936 HDFC BANK
    7 520 UC004 NSDL IN110022 32668858 HDFC BANK
    8 520 UC004 CDSL 12060022 12159936 HDFC BANK
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How you get this code to execute is a mystery to me since you have no aggregates in your select list:

    Code (SQL):
    SQL> SELECT * FROM dba_users GROUP BY username HAVING COUNT(1) > 1;
    SELECT * FROM dba_users GROUP BY username HAVING COUNT(1) > 1
           *
    ERROR at line 1:
    ORA-00979: NOT a GROUP BY expression
    And it won't work with count(1) as it has nothing to do with the result set:

    Code (SQL):
    SQL> SELECT a.username, COUNT(a.username) FROM v$session a GROUP BY a.username HAVING COUNT(1) > 1;

    USERNAME                       COUNT(A.USERNAME)
    ------------------------------ -----------------
                                                   0
    SYS                                            2
     
    Using a proper aggregate in the having clause (one appropriate to the query) 'fixes' things:

    Code (SQL):
    SQL> SELECT a.username, COUNT(a.username) FROM v$session a GROUP BY a.username HAVING COUNT(a.username) > 1;

    USERNAME                       COUNT(A.USERNAME)
    ------------------------------ -----------------
    SYS                                            2