• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

I've an SQL Statement Which works in Access, but not in Coldfusion

New Here ,
Dec 12, 2015 Dec 12, 2015

Copy link to clipboard

Copied

I've an SQL Statement Which works in MS Access 2010, but not in Coldfusion 7.  It's a way to achieve a display of most recent prices for my services, from a table of prices, which includethe date of each new price for each service; there are many services.  I have 2 tables.  the first table lists the services, one record for each service.  The 2nd table lists multiple instances of many of the services from the first table, each instance being a new price.  I do this to keep track of my price history.  The webpage throws an error, stating that: A CFML variable name cannot end with a "." character. SELECT SH1.rpr_srvc_hst_dt, rpr_srvcs.[rpr_srvc], SH1.[rpr_srvc_hst_amt_] FROM rpr_srvc_hst AS SH1 INNER JOIN rpr_srvcs ON SH1.[rpr_srvc_hst_srvc_] = rpr_srvcs.[rpr_srvcID] WHERE (((SH1.[rpr_srvc_hst_dt])=(SELECT MAX(rpr_srvc_hst_dt)     FROM rpr_srvc_hst AS SH2     WHERE SH2.[rpr_srvc_hst_srvc_] = SH1.[rpr_srvc_hst_srvc_]))); and the code on the page body:

#rpr_srvc##NumberFormat(SH1.[rpr_srvc_hst_amt_],'0.00')##DateFormat(SH1.rpr_srvc_hst_dt,'M/D/YY')#

Views

282

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 12, 2015 Dec 12, 2015

Copy link to clipboard

Copied

Well, apparently the [] brackets cannot be used, but now, I am wondering how to use tablename.fieldname, because the following works:#NumberFormat(rpr_srvc_hst_amt_,'0.00')#  but#NumberFormat(SH1.rpr_srvc_hst_amt_,'0.00')#doesn't work.  Please help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 12, 2015 Dec 12, 2015

Copy link to clipboard

Copied

I think ColdFusion drops the table prefixes once it has a recordset returned.  So if, in your query, you reference columns from multiple tables that have the same name (like table1.column1 and table2.column1), you need to use aliases to distinguish the two after ColdFusion has received the recordset back (so, maybe tbl1_column1 and tbl2_column1, etc.).

Then, you should be able to reference your query columns in CFML statements as queryname.columname as long as you use any aliases you defined in your query (like myquery.tbl1_column1).

I'm shooting in the dark, because I'm not sure if the error you are getting is pointing at the query itself or when you try to use the query results elsewhere in you page.  Please provide some code excerpts and specifically identify the line that is referenced in the error.

HTH,

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 12, 2015 Dec 12, 2015

Copy link to clipboard

Copied

#DollarFormat(SH1.rpr_srvc_hst_amt_)#was changed to#DollarFormat(rpr_srvc_hst_amt_)#I removed the tablename SH1 prefix and the error went away.  The fieldname rpr_srvc_hst_amt_ didn't need a prefix, because SH1 is the same table as rpr_srvc_hst, and therefore the data is the same.  I'm just interested in knowing why CF didn't like the prefix.  Very odd! Incidentally, I would like to display the dollar figures without the dollar signs.  When I do, CF rounds out the decimals automatically.  $59.99  I would like to read 59.99 and not 60.00

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 13, 2015 Dec 13, 2015

Copy link to clipboard

Copied

EwokStud,

What you observe is the expected behaviour. Consider this example:

<cfquery name="myQ">

SELECT col1, col2

FROM myTBL

</cfquery>

The variables col1 and col2 are undefined outside the context of the query myQ. Hence,

myQ.col1 and myQ.col2 exist

<cfloop query="myQ"><!--- col1 and col2 exist---></cfloop>

<cfoutput query="myQ"><!--- col1 and col2 exist---></cfoutput>

So let us name your query as follows:

<cfquery name="EwokStudQ">

SELECT SH1.rpr_srvc_hst_dt, rpr_srvcs.rpr_srvc, SH1.rpr_srvc_hst_amt_

FROM rpr_srvc_hst AS SH1

INNER JOIN rpr_srvcs

ON SH1.rpr_srvc_hst_srvc_ = rpr_srvcs.rpr_srvcID

WHERE (((SH1.rpr_srvc_hst_dt)=

                                 (SELECT MAX(rpr_srvc_hst_dt)    

                                  FROM rpr_srvc_hst AS SH2    

                                  WHERE SH2.rpr_srvc_hst_srvc_ = SH1.rpr_srvc_hst_srvc_)

               )

             );

</cfquery>

Then,

EwokStudQ.rpr_srvc_hst_dt, EwokStudQ.rpr_srvc, EwokStudQ.rpr_srvc_hst_amt_ exist;

<cfloop query="myQ"><!--- rpr_srvc_hst_dt, rpr_srvc, and rpr_srvc_hst_amt_ exist---></cfloop>

<cfoutput query="myQ"><!--- rpr_srvc_hst_dt, rpr_srvc, and rpr_srvc_hst_amt_ exist---></cfoutput>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 14, 2015 Dec 14, 2015

Copy link to clipboard

Copied

LATEST

Ewokstud,


What BKBK said. Also, "SH1" is not a prefix - it's a table name alias that you set within your SQL query.  If you ran the same query inside Access, the results grid does not show the table names/aliases as prefixes to columns (even if there is a column name collision between same-named columns in multiple tables - I just checked this in Access 2010).  So as BKBK said, this is expected behavior.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation