Embedded SQL Placeholder

[mikecorbo2]

I am trying to use a placeholder does a select with an "in" and am not sure of the correct format.

sqlstmt = 'select * from table where field1 in (?);
and then the placeholder would be
phtest = 'VALUE1','VALUE2';
or
'select * from table where field 1 in ?
phtest = '('VALUE1','VALUE2')';

[TedHolt]

You can't cram two values into one placeholder, Mike. The proper syntax (this is not tested) is something like this:

select * from table where field1 in (?,?)

[JonBoy]

This is surely a case where dynamic SQL is called for. Unless of course the list of possible values for the IN clause is limited in the way that Ted demonstrated.

[mikecorbo2]

This is surely a case where dynamic SQL is called for. Unless of course the list of possible values for the IN clause is limited in the way that Ted demonstrated.

I appreciate the help, i am usually good with sql but this one has had me boggled for a few days. It requires selected pay code & hours from a file. The user can select up to 5 pay codes that can be stored in any of 10 fields in the file and I have to retrieve the corresponding hours field for each matching pay code.

so the sql could potentially look like this
select paycode1, paycode2, ...,paycode10, hour1, hours2,...,hours10 from table where paycode1 in (?,?,?,?,?) paycode2 in (?,?,?,?,?) ... all the way up to paycode10. but if the user only entered 1 paycode, i wouldn't need all 5 placeholders.

[JonBoy]

The for sure dynamic is the way to go. Why are you not considering it?

[TedHolt]

No wonder it's got your boggled, Mike. What you have are ten buckets for codes and ten corresponding buckets for hours. This is a violation of 1NF, which makes the table difficult to query.

What you might do is to create a view to make the data look normalized. Something like this:

create or replace view PayView (Key, DataField, Sequence, PayCode, Hours)
as
select Key, DataField, 1, PayCode1, Hours1 from session.mytable where PayCode1 <> ''
union all
select Key, DataField, 2, PayCode2, Hours2 from session.mytable where PayCode2 <> ''
union all
select Key, DataField, 3, PayCode3, Hours3 from session.mytable where PayCode3 <> ''
union all
select Key, DataField, 4, PayCode4, Hours4 from session.mytable where PayCode4 <> ''
union all
select Key, DataField, 5, PayCode5, Hours5 from session.mytable where PayCode5 <> ''
union all
select Key, DataField, 6, PayCode6, Hours6 from session.mytable where PayCode6 <> ''
union all
select Key, DataField, 7, PayCode7, Hours7 from session.mytable where PayCode7 <> ''
union all
select Key, DataField, 8, PayCode8, Hours8 from session.mytable where PayCode8 <> ''
union all
select Key, DataField, 9, PayCode9, Hours9 from session.mytable where PayCode9 <> ''
union all
select Key, DataField, 10, PayCode10, Hours10 from session.mytable where PayCode10 <> '';

Where I have KEY, you will need to have all the key fields to the file. Where I have DATAFIELD, you will have to include all the needed non-key fields except for the buckets. This will let you query the data as if it were normalized.

select * from PayView where paycode in ('OT', 'HOL');

You can also do the same sort of thing with a common table expression, but it seems to me you may as well create a view.

That takes care of one problem -- that a pay code can be in any bucket.

The other part is checking against a list of 1 to 5 values. You can do that with dynamic SQL, as Jon points out. You'll do IF tests as you're building the where clause in a query string like the one in the previous code segment.

Another idea is to put the searched-for pay codes into a table and use it to select. Here's an article that explains that approach.

https://www.itjungle.com/2015/03/31/fhg033115-story02/

Instead of IN, you can also join to the temporary table that has the user's input values.

I don't know how all this would perform for you. You'll just have to try it and see.

[mikecorbo2]

Since all 10 field names are the same, excep they have 01 -10 appended to them, i built a nifty little subroutine to create the select for the sql statement

midsql = '(';
for im = 1 to 10;
midsql = midsql + 'TLPC' + %subst((%editc(im:'X')):4:2) + ' IN (' ;
for jm = 1 to 5;
if codes(jm) <> *blanks;
midsql = midsql + '''' + %trim(codes(jm)) + '''';
if codes(jm+1) <> *blanks;
midsql = midsql + ',';
endif;
endif;
endfor;
midsql = midsql + ')';
if im < 10;
midsql = midsql + ' OR ';
ENDIF;
endfor;
midsql = midsql + ')';
sqlstmt = sqlstmt1 + midsql + sqlstmt2;

codes are the selected paycodes (LEVQT, OT, VAC and REG)

midsql: (TLPC01 IN ('LEVQT','VAC','OT','REG') OR TLPC02 IN ('LEVQT','VAC','OT','REG') OR TLPC03 IN ('LEVQT','VAC','OT','REG') OR TLPC04 IN ('LEVQT','VAC','OT','REG') OR TLPC05 IN ('LEVQT','VAC','OT','REG') OR TLPC06 IN ('LEVQT','VAC','OT','REG') OR TLPC07 IN ('LEVQT','VAC','OT','REG') OR TLPC08 IN ('LEVQT','VAC','OT','REG') OR TLPC09 IN ('LEVQT','VAC','OT','REG') OR TLPC10 IN ('LEVQT','VAC','OT','REG'))

[TedHolt]

Thanks for sharing that, Mike. That will select the rows with the sought-for pay codes. How will you get the corresponding hours?

[mikecorbo2]

Thanks for sharing that, Mike. That will select the rows with the sought-for pay codes. How will you get the corresponding hours?

the missing sql selects all 10 paycode fields and all 10 hours fields from each record into a data structure, then I moved it into a corresponding dimensioned array where i check for the paycodes I needed and selected the matching hours.