Custom sql driven grid

[Wendy]

I have a simple grid where I'm pulling parameters from the prior screen selections. My users can select 1 or multiple warehouses to aggregate data. My grid will then should the details of all orders from several parameters but one being the multiple warehouses.

In the sql I want to say: Where WH in ('X','Y')

The 'in' clause doesn't seem to work.
I checked that the sql statement was syntactically correct in the PUISSNVP file. The sql statement will produce results in ACS with the syntax produced in PUISSNVP. Also, the variable holding the value of ('X','Y') displays properly on the screen making me confident that the variable has the correct data.

I'm hoping that this isn't a functionality that just doesn't work in Profound.

Thank you in advance for any help anyone can provide,
Wendy

[Scott Klement]

Can you post the SQL statement that doesn't work, and the error message you're receiving?

[Wendy]

I'm not receiving any errors, I'm just don't have any data returned.

Here is the sql in the screen format for the grid. I simply copied and pasted as it:

select WKPO#, WKPOLN, WKPN, WKCOMD, WKORDQ, WKRECQ, substr(char(WKRQDT),3,2) || '/' || substr(char(WKRQDT),5,2) || '/' || substr(char(WKRQDT),1,2), substr(char(WKRCDT),3,2) || '/' || substr(char(WKRCDT),5,2) || '/' || substr(char(WKRCDT),1,2), WKQTYV*-1, WKPFQT, WKDAYV, WKPFDT, WKWH from vr20p1 where WKMSTV = ? and wkrqdt >= ? and wkrqdt <= ? and wkwh IN %trim(?) and wkvent = ?

The next to the last parameter (4th parameter) is the problem.

When I change it to say wkwh = ? and pull a single warehouse field, it works fine. I've attached a screen shot showing the content of the 2 variables. The first being the one that works, the 2nd one used with the 'IN'. On the screen those variables are called WHS2, and WHArray respectively.

Thank you,
Wendy

[Scott Klement]

This code doesn't seem right:

Code: Select all

wkwh IN %trim(?) 

%trim() is an RPG built-in function. It is not an SQL function -- but SQL does have one named TRIM() that is similar.

Also, the argument to IN the IN predicate is typically a list of things (example follows.) I don't understand why you'd use IN with only one value.

Code: Select all

wkwh IN (?, ?, ?, ?)

or maybe

Code: Select all

wkwh IN (trim(?), trim(?), trim(?), trim(?))

Assuming you're using the Db2 for IBM i database, here are links to the manual for TRIM and IN:

TRIM function: https://www.ibm.com/support/knowledgece ... rimfun.htm
The IN predicate: https://www.ibm.com/support/knowledgece ... bafzin.htm

[Wendy]

I suspected %trim was a problem. The WHArray variable is bound to a hidden field on the screen that I using for the single parameter. I build it in the RPG program based on which warehouse checkboxes they select on the screen. I've attached a rendering snip of the variable WHArray showing 2 warehouses selected.

I've changed %trim(?) to just trim(?) and I still do not get any results.

Wendy

[Scott Klement]

I'm not sure if I completely understand what you are saying, but... are you telling me that your parameter marker contains the string "('E1', 'B2')"? You can't do that... You can't put multiple values and part of the SQL syntax into one parameter value. Each parameter value must represent just one value in the SQL statement.

To see the error message, have you looked at the network responses? Are you telling it to print the errors in an ondbload routine? https://docs.profoundlogic.com/x/hoLrAQ

[Wendy]

That's exactly what I did! I put all my parameter markers in a string and just used 1 parameter for them. After making all check boxes a separate parameters, it works fine. I do understand why the way I did it was wrong. I have to learn to think a little differently.

Thanks for the lessen!

Wendy