Re: Date list using SQL IN operator

[Keith Levenson]

Ok, I figured out I should use ListQualify on the list to add the single quotes and preservesinglequotes within the query to preserve the quotes for revised code of:

cfoutput query=checkForEmp21Overlap
cfset overlapRecordCount=
	ListAppend(overlapRecordCount,DateFormat(myStart,mm/dd/))			
cfif DateFormat(myStart,mm/dd/) gte DateFormat(finalDay,mm/dd/)
	cfbreak
/cfif	
			/cfoutput
cfset overlapRecordCount = ListQualify(overlapRecordCount,',,,CHAR) 

cfquery name=megaquery_output_recordspecific_dateRestricted dbtype=query
		SELECT * 
		FROM megaquery_output_recordspecific
		WHERE (myStart BETWEEN
		cfqueryparam cfsqltype=CF_SQL_TIMESTAMP value=#formattedBeginDay# and
		cfqueryparam cfsqltype=CF_SQL_TIMESTAMP value=#formattedEndDay#)
		cfif listlen(overlapRecordCount) gt 0
		AND (myStart NOT IN (#preserveSingleQuotes(overlapRecordCount)#))
		/cfif
		ORDER BY myStart;
	/cfquery

Now the problem is I get a Query Of Queries runtime error.
Unsupported type comparison.

I tried using cfqueryparam to set the type to date, but it doesn't seem to be compatible with lists.

Any new suggestions?

Thanks!
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: Adding elements to a structure

[Keith Levenson]

Thanks, Pascal.Your assumption was correct, and works great!
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

QofQ syntax

[Keith Levenson]

I'm trying to do a query of queries like so:

68: cfquery name=getData dbtype=query
69: SELECT * from myQuery
70: WHERE myStart BETWEEN
71: (cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72: cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#)
73: AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.WScdDate),0,0,1)#
74: /cfquery

***Here's the error I get:

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

The error occurred in [this file]: line 73

71 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#
73 :AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.skedDate),0,0,1)#
74 :/cfquery

***Here is the query its trying to do:

SELECT * from myQuery WHERE myStart BETWEEN (param 1) and (param 2) AND
myStart = {ts '2003-08-01 00:00:01'}

The variable myStart is in the {ts '2003-08-01 00:00:01'} format earlier in
the code.

Is it that I can't perform this kind of comparision in QofQ?  Should I
convert the date using datediff or some other function?

Thanks!

/Keith



~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

QofQ syntax

[Keith Levenson]

I'm trying to do a query of queries like so:

68: cfquery name=getData dbtype=query
69: SELECT * from myQuery
70: WHERE myStart BETWEEN
71: (cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72: cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#)
73: AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.WScdDate),0,0,1)#
74: /cfquery

***Here's the error I get:

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

The error occurred in [this file]: line 73

71 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#
73 :AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.skedDate),0,0,1)#
74 :/cfquery

***Here is the query its trying to do:

SELECT * from myQuery WHERE myStart BETWEEN (param 1) and (param 2) AND
myStart = {ts '2003-08-01 00:00:01'}

The variable myStart is in the {ts '2003-08-01 00:00:01'} format earlier in
the code.

Is it that I can't perform this kind of comparision in QofQ?  Should I
convert the date using datediff or some other function?

Thanks!

/Keith



~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Get the mailserver that powers this list at 
http://www.coolfusion.com

QofQ syntax

[Keith Levenson]

I'm trying to do a query of queries like so:

68: cfquery name=getData dbtype=query
69: SELECT * from myQuery
70: WHERE myStart BETWEEN
71: (cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72: cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#)
73: AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.WScdDate),0,0,1)#
74: /cfquery

***Here's the error I get:

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

The error occurred in [this file]: line 73

71 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount-1,startDate)# and
72 :cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#DateAdd('D',iCount,startDate)#
73 :AND myStart =
#createDateTime(Year(getEarlierData.skedDate),Month(getEarlierData.skedDate)
,Day(getEarlierData.skedDate),0,0,1)#
74 :/cfquery

***Here is the query its trying to do:

SELECT * from myQuery WHERE myStart BETWEEN (param 1) and (param 2) AND
myStart = {ts '2003-08-01 00:00:01'}

The variable myStart is in the {ts '2003-08-01 00:00:01'} format earlier in
the code.

Is it that I can't perform this kind of comparision in QofQ?  Should I
convert the date using datediff or some other function?

Thanks!

/Keith


~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm