CODEWITHSUNDEEP
sqloracle-databaseplsql
jlrolin
jlrolin

Reputation: 1614

SELECT INTO variable, two statements, add variables

I have a variable called c_kilometers. I have a cursor that grabs a bunch of records that have those kilometers. I need to run two separate SELECT statements in the cursor that simply grab a kilometer from one table based on values in the cursor, and run another SELECT doing the same thing on another table.

SELECT t.kilometers INTO c_kilometers
FROM table_name WHERE WHERE l.code = cursor_t.code_att

SELECT g.kilometers INTO c_kilometers
FROM table_name WHERE l.code = cursor_t.code_aff

My question is can I add the c_kilometers together without creating a temporary variable to hold on of the values? I haven't used PL/SQL in awhile, and I don't remember having to do this ever, so this is more of a learning question than anything.

Upvotes: 2

Views: 26792

Answers (2)

John Flack
John Flack

Reputation: 106

Join the SELECTs like this: SELECT a.kilometers + b.kilometers FROM (SELECT code, kilometers FROM table_name WHERE ...) a JOIN (SELECT code, kilometers FROM table_name WHERE ...) b ON a.code = b.code

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

Provided that both your queries always return exactly one row, you can do either of the following:

/* Variant 1 */


SELECT  t.kilometers + g.kilometers
INTO    c_kilometers
FROM    table_name t, table_name2 g
WHERE   etc1
        AND etc2

/* Variant 2 */

SELECT  t.kilometers
INTO    c_kilometers
FROM    table_name
WHERE   etc;

SELECT  c_kilometers + g.kilometers
INTO    c_kilometers
FROM    table_name2
WHERE   etc;

If they're in the same table as you posted, you can use:

SELECT COALESCE(SUM(kilometers), 0)
INTO   c_kilometers
FROM   table_name
WHERE  l.code IN (cursor_t.code_aff, cursor_t.code_att)

It seems that it will be more efficient to put table_name into your SELECT query that produces the cursor.

If you post this query, I'll probably can help to do this.

Upvotes: 6

Related Questions