CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Wesley Heron
Wesley Heron

Reputation: 423

How to get variable values and use it on query Oracle?

I'm developing a query that needs to use a values that returned in this declaration:

DECLARE
    V_CD_AM NUMBER;

    cursor cd_am_cursor IS
       SELECT CD_AMOST
       FROM   L_CHEG
       WHERE  SQ_CHEG = 4153839;
BEGIN
    OPEN cd_am_cursor;
    LOOP
        FETCH cd_am_cursor into V_CD_AM;
        EXIT WHEN cd_am_cursor%NOTFOUND;                 
    END LOOP;
END;

This query above return two rows. I want to get this two rows and run it on another query. The second query is more complex (there is some joins). Indeed all I need is result this second query.

SELECT AMAN.CD_AMOST_ANA CodAmAnalise,
             AN.DESCR_ANA         DescrAna,
             r.result              Result,
             ch.data_sist          DataCheg,
             AMAN.ORDEM               Ordem,
             aman.limite_atraso       LimiteAtraso,
             aman.idc_const       IdcConst,
             r.cod_obser         CodObser,
             r.tag_equip        Equip,
             SYSDATE                  DataAtual,
             r.seq_result          SeqResult,
             aman.cod_ana         CodAna,
             aman.idc_calc       IdcCalc,
             aman.cd_amost         CdAmost,
             aman.tempo_bloq      TempoBloq,
             r.data_sistema           DataSistema,
             r.idc_status             IdcStatus
        FROM AMOST_ANA AMAN
        JOIN ANALISE AN ON AMAN.CD_ANA = AN.CD_ANA
        LEFT JOIN CHEG_AMAN CHAMAN ON aman.cd_amost_ana =
                                                   chaman.cd_amost_ana
                                               AND aman.cd_amost =
                                                   chaman.cd_amost
                                               AND CHAMAN.seq_cheg =
                                                   4153839
        JOIN CHEG ch ON chaman.sq_cheg = ch.sq_cheg
                                 AND aman.cd_amost = ch.cd_amost
                                 AND ch.data_sistema =
                                     (SELECT MAX(data_sistema)
                                        FROM cheg
                                       WHERE sq_cheg = 4153839)
        JOIN RESULTADO R ON ch.SQ_CHEG = R.SQ_CHEG
                                  AND CHAMAN.CD_AMOST_ANA =
                                      R.COD_AMOST_ANA
                                  AND r.idc_status NOT IN ('T')
                                  AND (r.data_sistema =
                                      (SELECT MAX(data_sistema)
                                          FROM resultado
                                         WHERE sq_cheg = 4153839
                                           AND cd_amost_ana =
                                               CHAMAN.COD_AMOST_ANA
                                           AND idc_status NOT IN ('T')) OR
                                      r.data_sistema IS NULL)
       WHERE AN.IDC_ATIVO = 'True'
         AND aman.cd_amost = //Here in this line I would like use V_CD_AM
         AND AMAN.IDC_ATIVO = 'True'
       ORDER BY AMAN.ORDEM;

There is another way to execute this second query? The query works, if I put in:

AND aman.cd_amost = someRealValue

But this someRealValue must be dinamic, therefore I create the declaration V_CD_AM, How to get this values and run correctly this query?

Upvotes: 0

Views: 64

Answers (1)

kfinity
kfinity

Reputation: 9091

What Brad is saying is that you could do:

AND aman.cd_amost in (SELECT CD_AMOST FROM L_CHEG WHERE SQ_CHEG = 4153839)

or you could do it this way, and remove the line in the WHERE clause.

JOIN L_CHEG ON l_cheg.SQ_CHEG = 4153839
  AND aman.cd_amost = l_cheg.cd_amost

Upvotes: 2

Related Questions