on 05-10-2014 4:46 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
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
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.