sqloracle-databasebi-publisher

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...