longleyr
Posts: 6
|
| Posted: 12/15/2014, 6:21 PM |
|
I have a complex query that is part parameter and part expression. I need to use the date1 parameter from the parameter portion of the query as part of the expression. In SQL view it looks like '(date1)' in two places but when it executes the '(date1)' that is part of the expression is read as a string and not the value of date1 that is in the parameter created portion of the SQL statement. How do I get the date1 value to be read as part of the expression?
|
 |
 |
eratech
Posts: 513
|
| Posted: 12/16/2014, 10:20 PM |
|
Make sure it's in it's curly braces.
For example I use somethng like this all the time, which uses the parameter in the expression and the result:
WHERE organisations.id = (case when {organisation_id}=0 then organisations.id else {organisation_id} end)
Or have I mis-understood your situation? If so, please provide some example code.
E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
longleyr
Posts: 6
|
| Posted: 12/17/2014, 7:08 PM |
|
It still doesn't seem to recognize it. I tried {date1}, date1, and '{date1}'
I added a debugging script and it doesn't see to recognize it as value, it just thinks it is a string.
The only difference is that I get a syntax error with {date1} and I get a conversion error with '{date1}'
Either way I'm still stuck
|
|
|
|
eratech
Posts: 513
|
| Posted: 12/17/2014, 10:43 PM |
|
Can you paste in some of your SQL and I'll give it a try?
Also - are you using MS SQL or MySQL or another DB? That might affect it.
In thing to consider - I have had success with complex queries by making a view first that does the hard stuff and selecting from that. If you are using CodeCharge Studio you can also get data from a stored procedure. Just a suggestion.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
longleyr
Posts: 6
|
| Posted: 12/18/2014, 3:52 AM |
|
this is SQL Server
Here's the relevant portion of the SQL which if I plug in values works fine in SQL server directly. Note that I'm using the date parameter twice because I have to check against two different conditions so a view is out of the question.
WHERE (change_log.log_date='{date1}' and (infochange=1)) and (cases.caseid NOT IN (select caseid from change_log-where log_date<='{date1}' and info change<>1))
So basically I'm checking that one condition exists in the time specified and that another condition doesn't exist..
In the generated code the first '{date1}' is a parameter within the application and the second is part of an expression that I created manually. In the generated output, the first '{date1}' is replaced with the date from the form input, and the second one still says '{date1}'
|
|
|
|
eratech
Posts: 513
|
| Posted: 12/18/2014, 8:16 PM |
|
That is really odd - as you can see from my example, I use duplicates (and sometimes triplicate) versions of parameters in my SQL without problems.
The {date1} should be replaced by the code before it generates it so all should be good.
One suggestion to try - create a {date2} but give it the same source as {date1} and use it in the second spot.
Have you also tried setting the 'debug' flag to output all the SQL run on a page, just to confirm?
I can't recall the ASP file name but it's probably in the Common files called 'db_mssql.asp' or similar (PHP is db_mysqli.php for my MySQL project).
Edit and change value of '$Debug' from 0 to 1, then publish, and all the queries will be printed before the page. Obviously, change it back when you put it live.
E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
longleyr
Posts: 6
|
| Posted: 12/19/2014, 3:28 AM |
|
I didn't set to debug but I did add a response.write to output the SQL which does the same thing. I've tried different names for the value and this is just one of two different pages with different named values that I get the same results.
|
|
|
|
eratech
Posts: 513
|
| Posted: 12/21/2014, 8:00 PM |
|
Just wondering - are you using the "Query type" (aka "Data Source Type") of 'Table' or 'SQL'?
I know these sort of things work in 'SQL' mode, but sometimes I have added WHERE criteria in "Table" mode that make CCS cry a little. I've switched it to 'SQL' and had more luck.
Guessing a little here.
E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
|
