Home » SQL & PL/SQL » SQL & PL/SQL » PROBLEM WRITING QUERY
PROBLEM WRITING QUERY [message #427077] Wed, 21 October 2009 00:06 Go to next message
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 #427078 is a reply to message #427077] Wed, 21 October 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
better



You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: PROBLEM WRITING QUERY [message #427083 is a reply to message #427077] Wed, 21 October 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not post in UPPER case.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Regards
Michel
Re: PROBLEM WRITING QUERY [message #427087 is a reply to message #427077] Wed, 21 October 2009 01:06 Go to previous message
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
)


Previous Topic: Report not formating columns properly
Next Topic: DOS command output in pl/sql
Goto Forum:
  


Current Time: Tue Sep 27 17:56:18 CDT 2016

Total time taken to generate the page: 0.12976 seconds