cancel
Showing results for 
Search instead for 
Did you mean: 

Problem binding variable to query in procedure

patrickbachmann
Active Contributor
0 Kudos

Hi folks,

I'm having an issue binding a variable to a query.  For example the following works in my procedure;

DECLARE V_PLANT NVARCHAR(4) := 'ACME';

tempVar = select field1 from myView where Plant = :V_PLANT;

When I debug my procedure I can see the variable V_PLANT = 'ACME' and tempVar contains 1 single value (field1) record.

Now, alternatively, if I try passing my variable like this it is not working (get error: no data found);

DECLARE V_PLANT NVARCHAR(4) := 'ACME';

ABCVariable NVARCHAR(45) := '';

select field1 into ABCVariable from myView where Plant = :V_PLANT;

However if I hard code the plant variable as a literal string like this I indeed get data;

DECLARE V_PLANT NVARCHAR(4) := 'ACME';

ABCVariable NVARCHAR(45) := '';

select field1 into newVariable from myView where Plant = 'ACME';

Result is I have a value of 'ACME' in ABCVariable.  It seems when I'm using SELECT INTO syntax I can no longer use variables in the where condition.

Any suggestions?

Thanks,

-Patrick

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Patrick

I cannot reproduce this problem:

create procedure get_article_by_label (IN v_article_label varchar)

language sqlscript

as

begin

    declare v_article_id integer := -1;

   

    select max(article_id) into v_article_id

    from efashion.article_lookup

    where UPPER(article_label) like '%' || UPPER(:v_article_label) || '%';

   

    select * from efashion.article_lookup

    where article_id = :v_article_id;

end;

call get_article_by_label ('Leather Belt');

/*

ARTICLE_ID  ARTICLE_LABEL           CATEGORY            SALE_PRICE  FAMILY_NAME FAMILY_CODE

143848      Patchwork Leather Belt  Belts,bags,wallets  88.9        Accessories F60       

*/

As you see I use the input variable in the WHERE condition, and reuse the found ID to select data again.

Not sure though, why it doesn't work in your case.

Is the target variable in your working example also ABCVariable or indeed newVariable?

- Lars

patrickbachmann
Active Contributor
0 Kudos

Ooops that was a typo, it should read ABCVariable not newVariable.  I will correct in the above post.  Thanks for trying, I'm doing this in 68 as we have issues with our 72 server and have temporarily shelved it.  But i will try on that system to see if it behaves differently and update this thread soon.

Thanks,

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ok it indeed worked in 72 but does not work in 68 so seems to be a 68 bug.  Now to further complicate my life my actual query pulls data from a ZTABLE where our ABAP developer has a field called APPLICATION.  Apparently I found a NEW bug in 72 as it does not like the word APPLICATION passed in the where clause like this!

select field1 from table1 WHERE APPLICATION = 'XYZ'

Error = APPLICATION is incorrect of misplaced.

It likes all of my fields in where condition except will not allow the word 'application'.

Such fun!

-Patrick

patrickbachmann
Active Contributor
0 Kudos

If you are curious try just typing this into your procedure editor... as soon as you type the word APPLICATION you will get error....

tempVar = select APPLICATION from.......

well at least in 72 it seems to be the case.  Will create a message with SAP.  But I'm sure it's probably a new reserved word.

patrickbachmann
Active Contributor
0 Kudos

Lars I'm now thinking the variable binding issue is due to my select being against a VIRTUAL table. The virtual table actually points to our 72 HANA box.  I copied the table definition into the same box as my procedure (my 68 environment) and copied all records into it from the virtual table and then changed my procedure to use the physical table instead of the virtual table and it worked!

-Patrick

former_member185132
Active Contributor
0 Kudos

Hi Patrick,

If APPLICATION is a reserved word, could you try to enclose it in double quotes? Perhaps that will allow the word to be used as a column name.

Regards,

Suhas

Answers (0)