CODEWITHSUNDEEP
sqloracle-database
user9038544
user9038544

Reputation: 13

Posibility of using variables in Oracle SQL select

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

Upvotes: 1

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions