SAP SQL Anywhere

Question

I have a stored procedure not working for me

CREATE OR REPLACE PROCEDURE wslogin()
BEGIN
DECLARE @v_var varchar(256);
DECLARE @v_top int;

select value into @v_var from settings where keyword='CTYPE';
-- this value is something like E,1,3 or E,N,2,5

select count(name) into @v_top from customer where ctype in (@v_avar);
-- more code however this is the failure point I get 0 records
END

this does not find anything because customer.ctype is char(1)
I tried looping through creating varchar like 'E','1','3' ... still no luck
tried some casting and an unsuccessful array
I'm sure this may be simple, but I'm missing it.
thanks for the help

Answer 1

1

See sa_split_list:

http://dcx.sap.com/index.html#sqla170/en/html/8177739d6ce21014b82ebbcba7441f0b.html*loio8177739d6ce21014b82ebbcba7441f0b

-john.

permanent link

answered 05 Dec '16, 13:35

John Smirnios
12.0k●3●96●166
accept rate: 37%

Thanks John

this worked great

select count(name) into @v_top from customer
where ctype in (select row_value from sa_split_list(@v_var));

thanks again

(05 Dec '16, 15:15) tandersen42

flat view

Answer 2

Another option would be the LIKE Operator:
If you're sure to only have single char (or generally, matching length) elements in the list, you can check for "@v_var like '%' || ctype || '%'".
If you have elements of arbitrary length in the list, you must pre- and suffix both operands with the delimiter character, like "',' || @v_var || ',' like '%,' || ctype || ',%'".
In the second case: If you don't put the delimiters around the element you search for, you may get into matching substrings. If you don't put the extra delimiters around the list, the first and last list element won't match.

HTH
Volker
DB-TecKnowledgy

SAP SQL Anywhere

Stored Procedure varchar char convertion

Follow this question

Related questions