in 11.1 parameterized SQL queries were introduced. i have one example:
PARAMETERS(DAYS INTEGER)
SELECT *
FROM tasks
WHERE modified > TIMESTAMPADD(DAY, -1*DAYS, NOW())
is there any more documentation on this? if i want to declare 2 parameters, does it work like:
PARAMETERS(DAYS INTEGER, DAYS2 INTEGER)
Can we do anything fancier like give them a default value? thanks. |
| |
| Ben Bimber responded: |
2011-05-11 11:17 |
ok, that's actually good enough. thanks. |
| |
| Steve responded: |
2011-05-11 11:34 |
Here are some other examples from the spec, as a stopgap before the docs are finished:
PARAMETERS(X INTEGER DEFAULT 5, Y VARCHAR)
SELECT *
FROM Table R
WHERE R.x = X AND R.y LIKE Y || ‘%’
PARAMETERS(X INTEGER, Y VARCHAR)
SELECT X, UPPER(Y), Q1.*
FROM Q1 |
| |
| Matthew Bellew responded: |
2011-05-11 17:48 |
There is a small reason we did not document DEFAULT. We simply hadn't sorted out the behavior of DEFAULT in all different cases. Sometimes it's hard to tell if a parameter was not provided or was provided but explicitly set to NULL, etc.
So if you use this feature, just be aware of that. |
| |
| Ben Bimber responded: |
2011-05-11 17:55 |
hi matt,
i get the complication. so if you specify a default in the SQL statement, will it be essentially like not having the param at all?
one more thing: i wrote a parameterized query based on a study dataset. the SQL is:
PARAMETERS(STARTDATE TIMESTAMP, ENDDATE TIMESTAMP)
SELECT *
FROM study.housing h
WHERE
(STARTDATE >= h.date AND STARTDATE < COALESCE(h.enddate, curdate()))
OR
(COALESCE(ENDDATE, curdate()) > h.date AND COALESCE(ENDDATE, curdate()) <= COALESCE(h.enddate, curdate()))
Other than a complex where clause, pretty simple. Study datasets have all sort of metadata. For example, lsid is hidden by default. When I run this query, every single column shows up. They also dont seem to have their usual labels applied. Is that expected?
This could well be related to the 'select *', as opposed to the parameter. |
| |
| Ben Bimber responded: |
2011-05-12 12:03 |
Hi Matt,
One more thing:
This query has 2 param: start time and stop time. Ideally, I'd let to get it to behave such that if the user does not supply a value for end date, it will default to now(). however, it appears functions are not supported in the parameter syntax:
PARAMETERS(STARTDATE TIMESTAMP, ENDDATE TIMESTAMP DEFAULT curdate())
that doesnt work. do i have any options here?
also, for what it's worth, if you have a param and do not set a default in your SQL statement, labkey fails in a pretty ugly way if the user tries to load the query without providing one.
-ben |
| |
|
|