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
Custom sql driven grid
-
- 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:
-
- 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
Can you post the SQL statement that doesn't work, and the error message you're receiving?
-
- 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
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
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 (2.03 KiB) Viewed 1660 times
-
- Supplydbdd.json
- (94.51 KiB) Downloaded 136 times
-
- 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
This code doesn't seem right:
%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.
or maybe
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
Code: Select all
wkwh IN %trim(?)
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 (?, ?, ?, ?)
Code: Select all
wkwh IN (trim(?), trim(?), trim(?), trim(?))
TRIM function: https://www.ibm.com/support/knowledgece ... rimfun.htm
The IN predicate: https://www.ibm.com/support/knowledgece ... bafzin.htm
-
- 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
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
I've changed %trim(?) to just trim(?) and I still do not get any results.
Wendy
- Attachments
-
- WH array.PNG (1.4 KiB) Viewed 1657 times
-
- 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
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
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
-
- 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
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
Thanks for the lessen!
Wendy
Who is online
Users browsing this forum: No registered users and 2 guests