SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use of Analytical function

Sid_ Z.Apr 17 2015 — edited Apr 17 2015

Hi All,

Oracle 11gR2.

I have below data.

I have common data for every txnno except type and I would have to generate output as a single record for a each txnno.

The case is if my txnno contains type as 'FWD' then i have to send the flag as 'Y' with single record else 'N' with single record.

with t as

( select 'CARD' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'RATE' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'DEAL' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'FWD' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'NAL' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'BETA' type, 12 id, 'ABC' branch, 1234 txnno, 50 amt, 'xyz' product from dual union all

select 'CARD' type, 13 id, 'EFG' branch, 4567 txnno, 50 amt, 'pqr' product from dual union all

select 'NAL' type, 13 id, 'EFG' branch, 4567 txnno, 50 amt, 'pqr' product from dual union all

select 'BETA' type, 13 id, 'EFG' branch, 4567 txnno, 50 amt, 'pqr' product from dual union all

select 'DEAL' type, 13 id, 'EFG' branch, 4567 txnno, 50 amt, 'pqr' product from dual

)

SELECT case when type= 'FWD' then 'Y' else 'N' end Flag, id , branch,txnno,amt,product

FROM T;

Output:

Y 12 ABC 1234 50 xyz

N 13 EFG 4567 50 pqr

Thanks

Sid

This post has been answered by RogerT on Apr 17 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on May 15 2015

Added on Apr 17 2015

3 comments

139 views