Home » SQL & PL/SQL » SQL & PL/SQL » retrieveing two values from table 1 and displaying it one record after joining it with table 2 (merg
retrieveing two values from table 1 and displaying it one record after joining it with table 2 (merg [message #413785] Fri, 17 July 2009 11:41 Go to next message
namitanamburi
Messages: 32
Registered: March 2009
Member
Hello,

I have an issue and I would like to know how to handle this.

There are certain payments that are taken at bank and the transactions are like this



Date Tran# AcctType site # amount



10/12/2006 100 Sav 2 $120.00



10/12/2006 200 chk 3 $140.00



1/12/2007 156 sav 1 $128.00





Iam trying to retrieve all transaction for 10/12/2006.


The date is in table A



Tran# and Amount are in table B


Table C has two columns called Name and value which save all misc details like location type, account type and the site# etc.



Table C is like this.



Tran# Name Value



100 Acct_type Chk



200 Acct_type sav



100 site 2



100 site 3



and so on


I wrote a query and

when I retrieve data for 10/12/2006 the result is


Date Tran# AcctType site# amount



10/12/2006 100 Sav $120.00



10/12/2006 2 $120.00



10/12/2006 200 chk 3 $140.00



10/12/2006 3 $140.00


There are only two transactions. My expected result is


Date Tran# acct Type site # amount



10/12/2006 100 Sav 2 $120.00



10/12/2006 200 chk 3 $140.00



Basically the site # has to be beside the acct type instead of coming up as a new transaction.

Can you suggest me ideas to get these results.

Also Just FYI I tried two select queries and unioned them after joining the tables twice, (once in eact elect statements).


Thanks in advance

Nammu

Re: retrieveing two values from table 1 and displaying it one record after joining it with table 2 ( [message #413790 is a reply to message #413785] Fri, 17 July 2009 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should think to do something about your Parkinson disease that makes you frantically click on Submit button and type on Enter key.

Also 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: retrieveing two values from table 1 and displaying it one record after joining it with table 2 ( [message #413804 is a reply to message #413785] Fri, 17 July 2009 12:32 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Your posting is too difficult to read with all the extra blank line, but I am guessing that you are ignoring the time element of a DATE column in Oracle. You will need to TRUNC the column.
Re: retrieveing two values from table 1 and displaying it one record after joining it with table 2 ( [message #413843 is a reply to message #413804] Fri, 17 July 2009 15:43 Go to previous messageGo to next message
namitanamburi
Messages: 32
Registered: March 2009
Member
Hello,
Sorry about that, i cudnt even figure out if my thread posted. The format was indeed terrible, please forgive the inconvenience caused.
I have an issue and I would like to know how to handle this.
There are certain payments that are taken at bank and the transactions are like this

Date Tran# AcctType site # amount
10/12/2006 100 Sav 2 $120.00
10/12/2006 200 chk 3 $140.00
1/12/2007 156 sav 1 $128.00

Iam trying to retrieve all transaction for 10/12/2006.

The date is in table A
Tran# and Amount are in table B
Table C has two columns called Name and value which save all misc details like location type, account type and the site# etc.
Table C is like this.

Tran# Name Value
100 Acct_type Chk
200 Acct_type sav
100 site 2
100 site 3

and so on
I wrote a query and
when I retrieve data for 10/12/2006 the result is

Date Tran# AcctType site# amount
10/12/2006 100 Sav null $120.00
10/12/2006 100 null 2 $120.00
10/12/2006 200 chk null $140.00
10/12/2006 200 null 3 $140.00

There are only two transactions. My expected result is
Date Tran# acct Type site # amount

10/12/2006 100 Sav 2 $120.00
10/12/2006 200 chk 3 $140.00

Basically the site # has to be beside the acct type instead of coming up as a new transaction.
Can you suggest me ideas to get these results.
Also Just FYI I tried two select queries and unioned them after joining the tables twice, (once in eact elect statements).


Thanks in advance

Nammu
Re: retrieveing two values from table 1 and displaying it one record after joining it with table 2 ( [message #413855 is a reply to message #413785] Fri, 17 July 2009 17:31 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about showing us the actual query you used along with proper descriptions of each table.
Re: retrieveing two values from table 1 and displaying it one record after joining it with table 2 ( [message #413882 is a reply to message #413843] Sat, 18 July 2009 00:52 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted, still no test case.

Regards
Michel
Previous Topic: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
Next Topic: File separator creates problem
Goto Forum:
  


Current Time: Fri Dec 09 15:53:51 CST 2016

Total time taken to generate the page: 0.13929 seconds