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!

Problem writing query

Discussion in 'SQL PL/SQL' started by gencom99, Oct 21, 2009.

  1. gencom99

    gencom99 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a query

    SELECT D.DIRECTOR_LAST_NAME, D.DIRECTOR_FIRST_NAME,
    A.ACCOUNT_NUMBER, A.ACCOUNT_NAME,
    B.BANK_ACCOUNT_NUMBER,
    C.SECTION_DESCRIPTION, C.START_DATE, C.SECTION_TYPE,
    S.STATUS_CODE

    FROM ACCOUNT_TABLE A,
    ACCOUNT_REFERENCE_TABLE AA,
    ACCOUNT_TYPE_TABLE AAA,
    BANK_TABLE B,
    BANK_DETAILS_TABLE BB ,
    DIRECTOR_TABLE D,
    CATEGORY_TABLE C,
    STATUS_TABLE S,
    STATUS_ACCOUNT_TABLE SS

    WHERE A.ACCOUNT_ID = AA.ACCOUNT_ID
    and A.ACCOUNT_ID = B.ACCOUNT_ID
    and A.DIRECTOR_ID = D.DIRECTOR_ID
    and AA.ACCOUNT_REF_ID = AAA.ACCOUNT_REF_ID
    and AA.ACCOUNT_ID = B.ACCOUNT_ID
    and B.BANK_CODE = BB.BANK_CODE
    and C.ACCOUNT_ID= A.ACCOUNT_ID
    and C.ACCOUNT_ID= B.ACCOUNT_ID
    and C.ACCOUNT_ID= AA.ACCOUNT_ID
    and (C.START_DATE is null or C.SECTION_TYPE is null or RR_STATUS_ID is null)
    and RR.ACCOUNT_ID = A.ACCOUNT_ID
    and RR.ACCOUNT_ID = AA.ACCOUNT_ID
    and RR.ACCOUNT_ID = B.ACCOUNT_ID
    and RR.ACCOUNT_ID= C.ACCOUNT_ID
    and RR.CHNGE_ID = C.CHNGE_ID
    and RR.SECTION_ID = C.SECTION_ID
    and RR_STATUS_ID = R.STATUS_ID (+)
    and A.ACCOUNT_OPEN_CLOSE = 'O'
    and A.ACCOUNT_CLOSE_DATE is null
    and AAA.ACCOUNT_CODE in (‘50’, ‘100’)
    and BB.ACCOUNT_DESC = 'USA'
    order by D.DIRECTOR_LAST_NAME, D.DIRECTOR_FIRST_NAME, A.ACCOUNT_NUMBER

    THE RESULT IS

    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT
    JONES MARY 98765 CITIBANK BANK SAVING 123456789 BLUE 1/2/03 DEF CURRENT

    I HAVE ANOTHER QUERY

    SELECT D.DIRECTOR_LAST_NAME, D.DIRECTOR_FIRST_NAME,
    A.ACCOUNT_NUMBER, A.ACCOUNT_NAME,
    B.BANK_ACCOUNT_NUMBER,
    C.SECTION_DESCRIPTION, C.START_DATE, C.SECTION_TYPE,
    S.STATUS_CODE

    FROM ACCOUNT_TABLE A,
    ACCOUNT_REFERENCE_TABLE AA,
    ACCOUNT_TYPE_TABLE AAA,
    BANK_TABLE B, process_acct_xref p
    BANK_DETAILS_TABLE BB ,
    DIRECTOR_TABLE D,
    CATEGORY_TABLE C,
    STATUS_TABLE S,
    STATUS_ACCOUNT_TABLE SS,
    ACCOUNT_DETAIL_TABLE AAAA


    WHERE A.ACCOUNT_ID = AA.ACCOUNT_ID
    and A.ACCOUNT_ID = B.ACCOUNT_ID
    and A.DIRECTOR_ID = D.DIRECTOR_ID
    and AA.ACCOUNT_REF_ID = AAA.ACCOUNT_REF_ID
    and AA.ACCOUNT_ID = B.ACCOUNT_ID
    and B.BANK_CODE = BB.BANK_CODE
    and C.ACCOUNT_ID= A.ACCOUNT_ID
    and C.ACCOUNT_ID= B.ACCOUNT_ID
    and C.ACCOUNT_ID= AA.ACCOUNT_ID
    and (C.START_DATE is null or C.SECTION_TYPE is null or RR_STATUS_ID is null)
    and RR.ACCOUNT_ID = A.ACCOUNT_ID
    and RR.ACCOUNT_ID = AA.ACCOUNT_ID
    and RR.ACCOUNT_ID = B.ACCOUNT_ID
    and RR.ACCOUNT_ID= C.ACCOUNT_ID
    and RR.CHNGE_ID = C.CHNGE_ID
    and RR.SECTION_ID = C.SECTION_ID
    and RR_STATUS_ID = R.STATUS_ID (+)
    AND A.ACCOUNT_ID = AAAA.ACCOUNT_ID
    AND AAAA.SECTION_ID = C.SECTION_ID

    and A.ACCOUNT_OPEN_CLOSE = 'O'
    and A.ACCOUNT_CLOSE_DATE is null
    and AAA.ACCOUNT_CODE in (‘50’, ‘100’)
    and BB.ACCOUNT_DESC = 'USA'
    order by D.DIRECTOR_LAST_NAME, D.DIRECTOR_FIRST_NAME, A.ACCOUNT_NUMBER

    THE RESULT IS

    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT
    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT
    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT
    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT
    SMITH JOHN 12345 CHASE BANK CHECKING 98765432 RED 9/30/07 ABC CURRENT


    I NEED A QUERY WHERE THE RESULT WOULD BE

    JONES MARY 98765 CITIBANK BANK SAVING 123456789 BLUE 1/2/03 DEF CURRENT


    HOW DO I WRITE THE QUERY?
     
  2. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    Just add "MINUS" between the two queries! I will show you an example,

    Code (Text):


    SQL> WITH DATA
      2       AS (SELECT 1 ID
      3           FROM   DUAL
      4           UNION ALL
      5           SELECT 2
      6           FROM   DUAL),
      7       DATA2
      8       AS (SELECT 1 ID
      9           FROM   DUAL
     10           UNION ALL
     11           SELECT 1
     12           FROM   DUAL
     13           UNION ALL
     14           SELECT 1
     15           FROM   DUAL
     16           UNION ALL
     17           SELECT 1
     18           FROM   DUAL
     19           UNION ALL
     20           SELECT 1
     21           FROM   DUAL)
     22  SELECT A.ID
     23  FROM   DATA A
     24  MINUS
     25  SELECT B.ID
     26  FROM   DATA2 B
     27  /

            ID
    ----------
             2

     
     
  3. gencom99

    gencom99 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank you

    I have used your suggestion. It works, but the query runs very slowly. I am connecting to Oracle with an ODBC connection through Excel. Sometimes when I run the query, I receive the message Excel Not Responding.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to discuss this issue with your DBA so he or she can determine the source of the performance problem and find a suitable solution.