Genie grid not returning all rows

Use this board to ask questions or have discussions with other Genie users.
Post Reply
dougmatthews47
Profound User
Posts: 35
Joined: Fri Jan 12, 2018 10:53 am
First Name: Doug
Last Name: Matthews
Company Name: Maverick Transportation
Contact:

Genie grid not returning all rows

Post by dougmatthews47 »

Hello,

I have a very simple Genie grid with a simple SQL statement.
I am using custom sql in the grid data area...

SELECT ocrec# as Rec#, octyp as Type, ocloc as Location, ocdesc as Comment
FROM comment WHERE ocord# = ? and octyp = 'B' ORDER BY octyp

When I use the ? parameter value, Genie does not return all the rows,
When I use an actual value instead of the ? from the parameter value it returns all the rows.
i.e...
SELECT ocrec# as Rec#, octyp as Type, ocloc as Location, ocdesc as Comment
FROM comment WHERE ocord# = '1769954' and octyp = 'B' ORDER BY octyp

I have even tried using DATABASE FILE, FIELDS, and SELECTION CRITERIA with the parameter value and get the same results

Any suggestions would be greatly appreciated.
dougmatthews47
Profound User
Posts: 35
Joined: Fri Jan 12, 2018 10:53 am
First Name: Doug
Last Name: Matthews
Company Name: Maverick Transportation
Contact:

Re: Genie grid not returning all rows

Post by dougmatthews47 »

Just an update to this problem....

When I do not have an extra condition in the SQL statement i.e (and octyp = 'B')
The grid returns all the rows using the parameter value.
This only seems to happen when having more than one conditional statement in the SQL
dougmatthews47
Profound User
Posts: 35
Joined: Fri Jan 12, 2018 10:53 am
First Name: Doug
Last Name: Matthews
Company Name: Maverick Transportation
Contact:

Re: Genie grid not returning all rows

Post by dougmatthews47 »

Another Update....

I have tried..
var ordnum = get("D_1_23");
applyProperty("comgrid","parameter value",ordnum);
applyProperty("comgrid","widget type","grid");
in the LOAD function of the page

and I have tried..
script:get("D_1_23");
in the parameter value of the grid

and both do not return all rows when an additional condition in the SQL statement exists.
When only one condition exists all the rows get returned...
Weirdness!
User avatar
matt.denninghoff
Profound Logic Staff Member
Posts: 115
Joined: Wed Feb 10, 2016 3:53 pm
First Name: Matthew
Last Name: Denninghoff
Company Name: Profound Logic Software
State / Province: Ohio
Country: United States
Contact:

Re: Genie grid not returning all rows

Post by matt.denninghoff »

I'm not seeing the same problem. Perhaps I'm not understanding a step you described. I have this as a grid's "custom sql" property:

Code: Select all

select prid, pname from puisamples/prodp where prid < ?
and this as the "parameter value" property:

Code: Select all

script: get("D_1_13");
My screen's D_1_13 field happens to have a value of "68". I get 34 rows returned. That's the correct amount.

I can also hard-code "parameter value" to 68 in the grid properties and get the same result.

The only problem I see is when trying to get a value from a field that I added to the screen--in which case it doesn't seem to exist when the "parameter value" script is rendering.
dougmatthews47
Profound User
Posts: 35
Joined: Fri Jan 12, 2018 10:53 am
First Name: Doug
Last Name: Matthews
Company Name: Maverick Transportation
Contact:

Re: Genie grid not returning all rows

Post by dougmatthews47 »

add another condition into your sql stmt like..

select prid, pname from puisamples/prodp where prid < ? and pname = ?

Let me know if you get the correct number of rows returned.

This only happens when I use the parameter value with multiple conditions in the sql stmt

I have tried it with multiple parameter values and hardcoded values in the sql stmt
User avatar
matt.denninghoff
Profound Logic Staff Member
Posts: 115
Joined: Wed Feb 10, 2016 3:53 pm
First Name: Matthew
Last Name: Denninghoff
Company Name: Profound Logic Software
State / Province: Ohio
Country: United States
Contact:

Re: Genie grid not returning all rows

Post by matt.denninghoff »

I set my "custom sql" property to:

Code: Select all

select prid, pname from puisamples/prodp where prid < ? and pstock < ?
And I right clicked the "parameter value" property, then clicked "Add Another Parameter Value". Then under "parameter value 2" I set it to a field that evaluated to 84.

I get the same number of results as if I were to put 68 and 84 and the PRID and PSTOCK conditions: 31.
dougmatthews47
Profound User
Posts: 35
Joined: Fri Jan 12, 2018 10:53 am
First Name: Doug
Last Name: Matthews
Company Name: Maverick Transportation
Contact:

Re: Genie grid not returning all rows

Post by dougmatthews47 »

Oki Doki,
I'll look elsewhere for the issue....Wierdness

Thanks
Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests