Home » SQL & PL/SQL » SQL & PL/SQL » use of union with case (9i,)
use of union with case [message #311862] Mon, 07 April 2008 03:06 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i have two table with same feilds. table 1 returns 1 record and table two return multiple records.
let have look on query
select 
a.invoice_id, a.accts_pay_code_combination_id account_code,  a.invoice_amount inv_amt
from ap_invoices_all a
where 
a.invoice_id=10384
union
select 
b.invoice_id, b.dist_code_combination_id account_code, b.amount inv_amt
from ap_invoice_distributions_all b
where 
b.invoice_id=10384


and the result is like
INVOICE_ID ACCOUNT_CODE    INV_AMT
---------- ------------ ----------
     10384         1020      27590
     10384         1415       1811
     10384         1502       1688
     10384         1560       1717
     10384         1607       3851
     10384         1622       1382
     10384         1622       1728
     10384         1642       3312
     10384         1662        999
     10384         1662       2322
     10384         1684       1599

INVOICE_ID ACCOUNT_CODE    INV_AMT
---------- ------------ ----------
     10384         1705       2525
     10384         1754       2727
     10384         2089       1929



record no 1 ( 27590 ) is from table 1 and rest of is from table 2. Now i would like to change the inv_amt status . if inv_amt of table 1 is NEGATIVE(-) value then rest of all inv_amt of table 2 converts into POSITIVE(+) value or if inv_amt of table 1 is POSITIVE(+) value then rest of all inv_amt of table 2 converts into NEGATIVE(-) value.


how can i do that .. is this posible with case tool or any other solution ..

any idea ..


regards

Anwer
Re: use of union with case [message #311877 is a reply to message #311862] Mon, 07 April 2008 04:21 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
please post your table structures??
Re: use of union with case [message #311894 is a reply to message #311877] Mon, 07 April 2008 05:19 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

thanks for early reply

table 1

invoice_id,
accts_pay_code_combination_id ,
invoice_amount

Table 2
invoice_id,
dist_code_combination_id ,
amount


regards


Anwer
Re: use of union with case [message #311900 is a reply to message #311862] Mon, 07 April 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What happens when there are several rows from table 1 some with positive values and other with negative ones?

Regards
Michel
Re: use of union with case [message #311907 is a reply to message #311900] Mon, 07 April 2008 06:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try this out.

SELECT 
a.invoice_id, a.accts_pay_code_combination_id account_code,  a.invoice_amount inv_amt
FROM 
ap_invoices_all a
WHERE a.invoice_id=10384
UNION
SELECT 
 b.invoice_id, b.dist_code_combination_id account_code, 
CASE WHEN a.invoice_amount >0 THEN 
       -b.amount  
ELSE b.amount END inv_amt
FROM 
ap_invoice_distributions_all b,ap_invoices_all a
WHERE b.invoice_id=a.invoice_id AND b.invoice_id=10384
Re: use of union with case [message #311924 is a reply to message #311907] Mon, 07 April 2008 06:55 Go to previous message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

thank you


It works.


regards

Anwer
Previous Topic: dbms_sql.native
Next Topic: Performing with FloatNumbers
Goto Forum:
  


Current Time: Mon Feb 17 18:26:49 CST 2025