Home » SQL & PL/SQL » SQL & PL/SQL » joining select statements
joining select statements [message #410091] Thu, 25 June 2009 03:08 Go to next message
jr_db_dev
Messages: 7
Registered: June 2009
Junior Member
Hi,

Im sorry but this is quite long. Thanks for your time in advance. I really hope someone can help me. Sad

Can someone please help me how to join these 4 select statements and come up with a cursor containing the output below (of 3 rows only):

a. Valid duration for each scenario (of MSISDN as anum and MSISDN as bnum)

Select RRN, txn_endtime
from tssp_txn_logs where anumber = '639183372176'
AND SVC_TYPE_ID IN ( 'OTC', 'BDLOAD', 'PCHASE')
AND to_date(to_char(txn_endtime, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
between TO_DATE (TO_CHAR (SYSTIMESTAMP -1, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
AND TO_DATE (TO_CHAR (SYSTIMESTAMP, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
AND FREE_FIELD2 IS NULL
order by txn_endtime

-> Result on image 1 of attached doc


Select RRN, txn_endtime
from tssp_txn_logs where bnumber = '639183372176'
AND SVC_TYPE_ID IN ( 'OTC', 'BDLOAD', 'PCHASE')
AND to_date(to_char(txn_endtime, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
between TO_DATE (TO_CHAR (SYSTIMESTAMP -1, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
AND TO_DATE (TO_CHAR (SYSTIMESTAMP, 'MON-YYYY-DD HH24:MI:SS'),'MON-YYYY-DD HH24:MI:SS')
AND FREE_FIELD2 IS NULL
order by txn_endtime

-> Result on image 2 of attached doc


b. Valid balance per RRN (considering reversals) (of MSISDN as anum and MSISDN as bnum)

Select RRN, SUM(ANUMBER_TXNAMT)
from tssp_txn_logs where (FREE_FIELD3 IS NULL or FREE_FIELD3 = 'UC TRADE')
AND anumber = '639183372176'
AND SVC_TYPE_ID IN ( 'OTC', 'BDLOAD', 'PCHASE')
group by RRN

-> Result on image 3 of attached doc


Select RRN, SUM(BNUMBER_TXNAMT)
from tssp_txn_logs where (FREE_FIELD3 IS NULL or FREE_FIELD3 = 'UC TRADE')
AND bnumber = '639183372176'
AND SVC_TYPE_ID IN ( 'OTC', 'BDLOAD', 'PCHASE')
group by RRN

-> Result on image 4 of attached doc


c. while retrieving only the latest 3 transactions:

where rownum > 4


TARGET OUTPUT: (also in the attached doc)

RRN TXN_ENDTIME ANUMBER_TXNAMT BNUMBER_TXNAMT
7 6/24/2009 3:21:38.000000 AM 2000
6 6/24/2009 3:18:32.000000 AM -5500
5 6/24/2009 3:11:19.000000 AM 0

BTW: My goal is to retrieve the total deducts and credits of the last 3 transactions made by a subscriber


Thanks!
Re: joining select statements [message #410263 is a reply to message #410091] Thu, 25 June 2009 22:48 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
1) user to code formatter accessible from the main page to format your code.

2) use the code tags (see {..} above where you type your post) to tag your code when you post it so your formatting is retained when displayed.

As for how to do what you want. Why does putting the selects are inline selects in the from clause not do what you what?

select ...
from (
     ) a
     ,(
       ) b
     ,(
       ) c
     ,(
       ) d
where a.rrn = ...
/


inside each () put one of your selects. Do you know how to code this?

Kevin
Previous Topic: Updating Records in a table with max number (merged 2) 10g
Next Topic: migration script
Goto Forum:
  


Current Time: Fri Dec 09 23:39:29 CST 2016

Total time taken to generate the page: 0.13082 seconds