• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How do we use a Parameter more than once in a dynamic SQL with one Argument

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved How do we use a Parameter more than once in a dynamic SQL with one Argument

  1. How-to
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Tuesday, 23 July 2024 00:20 AM UTC

We are using PB17 and SQLAnywhere 17

We have a generalized routine whereby we can pass a SQL query which takes one argument.

The problem is that some queries need to reference the same argument more than once.

As we understand it - if you use the ? argument placeholder more than once - the system assumes as separate argument associated with each occurrance of the argument - hence the follwing fails

SELECT metadatavalue from propmetadata where metadatatypecd = 'SHORTSYNOPSIS' AND propassetid in (SELECT propassetid from propasset where propno = (left(?, 8) + 'S00') AND langcd = SUBSTRING(?, 16, 3) AND langusecd = SUBSTRING(?, 20, 3))

where the following single parameter reference works

SELECT metadatavalue from propmetadata where metadatatypecd = 'SHORTSYNOPSIS' AND propassetid in (SELECT propassetid from propasset where propno = (left(?, 8) + 'S00')  

We tried the following (noted as working in other database implementations) - which also doesnt work.

SELECT metadatavalue from propmetadata where metadatatypecd = 'SHORTSYNOPSIS' AND propassetid in (SELECT propassetid from propasset where propno = (left(?1, 8) + 'S00') AND langcd = SUBSTRING(?1, 16, 3) AND langusecd = SUBSTRING(?1, 20, 3))

Is there any way to do this in a straightforward manner??

Thanks

Glenn Barber

 

  1. Embedded SQL
  2. PowerBuilder (Appeon)
PowerBuilder App in VMWare display issues
Classic menu
Responses (3)
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Wednesday, 24 July 2024 06:00 AM UTC
  2. PowerBuilder
  3. # 1

You could try to rewrite your query. Something like this: (I'm not a sql anywhere expert!)

SELECT metadatavalue 
from propmetadata 
where metadatatypecd = 'SHORTSYNOPSIS' 
AND propassetid in 
  (SELECT propassetid 
   from propasset, 
        (select ? as param from sys.dummy) param_table
   where propno = (left(param_table.param, 8) + 'S00') 
     AND langcd = SUBSTRING(param_table.param, 16, 3) 
     AND langusecd = SUBSTRING(param_table.param, 20, 3))
Comment
Glenn Barber
  1. Glenn Barber
  2. Wednesday, 24 July 2024 16:35 PM UTC
Hi Rene

I tried something similar where using a derived value from the parameter and using that in the WHERE clause (seem to only work in SQL Anwhere) - However for some reason, it didnt work in the dynamic SQL



We were able to get it to work by creating a smart view which eliminated the complexity fo the query.

However the jury is still out whether there is a supported way to reference the same parameter more than once.

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 23 July 2024 17:29 PM UTC
  2. PowerBuilder
  3. # 2

Hi Glenn ;

  Why not just use a ":Var" variable declaration?

Alternatively if it's more complex, maybe use a Stored Procedure(s) instead.

Regards .. Chris

Comment
Glenn Barber
  1. Glenn Barber
  2. Wednesday, 24 July 2024 16:40 PM UTC
Doesn't seem to like named variable declarations in the SQL. We got around this using a view which simplified the dynamic query. However it would be nice if PB supported named vaiables as well as ?
  1. Helpful
There are no comments made yet.
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Tuesday, 23 July 2024 18:16 PM UTC
  2. PowerBuilder
  3. # 3

Hi Chris

We are using the the following generalized routine to execute predefined general SQL statements (:ls_sql) where the argument is identified as ? in the string

DECLARE C1 DYNAMIC CURSOR FOR SQLSA;
PREPARE SQLSA FROM :ls_sql;

Then calling this with where the value supplied in ls_arg populates the ? value

OPEN DYNAMIC C1 USING :ls_arg ;
FETCH C1 INTO :ls_metadatavalue;
CLOSE C1;

Are you saying that instead of using the ? in the predefined SQL we can use :var and it can be referenced multiple times in the SQL?

Thanks

Glenn

 

 

 

 

Comment
Load more comments
Glenn Barber
  1. Glenn Barber
  2. Tuesday, 23 July 2024 20:12 PM UTC
We tried replacing the ? with :var in the SQL passed in :ls_sql aand it didnt work. Are we missing something here?

  1. Helpful
René Ullrich
  1. René Ullrich
  2. Wednesday, 24 July 2024 05:53 AM UTC
From help of "Dynamic SQL Format 3": In the description for SQLStatement you can find: "reserved word substitution is not allowed". So you can only use "?" for parameters. A direct reference to PB variables in SQL is only possible for static cursors.

So I think the only way to have only one parameter is to rewrite the query (if possible, e.g. using the dummy system table).
  1. Helpful
Glenn Barber
  1. Glenn Barber
  2. Wednesday, 24 July 2024 16:38 PM UTC
Yes - our conclusion was similar - we got around this using a view which simplified the query - your dummy selection was interesting and we may try that - although as I had noted previously - we had some problem with that.

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.