Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
You learn something new every day - awesome, Thanks Chris! As always, please contact me if I can be of any futher assistance. Gavin Beau Baumanis Senior Application Developer PalCare Pty. Ltd. P: +61 -3 9380 3513 M: +61 -438 545 586 E: b...@palcare.com.au W: http://palcare.com.au On 04/10/2010, at 4:22 PM, christophe albrech wrote: Hi Scott, If I may, your code could be improved performance-wise. It is a classic example of Rbar programming (pronounced reebar = Row by agonizing row) as the process is not written in a set-based way, which is exactly what DB servers are really good (and really fast) at. If you think about it, the code loops over each record in the first query (the cfif is useless by the way) , and then does an update for each one. if you have a thousand records, that's a thousand loops, a thousand queries sent through the wire, a thousand query the optimizer has to parse, and so on. What you really want to do is have a single update statement instead. The problem in your case is that using aggregation forces you to use a group by statement. On top of that you need the aggregated data to perform the set operation which complicates the matter. While writing one single statement is possible it's probably easier to read if you use a temp table to store the maxed date and then write a single update. like so: cfquery SELECT sb_invoice.sb_invoice_id, MAX(sb_claim_date) AS sb_claim_date INTO##tempTable FROMsb_invoice INNER JOIN sb_invoice_item ON sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id INNER JOIN sb_item_claim ON sb_invoice_item.sb_invoice_item_id = sb_item_claim.sb_invoice_item_id WHERE sb_invoice.sb_bill_mech_code IN ( 'M', 'F', 'AG', 'SC', 'MB', 'MO' ) AND sb_invoice_item.sb_item_status_code IN ( '21', '24', '31' ) GROUP BY sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date HAVING sb_invoice.sb_reminder_date DATEADD(d, 30, MAX(sb_claim_date)) UPDATE i SET sb_reminder_date = DATEADD(d, 31, t.sb_claim_date) FROMsb_invoice i INNER JOIN #tempTable t ON i.sb_invoice_id = t.sb_invoice_id /cfquery The more records the initial select returns, the more resources you save. I just tried a simple example on my local machine where I tried to update 10k rows with both methods. the rbar one took about 10 seconds while the set-based one took less than half a second. trying with 100k rows, I got 70 seconds against 2.5. Let's keep in mind that lots of updates wouldn't need the temp table in the first place. It's only there because because you were updating with an aggregated value. Normally you'd just do: UPDATE t set blah = 6 FROM myTable t JOIN otherTable o on o.id = t.fk ... ... Anyway, speaking of agonizing stuff, I'm off to see the dentist. Cheers, Tof On Fri, Oct 1, 2010 at 11:30 AM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hello, Thanks for all replies. An update of my local dev server to 9,0,1,274733 appears to have solved the problem. From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Scott Thornton Sent: Friday, 1 October 2010 10:29 AM To: cfaussie@googlegroups.com Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query HI All, 1. What happens if you try CFDUMP instead right after the query? using cfdump var=#fix_this#cfabort returns Variable FIX_THIS is undefined. 2. re recordcount gt 0. I didn't know that, thanks. 3. Why the cfoutput? The original code used cfloop, I tried using cfoutput as well. Typically cfloop would be used. 4. Update CF? Will try that now. 5. Does the query run (as is)? Yes it does, however it does not return any rows ( in SQl Query Analyzer 2000). The database is MS SQL 2005 5. Much simplier query? cfquery datasource=#DSN# name=fix_this select top 1 * from sb_invoice /cfquery cfdump var=#fix_this#cfabort works fine. Sooo, I will update my local server first, then put the orginal query back in and see how it goes. From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of m...@ampersand.net.au Sent: Friday, 1 October 2010 9:41 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query What happens if you try CFDUMP instead right after the query? On 1/10/2010 9:35 AM, Scott Thornton wrote: Hi, Running across a odd problem in CF 9,0,0,251028. development server. I was just running a process that usually works and has not been modified for quite a long time. part of that process runs a query and then loops over the result set. Simple enough… however the query does not seem to exist after it has run. eg
Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Hi Scott, If I may, your code could be improved performance-wise. It is a classic example of Rbar programming (pronounced reebar = Row by agonizing row) as the process is not written in a set-based way, which is exactly what DB servers are really good (and really fast) at. If you think about it, the code loops over each record in the first query (the cfif is useless by the way) , and then does an update for each one. if you have a thousand records, that's a thousand loops, a thousand queries sent through the wire, a thousand query the optimizer has to parse, and so on. What you really want to do is have a single update statement instead. The problem in your case is that using aggregation forces you to use a group by statement. On top of that you need the aggregated data to perform the set operation which complicates the matter. While writing one single statement is possible it's probably easier to read if you use a temp table to store the maxed date and then write a single update. like so: cfquery SELECT sb_invoice.sb_invoice_id, MAX(sb_claim_date) AS sb_claim_date INTO##tempTable FROMsb_invoice INNER JOIN sb_invoice_item ON sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id INNER JOIN sb_item_claim ON sb_invoice_item.sb_invoice_item_id = sb_item_claim.sb_invoice_item_id WHERE sb_invoice.sb_bill_mech_code IN ( 'M', 'F', 'AG', 'SC', 'MB', 'MO' ) AND sb_invoice_item.sb_item_status_code IN ( '21', '24', '31' ) GROUP BY sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date HAVING sb_invoice.sb_reminder_date DATEADD(d, 30, MAX(sb_claim_date)) UPDATE i SET sb_reminder_date = DATEADD(d, 31, t.sb_claim_date) FROMsb_invoice i INNER JOIN #tempTable t ON i.sb_invoice_id = t.sb_invoice_id /cfquery The more records the initial select returns, the more resources you save. I just tried a simple example on my local machine where I tried to update 10k rows with both methods. the rbar one took about 10 seconds while the set-based one took less than half a second. trying with 100k rows, I got 70 seconds against 2.5. Let's keep in mind that lots of updates wouldn't need the temp table in the first place. It's only there because because you were updating with an aggregated value. Normally you'd just do: UPDATE t set blah = 6 FROM myTable t JOIN otherTable o on o.id = t.fk ... ... Anyway, speaking of agonizing stuff, I'm off to see the dentist. Cheers, Tof On Fri, Oct 1, 2010 at 11:30 AM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hello, Thanks for all replies. An update of my local dev server to 9,0,1,274733 appears to have solved the problem. *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On Behalf Of *Scott Thornton *Sent:* Friday, 1 October 2010 10:29 AM *To:* cfaussie@googlegroups.com *Subject:* RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query HI All, 1. What happens if you try CFDUMP instead right after the query? using *cfdump var=#fix_this#cfabort* * * *returns * Variable FIX_THIS is undefined. 2. re recordcount gt 0. I didn't know that, thanks. 3. Why the cfoutput? The original code used cfloop, I tried using cfoutput as well. Typically cfloop would be used. 4. Update CF? Will try that now. 5. Does the query run (as is)? Yes it does, however it does not return any rows ( in SQl Query Analyzer 2000). The database is MS SQL 2005 5. Much simplier query? cfquery datasource=#DSN# name=fix_this select top 1 * from sb_invoice /cfquery cfdump var=#fix_this#cfabort works fine. Sooo, I will update my local server first, then put the orginal query back in and see how it goes. *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On Behalf Of *...@ampersand.net.au *Sent:* Friday, 1 October 2010 9:41 AM *To:* cfaussie@googlegroups.com *Subject:* Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query What happens if you try CFDUMP instead right after the query? On 1/10/2010 9:35 AM, Scott Thornton wrote: Hi, Running across a odd problem in CF 9,0,0,251028. development server. I was just running a process that usually works and has not been modified for quite a long time. part of that process runs a query and then loops over the result set. Simple enough… however the query does not seem to exist after it has run. eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS. cfquery datasource=#DSN# name=fix_this select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date from sb_invoice inner join sb_invoice_item on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id inner join sb_item_claim
[cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified for
quite a long time. part of that process runs a query and then loops over the
result set. Simple enough... however the query does not seem to exist after it
has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date,
max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that helped,
to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
What happens if you try CFDUMP instead right after
the query?
On 1/10/2010 9:35 AM, Scott Thornton wrote:
Hi,
Running across a odd problem in CF
9,0,0,251028. development
server.
I was just running a process that usually
works and has not
been modified for quite a long time. part of that process runs
a query and then
loops over the result set. Simple enough however the query
does not seem
to exist after it has run.
eg:
The
following code returns Element
RECORDCOUNT is
undefined in FIX_THIS.
cfquery datasource="#DSN#"
name="fix_this"
select
sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date, max(sb_claim_date) as
sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id
=
sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on
sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where
sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and
sb_invoice_item.sb_item_status_code in
('21','24','31')
group by
sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
having
sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput
query="fix_this"
cfquery
name="upd_fund_reminder" datasource="#DSN#"
update
sb_invoice
set
sb_reminder_date = cfqueryparam cfsqltype="cf_sql_varchar"
value="#DateFormat(DateAdd("d",31,fix_this.sb_claim_date),"dd-mmm-")#"
where
sb_invoice_id = cfqueryparam cfsqltype="cf_sql_numeric"
value="#fix_this.sb_invoice_id#"
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a
few different
values, I have used cfloop instead of cfoutput, I added the
cfif.recordcount to
see if that helped, to no avail. Without the CFIF the error
was :
Attribute validation error for tag
cfoutput.
The value of the attribute query, which is
currently fix_this, is invalid.
Debug output shows that the query executed
in 922ms and
returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google
Groups "cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Not sure why you are getting that error, but there are a few things I will
ask why? Mainly for others who might be newer to ColdFusion and reading
this.
This
cfif fix_this.recordcount gt 0
Can be written
cfif fix_this.recordcount
Any number above zero will always return true.
Also this
cfoutput query=fix_this
You are not actually outputting anything, so why are you not using a cfloop?
And lastly, I think you might need to upgrade to CF9.01 with all the latest
hotfixes just in case your issue was fixed in the 9.01 or the patch just
after the 9.01 was released.
Regards,
Andrew Scott
http://www.andyscott.id.au/
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Scott Thornton
Sent: Friday, 1 October 2010 9:36 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified
for quite a long time. part of that process runs a query and then loops over
the result set. Simple enough. however the query does not seem to exist
after it has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that
helped, to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
I had this exact same problem a few months ago. Unfortunately I can't
remember exactly what the problem was but I do remember it was a smack
myself in the head kind of answer. I have a feeling that even though the
query was appearing to run OK, there was actually an issue with it that
meant it wasn't available for use. If you haven't already, double check the
query in SQLServer or try a much simpler query with the same name and then
build it back up to its current state one piece at a time. If I remember
exactly what it was I'll reply again.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Scott Thornton
Sent: Friday, 1 October 2010 9:36 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified
for quite a long time. part of that process runs a query and then loops over
the result set. Simple enough. however the query does not seem to exist
after it has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that
helped, to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
#DateAdd(d,30,max(sb_claim_date))#
From: cas...@bluerocksoftware.com
To: cfaussie@googlegroups.com
Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
Date: Fri, 1 Oct 2010 09:53:07 +1000
I had this exact same problem a
few months ago. Unfortunately I can’t remember exactly what the
problem was but I do remember it was a smack myself in the head kind of answer.
I have a feeling that even though the query was appearing to run OK, there was
actually an issue with it that meant it wasn’t available for use.
If you haven’t already, double check the query in SQLServer or try a much
simpler query with the same name and then build it back up to its current state
one piece at a time. If I remember exactly what it was I’ll reply
again.
From: cfaussie@googlegroups.com
[mailto:cfaus...@googlegroups.com] On Behalf Of Scott Thornton
Sent: Friday, 1 October 2010 9:36 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Strange problem - Element RECORDCOUNT is undefined
in query
Hi,
Running across a odd problem in CF 9,0,0,251028. development
server.
I was just running a process that usually works and has not been
modified for quite a long time. part of that process runs a query and then
loops over the result set. Simple enough… however the query does not seem
to exist after it has run.
eg:
The following code returns Element RECORDCOUNT is
undefined in FIX_THIS.
cfquery datasource=#DSN#
name=fix_this
select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date,
max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id = sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in ('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder datasource=#DSN#
update sb_invoice
set sb_reminder_date = cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id = cfqueryparam cfsqltype=cf_sql_numeric
value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different
values, I have used cfloop instead of cfoutput, I added the cfif.recordcount to
see if that helped, to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this,
is invalid.
Debug output shows that the query executed in 922ms and
returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
There is a SQLServer DateAdd function that I think it is using
http://www.w3schools.com/sql/func_dateadd.asp
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Mark Ireland
Sent: Friday, 1 October 2010 10:16 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
#DateAdd(d,30,max(sb_claim_date))#
_
From: cas...@bluerocksoftware.com
To: cfaussie@googlegroups.com
Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined
in query
Date: Fri, 1 Oct 2010 09:53:07 +1000
I had this exact same problem a few months ago. Unfortunately I can't
remember exactly what the problem was but I do remember it was a smack
myself in the head kind of answer. I have a feeling that even though the
query was appearing to run OK, there was actually an issue with it that
meant it wasn't available for use. If you haven't already, double check the
query in SQLServer or try a much simpler query with the same name and then
build it back up to its current state one piece at a time. If I remember
exactly what it was I'll reply again.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Scott Thornton
Sent: Friday, 1 October 2010 9:36 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified
for quite a long time. part of that process runs a query and then loops over
the result set. Simple enough. however the query does not seem to exist
after it has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that
helped, to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http
Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Maybe you also have fix_this defined in a different scope?
B)
On 1/10/2010 7:35 AM, Scott Thornton wrote:
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified for
quite a long time. part of that process runs a query and then loops over the
result set. Simple enough… however the query does not seem to exist after it
has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date,
max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder datasource=#DSN#
update sb_invoice
set sb_reminder_date = cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id = cfqueryparam
cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that helped,
to no avail. Without the CFIF the error was :
*Attribute validation error for tag cfoutput. *
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.856 / Virus Database: 271.1.1/3167 - Release Date: 09/30/10
03:50:00
--
Brett Payne-Rhodes
YourSite Web Solutions
w: http://www.yoursite.net.au
e: br...@ehc.net.au
t: +61 (0)8 9371-0471
m: +61 (0)414 371 047
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
HI All,
1. What happens if you try CFDUMP instead right after the query?
using
cfdump var=#fix_this#cfabort
returns
Variable FIX_THIS is undefined.
2. re recordcount gt 0.
I didn't know that, thanks.
3. Why the cfoutput?
The original code used cfloop, I tried using cfoutput as well. Typically cfloop
would be used.
4. Update CF?
Will try that now.
5. Does the query run (as is)?
Yes it does, however it does not return any rows ( in SQl Query Analyzer 2000).
The database is MS SQL 2005
5. Much simplier query?
cfquery datasource=#DSN# name=fix_this
select top 1 * from sb_invoice
/cfquery
cfdump var=#fix_this#cfabort
works fine.
Sooo, I will update my local server first, then put the orginal query back in
and see how it goes.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of
m...@ampersand.net.au
Sent: Friday, 1 October 2010 9:41 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
What happens if you try CFDUMP instead right after the query?
On 1/10/2010 9:35 AM, Scott Thornton wrote:
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified for
quite a long time. part of that process runs a query and then loops over the
result set. Simple enough… however the query does not seem to exist after it
has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date,
max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that helped,
to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to
cfaussie@googlegroups.commailto:cfaussie@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.commailto:cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
If a simple query works i would be taking stuff out stripping stuff out of
the query and seeing what is making it not return anything and i would be
starting with the HAVING clause. It is possible that there is something in
there that is making it not return anything.
Out of curiosity, what happens if you add the result attribute into the
query and dump the result value? What do you get?
From: Scott Thornton [mailto:scott.thorn...@hnehealth.nsw.gov.au]
Sent: Friday, 1 October 2010 10:29 AM
To: cfaussie@googlegroups.com
Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined
in query
HI All,
1. What happens if you try CFDUMP instead right after the query?
using
cfdump var=#fix_this#cfabort
returns
Variable FIX_THIS is undefined.
2. re recordcount gt 0.
I didn't know that, thanks.
3. Why the cfoutput?
The original code used cfloop, I tried using cfoutput as well. Typically
cfloop would be used.
4. Update CF?
Will try that now.
5. Does the query run (as is)?
Yes it does, however it does not return any rows ( in SQl Query Analyzer
2000). The database is MS SQL 2005
5. Much simplier query?
cfquery datasource=#DSN# name=fix_this
select top 1 * from sb_invoice
/cfquery
cfdump var=#fix_this#cfabort
works fine.
Sooo, I will update my local server first, then put the orginal query back
in and see how it goes.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of m...@ampersand.net.au
Sent: Friday, 1 October 2010 9:41 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined
in query
What happens if you try CFDUMP instead right after the query?
On 1/10/2010 9:35 AM, Scott Thornton wrote:
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified
for quite a long time. part of that process runs a query and then loops over
the result set. Simple enough. however the query does not seem to exist
after it has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that
helped, to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie
RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in query
Hello,
Thanks for all replies.
An update of my local dev server to 9,0,1,274733 appears to have solved the
problem.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of
Scott Thornton
Sent: Friday, 1 October 2010 10:29 AM
To: cfaussie@googlegroups.com
Subject: RE: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
HI All,
1. What happens if you try CFDUMP instead right after the query?
using
cfdump var=#fix_this#cfabort
returns
Variable FIX_THIS is undefined.
2. re recordcount gt 0.
I didn't know that, thanks.
3. Why the cfoutput?
The original code used cfloop, I tried using cfoutput as well. Typically cfloop
would be used.
4. Update CF?
Will try that now.
5. Does the query run (as is)?
Yes it does, however it does not return any rows ( in SQl Query Analyzer 2000).
The database is MS SQL 2005
5. Much simplier query?
cfquery datasource=#DSN# name=fix_this
select top 1 * from sb_invoice
/cfquery
cfdump var=#fix_this#cfabort
works fine.
Sooo, I will update my local server first, then put the orginal query back in
and see how it goes.
From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of
m...@ampersand.net.au
Sent: Friday, 1 October 2010 9:41 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] Strange problem - Element RECORDCOUNT is undefined in
query
What happens if you try CFDUMP instead right after the query?
On 1/10/2010 9:35 AM, Scott Thornton wrote:
Hi,
Running across a odd problem in CF 9,0,0,251028. development server.
I was just running a process that usually works and has not been modified for
quite a long time. part of that process runs a query and then loops over the
result set. Simple enough… however the query does not seem to exist after it
has run.
eg: The following code returns Element RECORDCOUNT is undefined in FIX_THIS.
cfquery datasource=#DSN# name=fix_this
select sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date,
max(sb_claim_date) as sb_claim_date
from sb_invoice
inner join sb_invoice_item
on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
inner join sb_item_claim
on sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
where sb_invoice.sb_bill_mech_code in
('M','F','AG','SC','MB','MO')
and sb_invoice_item.sb_item_status_code in ('21','24','31')
group by sb_invoice.sb_invoice_id, sb_invoice.sb_reminder_date
having sb_invoice.sb_reminder_date
DateAdd(d,30,max(sb_claim_date))
/cfquery
cfif fix_this.recordcount gt 0
cfoutput query=fix_this
cfquery name=upd_fund_reminder
datasource=#DSN#
update sb_invoice
set sb_reminder_date =
cfqueryparam cfsqltype=cf_sql_varchar
value=#DateFormat(DateAdd(d,31,fix_this.sb_claim_date),dd-mmm-)#
where sb_invoice_id =
cfqueryparam cfsqltype=cf_sql_numeric value=#fix_this.sb_invoice_id#
/cfquery
/cfoutput
/cfif
I have changed the name of the query to a few different values, I have used
cfloop instead of cfoutput, I added the cfif.recordcount to see if that helped,
to no avail. Without the CFIF the error was :
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently fix_this, is invalid.
Debug output shows that the query executed in 922ms and returned no rows.
What am I missing?
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to
cfaussie@googlegroups.commailto:cfaussie@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.commailto:cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
--
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email
