Custom sql driven grid

Use this board to ask questions or have discussions with other Rich Displays users.
Post Reply
Wendy
Profound User
Posts: 37
Joined: Mon Aug 06, 2018 4:00 pm
First Name: Wendy
Last Name: Sauers
Company Name: Conestoga Wood Specialties
Phone: 5706589663
Address 1: 441 West Market St.
City: Beavertown
State / Province: Pennsylvania
Zip / Postal Code: 17812
Country: United States
Contact:

Custom sql driven grid

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Custom sql driven grid

Post by Scott Klement »

Can you post the SQL statement that doesn't work, and the error message you're receiving?
Wendy
Profound User
Posts: 37
Joined: Mon Aug 06, 2018 4:00 pm
First Name: Wendy
Last Name: Sauers
Company Name: Conestoga Wood Specialties
Phone: 5706589663
Address 1: 441 West Market St.
City: Beavertown
State / Province: Pennsylvania
Zip / Postal Code: 17812
Country: United States
Contact:

Re: Custom sql driven grid

Post by 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
Attachments
WH array.PNG
WH array.PNG (2.03 KiB) Viewed 1660 times
Supplydbdd.json
(94.51 KiB) Downloaded 136 times
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Custom sql driven grid

Post by 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
Profound User
Posts: 37
Joined: Mon Aug 06, 2018 4:00 pm
First Name: Wendy
Last Name: Sauers
Company Name: Conestoga Wood Specialties
Phone: 5706589663
Address 1: 441 West Market St.
City: Beavertown
State / Province: Pennsylvania
Zip / Postal Code: 17812
Country: United States
Contact:

Re: Custom sql driven grid

Post by 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
Attachments
WH array.PNG
WH array.PNG (1.4 KiB) Viewed 1657 times
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Custom sql driven grid

Post by 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
Profound User
Posts: 37
Joined: Mon Aug 06, 2018 4:00 pm
First Name: Wendy
Last Name: Sauers
Company Name: Conestoga Wood Specialties
Phone: 5706589663
Address 1: 441 West Market St.
City: Beavertown
State / Province: Pennsylvania
Zip / Postal Code: 17812
Country: United States
Contact:

Re: Custom sql driven grid

Post by 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
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests