5 Replies Latest reply on Dec 14, 2015 9:40 AM by Carl Von Stetten

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

    EwokStud Level 1

      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')#
        • 1. Re: I've an SQL Statement Which works in Access, but not in Coldfusion
          EwokStud Level 1

          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.

          • 2. Re: I've an SQL Statement Which works in Access, but not in Coldfusion
            Carl Von Stetten Adobe Community Professional & MVP

            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.

            • 3. Re: I've an SQL Statement Which works in Access, but not in Coldfusion
              EwokStud Level 1

              #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

              • 4. Re: I've an SQL Statement Which works in Access, but not in Coldfusion
                BKBK Adobe Community Professional & MVP

                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>

                • 5. Re: I've an SQL Statement Which works in Access, but not in Coldfusion
                  Carl Von Stetten Adobe Community Professional & MVP

                  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.