ab5ni
Posts: 177
|
| Posted: 10/05/2006, 11:54 AM |
|
CCS keeps telling me that my SQL statement h as a problem.
Here's the code:
AND date(rdt) >= date({ldDateLow})
AND date(rdt) <= date({ldDateHigh})
Where:
rdt = datetime
ldDateLow = Value not yet set, but going to be picked up by a date-range selector page.
ldDateHigh = Value not yet set, but going to be picked up by a date-range selector page.
What the heck gives 
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
 |
 |
peterr
Posts: 5971
|
| Posted: 10/05/2006, 12:26 PM |
|
What is the error message?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/05/2006, 12:33 PM |
|
Hi again, peter, and thanks for the reply.
Loading fields list: [MySQL[ODBC 3.51 Driver][mysqld-4.1.21-standard]You have an
error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'AND date(rdt) >= date(0000-01-01 00:00)
AND date(rdt) <= date(0000-01-
and the message ends.
BTW, it would be nice if we could copy and paste the error messages
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/05/2006, 12:49 PM |
|
OK. I'm assuming that the error just doesn't fit in the window, or is cut off by the ODBC driver.
Can you check if your syntax works directly in MySQL?
For example such syntax would be invalid:
WHERE AND date(rdt) >= date(0000-01-01 00:00) AND date(rdt) <= date(0000-01-01 00:00)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 10/05/2006, 12:51 PM |
|
Also, based on http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html , I see that their examples have dates enclosed in single quotes but yours don't, so this could also be a problem.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/05/2006, 1:19 PM |
|
Hi again, Peter, and thanks for the quick reply.
>OK. I'm assuming that the error just doesn't fit in the window, or is cut off by the ODBC driver.
Yep. Cut off.
> Can you check if your syntax works directly in MySQL?
I can try. It's a hairy SELECT...here's the entire query:
SELECT x.dsid, x.rdt, date_format(x.rdt,'%h') as rdt_hour,
date_format(x.rdt,'%i') as rdt_min,
x.ttyp, x.tum, t.cname as tco,
concat(TRIM(org.ccity), ", ", TRIM(org.cstate)) as origin_city,
org.cname as orgin_loc,
dst.cname as dest_loc,
concat(TRIM(dst.ccity), ", ", TRIM(dst.cstate)) as dest_city,
cmp.cname as client,
y.afe, y.type, y.bkname, y.rgname, y.wname, concat(date_format(rdt,'%b'), date_format(rdt,'%y'),substring(dsheet.dsid,2,10)) as dnum,
x.fsc-x.fsc as bRate,
x.fsc-x.fsc as ISC,
x.fsc-x.fsc as FSC,
x.fsc-x.fsc as per,
x.fsc-x.fsc as nen,
x.fsc-x.fsc as haz,
x.fsc-x.fsc as hr,
x.fsc-x.fsc as det,
x.fsc-x.fsc as other,
x.fsc-x.fsc as sTotal,
x.fsc-x.fsc as bHaul,
x.fsc-x.fsc as Total,
x.fsc-x.fsc as Savings,
x.edt, date_format(x.edt,'%h') as edt_hour,
date_format(x.edt,'%i') as edt_min,
concat(TRIM(uo.fname), " ", TRIM(uo.lname)) as orderedby,
concat(TRIM(ue.fname), " ",TRIM(ue.lname)) as enteredby
FROM omstruck_cd.dsheet x
LEFT OUTER JOIN omstruck_cd.accounts y ON x.acc = y.afeid
LEFT JOIN omstruck_cd.companies cmp ON cmp.cmpid = y.cmpid
LEFT JOIN omstruck_cd.companies org ON org.cmpid = x.ploc
LEFT JOIN omstruck_cd.companies dst ON dst.cmpid = x.sloc
LEFT JOIN omstruck_cd.companies t ON t.cmpid = x.tco
LEFT JOIN omstruck_cd.users uo ON uo.uid = x.dby
LEFT JOIN omstruck_cd.users ue ON ue.uid = x.eby
WHERE cmp.cname like {lcCname}
AND date(rdt) >= date({ldDateLow})
AND date(rdt) <= date({ldDateHigh})
AND x.acc = {lcAccID}
>For example such syntax would be invalid:
>WHERE AND date(rdt) >= date(0000-01-01 00:00) AND date(rdt) <= date(0000-01-01 00:00)
Really? Why is that? Looks like legit code to me. (Shrug.)
That is, date() is just stripping the time portion off of the datetime field rdt,
and date(0000-01-01 00:00) should be translated to 0000-01-01, you'd
think?? So, isn't it just the comparison of two dates, ot does MySQL
allow for such a comparison? I'm coming from the Visual FoxPro world,
BTW, and something very close to this is legit code. I've also tried
using date_format() to make sure that the dates were properly formated,
but Report Builder's "Visual Query Builder" would complain about that, too.
TIA, dewd, and keep up the good work! We really enjoy your support!
Randall
p.s.
I realize that you can't run the query (unless you create a dummy database
and all). I just included the code so you can see the entire query, and
maybe you might see a flaw or three :^).
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/05/2006, 1:35 PM |
|
I meant that I didn't see your full syntax and had to assume that the problem could be around the "AND", so I mentioned that the syntax "WHERE AND ..." is invalid. (it'd had to be "WHERE something AND ..."
Also see my previous post about quotes. Not sure if that's the problem, but I don't have other ideas...
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/05/2006, 1:48 PM |
|
>I meant that I didn't see your full syntax and had to assume that the problem could be around the
> "AND", so I mentioned that the syntax "WHERE AND ..." is invalid. (it'd had to be "WHERE
>something AND ..."
Ah. Okay. Gotcha.
>Also see my previous post about quotes. Not sure if that's the problem, but I don't have
>other ideas...
Okay. Will do, Peter. Also, don't worry too much about it. If I can't make the AND clauses
work in the current format, I'll just see if I can try another approach. If I get stuck in that
approach, I'll probably show back up here and see if anyone has any ideas.
Thanks, dewd.
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/05/2006, 2:37 PM |
|
Ok...tried this...didn't like it
WHERE rdt BETWEEN QUOTE(date({ldDateLow})) AND QUOTE(date({ldDateHigh}))
AND cmp.cname like {lcCname}
AND x.acc = {lcAccID}
Maybe this could be the problem: The page where the variables are located
hasn't been designed yet; that is, there not set up as Session variables yet,
if I understand CCS correctly. BTW, I come from the land of programming
PHP and hitting a MySQL database directly. I'm learning the CCS way of doing
things "on the fly," or one could say "by the seat of my pants." In other words,
the powers that be want a page up doing this app NOW, or close to it.
Anywho, should I get things wrong and use the incorrect CCS syntax when
talking about things, please forgive me, because it usually means that I don't
have the time to read the entire tutorial and have to search the example database
to see how things are done.
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/06/2006, 1:05 AM |
|
There is not much CCS syntax there, mainly MySQL.
But I'm still curious about those quotes. Have you tried if this syntax works in MySQL at all?:
date(0000-01-01 00:00)
vs:
date('0000-01-01 00:00')
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/09/2006, 7:55 AM |
|
Hi again, Peter.
CCS didnt' complain about this query:
SELECT x.dsid, x.rdt, date_format(x.rdt,'%h') as rdt_hour,
date_format(x.rdt,'%i') as rdt_min,
x.ttyp, x.tnum, t.cname as tco,
concat(TRIM(org.ccity), ", ", TRIM(org.cstate)) as origin_city,
org.cname as orgin_loc,
dst.cname as dest_loc,
concat(TRIM(dst.ccity), ", ", TRIM(dst.cstate)) as dest_city,
cmp.cname as client,
y.afe, y.type, y.bkname, y.rgname, y.wname, concat(date_format(rdt,'%b'), date_format(rdt,'%y'),substring(dsid,2,10)) as dnum,
x.fsc-x.fsc as bRate,
x.fsc-x.fsc as ISC,
x.fsc-x.fsc as FSC,
x.fsc-x.fsc as per,
x.fsc-x.fsc as nen,
x.fsc-x.fsc as haz,
x.fsc-x.fsc as hr,
x.fsc-x.fsc as det,
x.fsc-x.fsc as other,
x.fsc-x.fsc as sTotal,
x.fsc-x.fsc as bHaul,
x.fsc-x.fsc as Total,
x.fsc-x.fsc as Savings,
x.edt, date_format(x.edt,'%h') as edt_hour,
date_format(x.edt,'%i') as edt_min,
concat(TRIM(uo.fname), " ", TRIM(uo.lname)) as orderedby,
concat(TRIM(ue.fname), " ",TRIM(ue.lname)) as enteredby
FROM dsheet x
LEFT OUTER JOIN omstruck_cd.accounts y ON x.acc = y.afeid
LEFT JOIN omstruck_cd.companies cmp ON cmp.cmpid = y.cmpid
LEFT JOIN omstruck_cd.companies org ON org.cmpid = x.ploc
LEFT JOIN omstruck_cd.companies dst ON dst.cmpid = x.sloc
LEFT JOIN omstruck_cd.companies t ON t.cmpid = x.tco
LEFT JOIN omstruck_cd.users uo ON uo.uid = x.dby
LEFT JOIN omstruck_cd.users ue ON ue.uid = x.eby
WHERE rdt BETWEEN '{ldDateLow}' AND '{ldDateHigh}'
AND cmp.cname like '{lcCname}'
AND x.acc = '{lcAccID}'
I've also written another page that contains 2 DatePickers
and 2 Listbox selectors. Now all I have to do is pass the
"lcVariables" to the report page.
BTW, the code listed above is just to select the records
we're interested in for a particular company at a given
location. Now I have to use some of the fields in each
record to calculate costs and display that data in the
report. What would be the best way of doing this? Cursor?
Custom PHP code that does mysql_query(), calculates
the fields, and then stores the results into a temporary
table, feeding the temporary table to Report Builder?
Suggestions? 
TIA,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 10/11/2006, 7:21 AM |
|
Hi again, Peter.
Here are two queries for you to look over. The first works perfectly in MySQL,
and the second, which uses the DatePicker code and CCS variables doesn't.
Query 1 (works in MySQL):
SELECT x.dsid, x.rdt, date_format(x.rdt,'%h') as rdt_hour,
date_format(x.rdt,'%i') as rdt_min,
x.ttyp, x.tnum, t.cname as tco,
concat(TRIM(org.ccity), ", ", TRIM(org.cstate)) as origin_city,
org.cname as orgin_loc,
dst.cname as dest_loc,
concat(TRIM(dst.ccity), ", ", TRIM(dst.cstate)) as dest_city,
cmp.cname as client,
y.afe, y.type, y.bkname, y.rgname, y.wname, concat(date_format(rdt,'%b'), date_format(rdt,'%y'),substring(dsid,2,10)) as dnum,
x.fsc-x.fsc as bRate,
x.fsc-x.fsc as ISC,
x.fsc-x.fsc as FSC,
x.fsc-x.fsc as per,
x.fsc-x.fsc as nen,
x.fsc-x.fsc as haz,
x.fsc-x.fsc as hr,
x.fsc-x.fsc as det,
x.fsc-x.fsc as other,
x.fsc-x.fsc as sTotal,
x.fsc-x.fsc as bHaul,
x.fsc-x.fsc as Total,
x.fsc-x.fsc as Savings,
x.edt, date_format(x.edt,'%h') as edt_hour,
date_format(x.edt,'%i') as edt_min,
concat(TRIM(uo.fname), " ", TRIM(uo.lname)) as orderedby,
concat(TRIM(ue.fname), " ",TRIM(ue.lname)) as enteredby
FROM dsheet x
LEFT OUTER JOIN accounts y ON x.acc = y.afeid
LEFT JOIN companies cmp ON cmp.cmpid = y.cmpid
LEFT JOIN companies org ON org.cmpid = x.ploc
LEFT JOIN companies dst ON dst.cmpid = x.sloc
LEFT JOIN companies t ON t.cmpid = x.tco
LEFT JOIN users uo ON uo.uid = x.dby
LEFT JOIN users ue ON ue.uid = x.eby
WHERE date(x.rdt) >= '2006-01-01'
AND date(x.rdt) <= '2006-10-10'
AND cmp.cname like 'BURLINGTON%'
BTW, the query listed above does not work with CCS. That is,
selecting the exact same database and hard-coding the variables
works under MySQL but doesn't work under CCS.
Second query that doesn't work, using DatePicker():
SELECT x.dsid, x.rdt, date_format(x.rdt,'%h') as rdt_hour,
date_format(x.rdt,'%i') as rdt_min,
x.ttyp, x.tnum, t.cname as tco,
concat(TRIM(org.ccity), ", ", TRIM(org.cstate)) as origin_city,
org.cname as orgin_loc,
dst.cname as dest_loc,
concat(TRIM(dst.ccity), ", ", TRIM(dst.cstate)) as dest_city,
cmp.cname as client,
y.afe, y.type, y.bkname, y.rgname, y.wname, concat(date_format(rdt,'%b'), date_format(rdt,'%y'),substring(dsid,2,10)) as dnum,
x.fsc-x.fsc as bRate,
x.fsc-x.fsc as ISC,
x.fsc-x.fsc as FSC,
x.fsc-x.fsc as per,
x.fsc-x.fsc as nen,
x.fsc-x.fsc as haz,
x.fsc-x.fsc as hr,
x.fsc-x.fsc as det,
x.fsc-x.fsc as other,
x.fsc-x.fsc as sTotal,
x.fsc-x.fsc as bHaul,
x.fsc-x.fsc as Total,
x.fsc-x.fsc as Savings,
x.edt, date_format(x.edt,'%h') as edt_hour,
date_format(x.edt,'%i') as edt_min,
concat(TRIM(uo.fname), " ", TRIM(uo.lname)) as orderedby,
concat(TRIM(ue.fname), " ",TRIM(ue.lname)) as enteredby
FROM dsheet x
LEFT OUTER JOIN accounts y ON x.acc = y.afeid
LEFT JOIN companies cmp ON cmp.cmpid = y.cmpid
LEFT JOIN companies org ON org.cmpid = x.ploc
LEFT JOIN companies dst ON dst.cmpid = x.sloc
LEFT JOIN companies t ON t.cmpid = x.tco
LEFT JOIN users uo ON uo.uid = x.dby
LEFT JOIN users ue ON ue.uid = x.eby
WHERE date(x.rdt) >= '{ldDateLow}'
AND date(x.rdt) <= '{ldDateHigh}'
AND cmp.cname like '{lcCname}%'
All the dates are in their proper format. I check the URL that's being
passed to the query, and all looks well.
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/12/2006, 12:51 AM |
|
Hi Randall,
Well, I have to repeat again the same answer (or question) again - do both these SQL syntax variants work OK for you in MySQL:
date(0000-01-01 00:00)
vs:
date('0000-01-01 00:00')
?
If not, then this was the problem all along. If yes, then I don't have any other ideas for now.
Thanks.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/12/2006, 1:05 AM |
|
Switch on Debug(), so the SQL is actually shown after the labels are filled.
Copy that shown SQL in to you SQL CLI and see if THAT SQL works ok.
I agree with P, expecting single quotes around the dates.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
