Posibility of using variables in Oracle SQL select

Question

I dont know what to search for so I dont know if question has already been answered :-)

I have an Oracle function returning an ID, I want to use this function multiple times in a SQL select statement.

Excample:

select 
    custname,       
    custid,
    salesorderid,
    getIdFromSalesOrder(salesorderid),
    qty,
    getPriceFromID(getIdFromSalesOrder(salesorderid)),
    getXFromID(getIdFromSalesOrder(salesorderid)),
    getYFromID(getIdFromSalesOrder(salesorderid)),
    getZFromID(getIdFromSalesOrder(salesorderid))
from
    tableX,
    tableY
where
    .......

is there any way to use a local variable so I avoid calling getIdFromSalesOrder again and again? Mayby something like this?

id_ number(10,0);

select 
    custname,       
    custid,
    salesorderid,
    getIdFromSalesOrder(salesorderid) into id_,
    qty,
    getPriceFromID(id_),
    getXFromID(id_),
    getYFromID(id_),
    getZFromID(id_)
from
    tableX,
    tableY
where
    .......

SQL select statement will return 3-4.000 rows so in some way the variable will need to be pr. row?

Best regards Anders

Gordon Linoff · Accepted Answer · 2017-12-01 10:46:33Z

1

You can use a subquery or CTE:

select custname, custid, salesorderid, id_salesorderid, qty,
       getPriceFromID(id_salesorderid),
       getXFromID(id_salesorderid),
       getYFromID(id_salesorderid),
       getZFromID(id_salesorderid)
from (select x.*, getIdFromSalesOrder(salesorderid) as id_salesorderid
      from tableX x
     ) x join
     tableY y
     on . . .;

Notice that I added table aliases to the query. You should qualify all table names. Also notice that I added in proper, explicit, join syntax.

answered Dec 1, 2017 at 10:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Posibility of using variables in Oracle SQL select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related