on 04-27-2016 6:53 PM
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
U can use a input parameter in procedure and use the same
Regards,
Krishna Tangudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
there is a screenshot on this blog where you can see how to use the input parameters from a view
hope this helps
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
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?
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.