PROBLEM WRITING QUERY [message #427077] |
Wed, 21 October 2009 00:06  |
gencom99
Messages: 1 Registered: October 2009
|
Junior Member |
|
|
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?
|
|
|
|
|
Re: PROBLEM WRITING QUERY [message #427087 is a reply to message #427077] |
Wed, 21 October 2009 01:06  |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Use another filter in the sql by account number( account number =123456789)in the first sql.Verify the result.
Please post the test case (Create table and Insert statement
)
|
|
|