Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: problem in select query

    Hi All,

    I have a problem with this select statement... Can anyone pls help me solve this.....


    ----------------------------Select query----------------------------

    declare SQL_ADV VARCHAR2(2000);
    declare ColumnType VARCHAR2(2000);

    select data_type from all_tab_columns where upper(table_name) = upper('emp_ins_types'))
    ColumnType :=data_type;

    SELECT
    case
    when eDocsNet_BLStatusChange_Appr.ColumnType in
    ( 'NUMBER' , 'NUMBER' , 'NUMBER' , 'NUMBER' , 'NUMBER' )
    then 'update emp_ins_types set ' || eDocsNet_BLStatusChange_Appr.ComponentID || ' = ' || eDocsNet_BLStatusChange_Appr.ProposedDataValue || ' where emp_code = ''' || eDocsNet_BLStatusChange_Appr.EmployeeID || ''''
    when eDocsNet_BLStatusChange_Appr.ColumnType in
    ( 'VARCHAR2' , 'DATE' , 'char' , 'nvarchar' , 'nchar' , 'LONG' , 'LONG' )
    then 'update emp_ins_types set ' || eDocsNet_BLStatusChange_Appr.ComponentID || ' = ''' || eDocsNet_BLStatusChange_Appr.ProposedDataValue || ''' where emp_code = ''' || eDocsNet_BLStatusChange_Appr.EmployeeID || ''''
    else null
    end INTO eDocsNet_BLStatusChange_Appr.SQL_ADV FROM DUAL;

    IF SQL_ADV is null THEN
    BEGIN


    RETURNPARAMETER := -1;

    return;
    END;
    END IF;
    EXECUTE IMMEDIATE 'SQL_ADV';

    ihave some update query here....
    --------------------------------------------------------------------------

    Every time it return -1..... This query is a part in the procedure.....

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can't select into this -> "eDocsNet_BLStatusChange_Appr.SQL_ADV" and check a local variable's value -> "SQL_ADV is null".

    Perhaps you could SELECT INTO SQL_ADV and then ask IF SQL_ADV IS NULL ...

    Though, perhaps I've misread it. Please, next time format your code and enclose it into the [code] tags to preserve formatting (and make reading easier).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •