Q: PLSQL - Select works but Select within Where Clause returns no data This is driving me crazy. I want to do simple comparison of a column and a variable but it just doesn't work. The QUERY 1 in following code returns me my value when i do a simple select, but i use the resulting variable in my 2nd query it just doesn't work..
It looks sooooo simple but I've been working on this for hours. The complete sql proc is
The big confusing thing is that if I replace v_bbg_symbol  with some hard coded 'Value' (like 'FEDL01') it gives a correct answer for Query 2, but when I use the variable v_bbg_symbol it just doesn't work any more 
Declare
           v_bbg_symbol VARCHAR2(50);
           V_OLD_INS_NAME Varchar2(50);
      Begin
       --QUERY 1
       SELECT BBG_SYMBOL into v_bbg_symbol FROM quotes_external WHERE ID = 1;

       --Gives output - 'FEDL01'
       DBMS_OUTPUT.PUT_LINE('I got here:'||v_bbg_symbol||' is my value'); 

      -QUERY 2
      SELECT NAME INTO V_OLD_INS_NAME FROM INSTRUMENT
      JOIN CURVE_INSTRUMENT ON
      INSTRUMENT.INSTRUMENT_ID = CURVE_INSTRUMENT.INSTRUMENT_ID
      JOIN GENERIC_INSTRUMENT ON
      CURVE_INSTRUMENT.GENERIC_INSTRUMENT_ID = GENERIC_INSTRUMENT.GENERIC_INSTRUMENT_ID
      WHERE CURVE_INSTRUMENT.CURVE_SNAPSHOT_ID =
      (SELECT MAX(CURVE_INSTRUMENT.CURVE_SNAPSHOT_ID) FROM CURVE_INSTRUMENT)
      AND GENERIC_INSTRUMENT.INSTRUMENT_NAME =  v_bbg_symbol;

      --ORACLE ERROR 'No Data Found'
      DBMS_OUTPUT.PUT_LINE('I got here:'||V_OLD_INS_NAME||' is the new value'); 
      END;

The first 'SELECT' gives me value which i select INTO a variable 'v_bbg_symbol', but when I use the same variable 'v_bbg_symbol' in my 2nd QUERY it pretends as if there is no value passed and does not return any result. If I give static value of 'v_bbg_symbol' i.e. ('FEDL01' in this case) in my 2nd QUERY, the results come as expected.
Please help..

A: Here is your query, with table aliases to facilitate following it:
SELECT NAME INTO V_OLD_INS_NAME
FROM INSTRUMENT i JOIN
     CURVE_INSTRUMENT ci
     ON i.INSTRUMENT_ID = ci.INSTRUMENT_ID JOIN
     GENERIC_INSTRUMENT gi
     ON ci.GENERIC_INSTRUMENT_ID = gi.GENERIC_INSTRUMENT_ID
WHERE ci.CURVE_SNAPSHOT_ID = (SELECT MAX(ci.CURVE_SNAPSHOT_ID) FROM CURVE_INSTRUMENT ci) and
      gi.INSTRUMENT_NAME = v_bbg_symbol;

What this says is that the maximum ci.curve_snapshot_id is not for the instrument that is associated with v_bbg_symbol.   I think you want a correlated subquery:
SELECT NAME INTO V_OLD_INS_NAME
FROM INSTRUMENT i JOIN
     CURVE_INSTRUMENT ci
     ON i.INSTRUMENT_ID = ci.INSTRUMENT_ID JOIN
     GENERIC_INSTRUMENT gi
     ON ci.GENERIC_INSTRUMENT_ID = gi.GENERIC_INSTRUMENT_ID
WHERE ci.CURVE_SNAPSHOT_ID = (SELECT MAX(ci2.CURVE_SNAPSHOT_ID)
                              FROM CURVE_INSTRUMENT ci2
                              WHERE ci2.instrument_id = i.instrument_id
                             ) and
      gi.INSTRUMENT_NAME = v_bbg_symbol;

