cfquery - query of quries error 1

[countdrak]

I keep getting this error---

Query Of Queries syntax error.
Encountered "2004" at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

Anyone know whats going on!?

SELECT *
FROM Request.GetContent
WHERE Site = 'Public'
AND Fuse = 'About Us'
AND Type = 'M'
AND ((Publish = 1 AND StartPub IS NULL AND EndPub IS NULL)
OR (Publish = 1 AND StartPub >= '#CreateODBCDate(Now())#' AND EndPub IS NULL)
OR (Publish = 1 AND StartPub IS NULL AND EndPub <= '#CreateODBCDate(Now())#')
OR (Publish = 1 AND StartPub >= '#CreateODBCDate(Now())#' AND EndPub <= '#CreateODBCDate(Now())#')
)
ORDER BY EID

 

[webmigit]

Query of Queries have a syntax all their own, as does every sql language flavor.....

Try not quoting the Dates...

SELECT *
   FROM   Request.GetContent
   WHERE Site = 'Public'
   AND      Fuse = 'About Us'
   AND   Type = 'M'
   AND ((Publish = 1 AND StartPub IS NULL AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub >= #CreateODBCDate(Now())# AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub IS NULL AND EndPub <= #CreateODBCDate(Now())#)
         OR (Publish = 1 AND StartPub >= #CreateODBCDate(Now())# AND EndPub <= #CreateODBCDate(Now())#)
   )
   ORDER BY EID

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[countdrak]

Almost the same error!

Query Of Queries syntax error.
Encountered "StartPub >= {" at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

Argghhhh...

 

[webmigit]

   AND ((Publish = 1 AND StartPub IS NULL
         AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub >= #Now()#
             AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub IS NULL
             AND EndPub <= #Now()#
         OR (Publish = 1 AND StartPub >= #Now()#
             AND EndPub <= #Now()#))

Try that?

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

rather

   AND ((Publish = 1 AND StartPub IS NULL
         AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub >= #Now()#
             AND EndPub IS NULL)
         OR (Publish = 1 AND StartPub IS NULL
             AND EndPub <= #Now()#)
         OR (Publish = 1 AND StartPub >= #Now()#
             AND EndPub <= #Now()#))

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[countdrak]

Nope, same error. Can it be something to do with the Now fucntion not working with a Query. My resultset is another query.

Request.GetContent is actually a query. Anybody?

 

[TruthInSatire]

what about surrounding dates with pound signs?

AND ((Publish = 1 AND StartPub IS NULL
AND EndPub IS NULL)
OR (Publish = 1 AND StartPub >= ###Now()###
AND EndPub IS NULL)
OR (Publish = 1 AND StartPub IS NULL
AND EndPub <= ###Now()###)
OR (Publish = 1 AND StartPub >= ###Now()###
AND EndPub <= ###Now()###))

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[countdrak]

Lexical error !

Maybe I should trying using a diffirent date function. Like DateDiff or DateCompare...hopefully that should do the trick.

Thank a lot everyone. Keep it coming

 

[TruthInSatire]

i wonder if q of q is recognizing the field as a date field. it sounds like it is trying to do a >= comparison with text. maybe put dateFormat() around the fields, something like

AND ((Publish = 1 AND StartPub IS NULL
AND EndPub IS NULL)
OR (Publish = 1 AND dateFormat(StartPub, "mm/dd/yyyy") >= '#Now()#'
AND EndPub IS NULL)
OR (Publish = 1 AND StartPub IS NULL
AND dateFormat(EndPub, "mm/dd/yyyy") <= '#Now()#')
OR (Publish = 1 AND dateFormat(StartPub, "mm/dd/yyyy") >= '#Now()#'
AND dateFormat(EndPub, "mm/dd/yyyy") <= '#Now()#'))

I put the ' back around the #now()# just because.... you can try taking them back out and see if it makes any differance.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[countdrak]

Thanks everybody for helping me out. Finally someone figured it out. Query of Queries doesnt accept complex expression..*sigh*. I guess its a macromedia bug or something.

So I had to use UNION between my SELECT statements and have four seperate select clauses, and it works great now.

Hope this helps someone else too. Thanks everyone.

 

[webmigit]

You get a star for posting the solution, but maybe post the code?

Btw, its not a bug in query of queries, its just that they're very under-developed and very very undocumented.

QofQs serve three good purposes:
. A way to frustrate programmers.
. A way to reorder queries
. A way to filter queries (On one of my sites, I count a certain activity, but I have two types of activity, so its useful for me to say There are X [activities] going on.. But there are X of this type and X of that type.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[countdrak]

LOL...Thanks. This how I fixed it. You can see my original code in my first post.

<cfquery dbtype="query" name="GetAboutUs">
<!--- first condition where startpub and endpub are both null --->
SELECT *
FROM request.getcontent
WHERE Site = 'Public'
AND Fuse = 'About Us'
AND Type = 'M'
AND Publish = 1
AND endpub is null
AND startpub is null
AND Marked=0
UNION
<!--- second condition where startpub is less than or equal to today AND endpub is null --->
SELECT *
FROM request.getcontent
WHERE Site = 'Public'
AND Fuse = 'About Us'
AND Type = 'M'
AND Publish = 1
AND endpub is null
AND startpub <= <cfqueryparam value="#NOW()#" cfsqltype="CF_SQL_DATE">
AND Marked=0
UNION
<!--- third condition where startpub is null and endpub is greater than or equal to today --->
SELECT *
FROM request.getcontent
WHERE Site = 'Public'
AND Fuse = 'About Us'
AND Type = 'M'
AND Publish = 1
AND startpub is null
AND endpub >= <cfqueryparam value="#NOW()#" cfsqltype="CF_SQL_DATE">
AND Marked=0
UNION
<!--- final condition when today falls between and startpub and endpub or is equal to both --->
SELECT *
FROM request.getcontent
WHERE Site = 'Public'
AND Fuse = 'About Us'
AND Type = 'M'
AND Publish = 1
AND startpub <= <cfqueryparam value="#NOW()#" cfsqltype="CF_SQL_DATE">
AND endpub >= <cfqueryparam value="#NOW()#" cfsqltype="CF_SQL_DATE">
AND Marked=0
ORDER BY EID
</cfquery>