coyote999
Posts: 22
|
| Posted: 07/14/2011, 3:37 AM |
|
Hello Codecharge-Community,
I created a grid with a custom SQL-Query.
SELECT xxx, src
FROM table
WHERE src='{src}'
As SQL-Parameter I set "src" (Type: URL, Text; Design Default Value: testsrc, default value: empty)
When I show the page with page.php?src=testsrc it works fine and show all records with src=testsrc, but if I leave the parameter empty the result is "no records found"
How can I show the grid with all values, when I leave the parameter empty?
Thanks for your replies & greetings from germany.
Martin
|
 |
 |
tfertil
Posts: 43
|
| Posted: 07/14/2011, 5:35 AM |
|
Hi, coyote999
When you use a table as datasource, you can choose to "omit" any parameter not specified.
This is NOT the same as leaving a parameter with a NULL value.
In the first case, when you omit, or there is no value for a parameter, the WHERE statement built does not contain the comparison.
But when you specify a custom SQL query (and that's your case), the WHERE is always there, but if you omit the parameter then you're comparing with NULL.
So you can change your SQL to this:
SELECT xxx, src
FROM table
WHERE src = ISNULL({src},src)
This way when you have no value for {src} the field compares to itself resulting always true, so the full table will be listed.
NOTES:
1. This syntax is for Microsoft SQL-Server. If you're using another database, you must adjust it to the correct function or syntax for ISNULL.
2. This works as long as NULL is NOT one of the permitted values for the field.
Regards,
|
|
|
|
|
