asked on

Create a Table output based on a case condition in PL/SQL (Oracle or DB2 database)

Dear Techies,
I have to create a table output based on the following conditions.
Three COLUMNS : TX_Vereinbart, DISCOUNTS, IA_Vereinbart
They all are coming from table called EP101T

The below are the conditions to populate the output
The fields defining these three fields are:
1) pricing model (TX_Vereinbart) -> If EP101T.ENTGELT_TYP_ID = 'TX'
2) special conditions (discounts) -> EP101T.TX_ONLINE_DISCOUNT ('Y' / 'N')
3) payout of inducements("IA_vereinbart") -> If EP101T.ENTGELT_TYP_ID = 'IA'

In NSLRPT01 (Output Table) the displayed values for these fields are the following:
1) pricing model (TX_Vereinbart):
- if TX available -> display Bookingtext constant: "NSLRPT01_TX_AVAILABLE_YES"
- if TX not available -> display Bookingtext constant: "NSLRPT01_TX_AVAILABLE_NO"
2) special conditions (discounts):
- if TX_ONLINE_DISCOUNT = 'Y' -> display Bookingtext constant: "NSLRPT01_YES"
- if TX_ONLINE_DISCOUNT = 'N' -> display Bookingtext constant: "NSLRPT01_NO"
3) payout of inducements("IA_vereinbart"):
- if IA available -> display code constant: "Ja"
- if IA not available -> display code constant: "Nein"

Can somebody please let me know the simplest query for this output.? Do I need to write a Stored proc for the same.?

Kent Olsen

Hi Flextron,

You've tagged both DB2 and Oracle in the question and the process is actually a bit different. What DBMS are you using?

Kent

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_2484401

Kent has done all the "heavy lifting", but if you want to actually create a table with that output, you'll need to wrap the SELECT statement with CREATE TABLE AS.

e.g.

create table MySchema.MyNewTable as (
... Kent's SELECT statment ...
) with data

Open in new window

HTH,
DaveSlash

Flex Tron

ASKER

Thanks for the Answer. This query is what is needed for the case statements..and yes they are pretty much same for both DB2 and ORacle.