cancel
Showing results for 
Search instead for 
Did you mean: 

Sub Select in Place Holders Parameters

Former Member
0 Kudos

Hi All,

Is it possible to pass a sub select query to  a parameter place holder

This is my regular query

select * from myview (

'PLACEHOLDER' = ('$$PCode$$', '''VALA'',''VALB'''));

I need to call this view in  a procedure  and i don't want to hard code place holder values

This is my procedure code

code  = select code from tablea;

varout  = select * from myview ( 'PLACEHOLDER' = ('$$PCode$$', :code ));

Thanks in advance

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member182302
Active Contributor
0 Kudos

U can use a input parameter in procedure and use the same

Regards,

Krishna Tangudu

Former Member
0 Kudos

How does that work ..

When i cannot pass a temp table code how does the input parameter work..

I'm talking hundreds of values in code ...

code  = select code from tablea;

varout  = select * from myview ( 'PLACEHOLDER' = ('$$PCode$$', :code ));

former_member182302
Active Contributor
0 Kudos

Got you. You basically want to send a list of values to the input parameter. 2 options you have here

1) To send the list of values as a string with comma separated and then use INSTR to filter

2) You can also directly place it as a where condition, instead of input parameter . If it is only the filtering you are looking out for. From SP10, atleast i have noticed the change in the performance and both with where clause and input parameter are working almost similar

Regards,

Krishna Tangudu

SergioG_TX
Active Contributor
0 Kudos

there is a screenshot on this blog where you can see how to use the input parameters from a view

hope this helps

Former Member
0 Kudos

Sergio,

I looked at your blog and tried doing the same but i'm running into some issues..I'm exactly looking to do what you have in the blog

This is how my place holder Syntax is - I'm on SPS10

'PLACEHOLDER' = ('$$Code$$', ''Code1'','Code2''')

I tried changing as per your blog

declare  v_Code_String varchar(5000);

SELECT STRING_AGG("CODE",''',''') into v_Code_String from (SELECT  DISTINCT "CODE" FROM "SCH"."CODE") b;

     'PLACEHOLDER' => ('$$Code$$', :v_Code_String ));

--     'PLACEHOLDER'.'$$Code$$'   => :v_Code_String );

sql syntax error: incorrect syntax near "=>": line 29 col 17 (at pos 973)

Any suggestions

SergioG_TX
Active Contributor
0 Kudos

Hari,

(PLACEHOLDER."$$<YOUR_INPUT_PARAMETER_CASE_SENSITIVE>$$" => yourStringValues)

if you have more than 1 input parameter, then add them by separating them with 1 comma every time... as I did on my example, I had 2 input parameters

yourStringValues must be comma separates and each of them need to be wrapper in single quotes so If you are passing more than one value you can see what I did on line 18 and also 21. Each of those select intos are for different input parameter values. 18 is for materials, 21 was for locations

makes sense?