Using PL/SQL table within SQL query

Question

I have a simple select query like below which has only 2 columns:

SELECT a, b FROM table;

I have some mapping data with me like below:

if a=1 and b=1 then c=100
if a=1 and b=2 then c=200
if a=2 and b=1 then c=300
and so on.

Now, I need to create a query so that I can get output like:

1,1,100
1,2,200
2,1,300
and so on

Here I don't want to create a table and store the mapping data. I can create any data structures in PL/SQL to store it.

How can I achieve this?

PS: I tried to create this mapping data using a PL/SQL table and using INNER JOIN. But I realized PL/SQL tables can not be used in SQL queries.

I am using Oracle 11g.

PL/SQL tables can not be used in SQL queries yes they can. You only need to have the global types creation privilege — Sebas
– Sebas, Commented Jul 25, 2013 at 20:37

juergen d · Accepted Answer · 2013-07-25 18:39:31Z

2

You can generate the column c output on-the-fly in your query

SELECT a, b,
       case when a=1 and b=1 then 100 
                 a=1 and b=2 then 200 
                 a=2 and b=1 then 300 
       end as c
FROM table;

answered Jul 25, 2013 at 18:39

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-07-25 18:40:16Z

1

You can use CASE statement to test the value. The purpose of extra WHERE clause on the following query is to speed up the query a little bit since it will only test for values present on the filter condition.

SELECT  a, b,
        CASE WHEN a = 1 AND b = 1 THEN 100
            WHEN a = 1 AND b = 2 THEN 200
            WHEN a = 2 AND b = 1 THEN 300
        END c
FROM    tableName
WHERE   a IN (1,2) AND
        b IN (1,2)

however, if you want to run in all records, you can just remove the WHERE clause.

answered Jul 25, 2013 at 18:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

Using PL/SQL table within SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related