Re: exec into question

In my experience i use dynamic SQL where needed, and static where not.

One of the best examples would be something like a search done on EBay
where there are hundreds of possible selections, ranges, options, etc.

In that case what would you rather do? Build a large nested select/if/etc
statement with every possible selection, or dynamically build the SQL
statement and execute it, and sacrifice a few cpu cycles?

For me its the latter. But the OP didn't really mention the context here.

Bradley V. Stone
www.bvstools.com
GreenTools for PayPal <https://www.bvstools.com/g4pp.html>: Easily send
PayPal invoices from your IBM i applications using RPG ILE! Process
refunds, get account balances and more!

On Fri, Jul 20, 2018 at 12:47 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi tim

You can't do it this way - host variables are for values, not
expressions - so you would have to specify the column name in the
statement, not as part of a string - host variables are not for
substitution into the statement.

Here's how it would look with a host variable:

testvalue = 100

exec sql SELEC count(*)
into :cnt
FROM claims
where number = :testvalue

This is a better performer than this option - put the entire statement
into a character variable and prepare a statement from that variable -
then execute it. I don't want to make an example of that here, too easy
to mislead you.

HTH
Vern

On 7/20/2018 11:17 AM, tim wrote:

i have the following:

exec sql SELECT count(*)
into :cnt
FROM claims;


i would like to do the following (variable where build from field)

wherestr = 'number = 100'

exec sql SELECT count(*)
into :cnt
FROM claims
where :wherestr


can this be done somehow?

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD