[SOLVED] For Oracle DB. looking to store a value from a select statement into a variable to use in a later query

For Oracle DB. looking to store a value from a select statement into a variable to use in a later query

I need to use two IDs from my select statement so I can make other statements retrieving data from another table. I'm using a data model in Oracle BI Publisher.

select
a.dist_code_combination_id,
a.def_acctg_accrual_ccid,
a.invoice_id,
b.column4,

declare
var1 int;
begin
select a.dist_code_combination_id into var1 from ap_invoice_distributions_all a;
end;

declare
var2 int;
begin
select a.def_acctg_accrual_ccid into var2 from ap_invoice_distributions_all a;
end;

select segment from gl_code_combinations where code_combination_ID = testvar1,
select segment from gl_code_combinations where code_combination_ID = testvar2,

from
AP_invoice_distributions_all a,
table2 b

where
a.invoice_id = b.column4

I was expecting 6 columns total but I get an error about FROM not found where expected.

Solution

I don't understand the declare blocks. They are unnecessary and I think the above query has some issues.

Since you need 6 columns only, please check this query. I am not sure this is what you need.

SELECT 
    a.dist_code_combination_id var1,
    a.def_acctg_accrual_ccid var2,
    a.invoice_id,
    b.column4,
    (SELECT segment FROM gl_code_combinations WHERE code_combination_id = a.dist_code_combination_id) AS segment1,
    (SELECT segment FROM gl_code_combinations WHERE code_combination_id = a.def_acctg_accrual_ccid) AS segment2
FROM 
    AP_invoice_distributions_all a
JOIN 
    table2 b 
ON 
    a.invoice_id = b.column4;

I just thought you want to rename a.dist_code_combination_id to var1...