10 Replies Latest reply on Mar 17, 2011 12:49 PM by sean69

    Storing variables names in a database

    sean69

      Hi;

       

      I'm storing some calculations in a database  table and need to retrieve them and run them in a query, one of them  needs to access the value of an argument passed to the function  performing the query/calculation. Problem is I'm not sure how to  evaluate the variable when it is retrieved - it's not getting processed.

       

      Any ideas?

       

      here is the functon:

       

      <cffunction
       name="fnShowliveSurcharge" access="public" output="true" 
      returntype="any" hint="makes a calculation based on a formulae" >
      
              <cfargument name="avgCost" required="true" />
      
          
      
              <cfquery name="qryGetAlloys" datasource="#variables.dsn#" result="qryGetAlloys_results" >
      
                  select * from nas_alloys;
      
              </cfquery>
      
              
      
              <cfquery name="qryGetGrades" datasource="#variables.dsn#" result="qryGetGrades_results" >
      
                  select * from nas_grades order by id;
      
              </cfquery>
      
              
      
              <cfloop query="qryGetGrades">
      
                  <cfloop query="qryGetAlloys">
      
                      <cfquery name="qryGetSurcharge" datasource="#variables.dsn#" result="qryGetSurcharge_results">    
      
                          select 
      
                              #qryGetAlloys.formulae# as myValue
      
                          from nas_alloys a left join nas_triggers t on t.alloyid = a.id
      
                          left join nas_astm astm on astm.alloyid = a.id
      
                          left join nas_estimatedprice ep on ep.alloyid = a.id
      
                          where astm.gradeid = '#qryGetGrades.Id#'
      
                          and a.id = '#qryGetAlloys.Id#';
      
                      </cfquery>
      
                  </cfloop>
      
              </cfloop>
      
              
      
          </cffunction>
      

       

       

      the second line of the query is inserting

       

      ((#arguments.avgCost# - t.value) * (astm.astm/100) * 1.2)

       

       

      from the nas_alloys table, however the query ends up looking like;

       

      select ((#arguments.avgCost# - t.value) * (astm.astm/100) * 1.2) as myValue from nas_alloys a left join nas_triggers t on t.alloyid = a.id left join nas_astm astm on astm.alloyid = a.id left join nas_estimatedprice ep on ep.alloyid = a.id where astm.gradeid = '1' and a.id = '1';                    

       

      instead of:

       

      select ((2.2289 - t.value) * (astm.astm/100) * 1.2) as myValue from nas_alloys a left join nas_triggers t on t.alloyid = a.id left join nas_astm astm on astm.alloyid = a.id left join nas_estimatedprice ep on ep.alloyid = a.id where astm.gradeid = '1' and a.id = '1';                    

       

       

      Any thoughts on how to correct this?

       

      -thanks

      -sean

        • 1. Re: Storing variables names in a database
          Owain North Level 4
          I'm not sure how to  evaluate the variable when it is retrieved

           

          Have you tried using the evaluate() function?

          • 2. Re: Storing variables names in a database
            Adam Cameron. Level 5

            A rule of thumb that I have is if I find myself looping over one query to get joining info for another query embedded with the loop: I am not thinking things through properly.  And to have the inner CFQUERY within two nested query loops?  Yikes.  Other than the variation of formula here, isn't all that doable with one query?  Even if not, all you're using CF for here is manipulating data, which is the job of the DB, not CF.  CF is for taking the resultant data from the DB and using it to generate text to return to the web server (you know: making web pages).

             

            Speaking of the formulas... how many have you got?  Rather than storing your logic as data, I'd be storing it as... well... logic.  Either have a suite of procs, or one proc with a switch based on (whatever) which calls in a different formula as is required.  All CF should be doing is calling a proc.

             

            --

            Adam

            1 person found this helpful
            • 3. Re: Storing variables names in a database
              Dan Bracuk Level 5

              Adam gave you good advice.  I am merely going to answer your specific question.

               

              If you are storing variable names in a db, and you want coldfusion to process them, you have to do this.

              1.  run your query

              2.  create a coldfusin file containing the query results

              3.  cfinclude the file.

               

              I don't think Owain's suggestion will work.

              1 person found this helpful
              • 4. Re: Storing variables names in a database
                sean69 Level 1

                Hi guys;

                 

                thanks, yes Adam is technically correct, though a query like that might be a little beyond me... can you even do conditional sql in mysql? [i.e. if(table.id = '1'}( (select * from anothertable))}else{(select avg(somethingelse) from yetAnotherTable))} ] ??

                 

                one of the problems with the variable is that ~sometimes~ it needs to be the average of a select from another table.

                 

                select avg(cost/2204.6) as averageCost from nas_cost 
                where cost != '0' and date >= '#variables.firstOfMonth#' 
                and date <= '#variables.lastOfMonth#' order by date;
                

                 

                - there are currently 6 or 7 different calculations [one for each alloy]

                - the calculations can change from time to time dependent on the alloy composition and quality [wonderful!]

                 

                 

                hmmm guess I have some mysql docs to run through.

                 

                -sean

                • 5. Re: Storing variables names in a database
                  Dan Bracuk Level 5

                  Conditional logic can be done in sql a number of ways.  One way that works with just about anything is a case construct.

                   

                  select case when something then this

                  when something else then that

                  else whatever

                  end fieldname.

                   

                  Then there are functions like ifnull, coalesce, decode, etc.  These are db specific.  I don't use MySql so I can't tell you what functions it has.

                  • 6. Re: Storing variables names in a database
                    sean69 Level 1

                    Hi Dan;

                     

                    thanks, I was just looking at my options, there appears to be some pretty good 'case' construct documentation for mysql...  also did some minor testing... I can't create stored proceedures on the host - so that's out.

                     

                    damn.

                     

                    -sean

                    • 7. Re: Storing variables names in a database
                      sean69 Level 1

                      Trying to do some 'case' tests here, problems with syntax I think, [the other problem is the mysql docs on case degenerate into IF statement examples...! ]

                       

                      select a.* 
                           CASE 
                                WHEN a.id = '1' THEN from nas_alloys a
                                ELSE ,g.* from nas_alloys a inner join nas_grades g
                           END
                      ; 
                      
                      
                      select 
                           CASE a.id
                                WHEN '1' THEN (a.* from nas_alloys a)
                                ELSE (a.*, g.* from nas_alloys a inner join nas_grades g)
                           END
                      
                      order by a.id ; 
                      

                       

                       

                      any thoughts?

                      • 8. Re: Storing variables names in a database
                        Dan Bracuk Level 5

                        The first syntax error is a missing comma.

                         

                        The next one is attempting to do a join in your select clause instead of your from or where clause.

                        • 9. Re: Storing variables names in a database
                          sean69 Level 1

                          Dan;

                           

                          I don't kjnow about the sql syntax - if you take the first case:

                           

                          select a.* 
                               CASE 
                                    WHEN a.id = '1' THEN from nas_alloys a
                                    ELSE ,g.* from nas_alloys a inner join nas_grades g
                               END
                          ; 
                          

                           

                           

                          Both queries run fine if I remove the CASE/THEN/ELSE stuff...

                           

                          select a.* from nas_alloys a ;
                          select a.*,g.* from nas_alloys a inner join nas_grades g; 
                          

                           

                          ?

                           

                          -sean

                          • 10. Re: Storing variables names in a database
                            sean69 Level 1

                            ok - getting somewhere here, I don't think the CASE type will do what I expected it to do, I started working on an IF type SQL statement instead,

                             

                            If I do this:

                             

                            select a.id,a.alloyname,a.label,a.symbol, g.grade,
                                 if(a.id = 1,(
                                      (((
                                      select avg(cost/2204.6) as averageCost from nas_cost 
                                      where cost != '0' and `date` >= '2011-03-01' 
                                      and `date` <= '2011-03-31') - t.value) * (astm.astm/100) * 1.2)
                                 ),a.formulae)
                                 as thisValue
                            
                            from nas_alloys a 
                            left join nas_triggers t on t.alloyid = a.id
                            left join nas_astm astm on astm.alloyid = a.id
                            left join nas_estimatedprice ep on ep.alloyid = a.id
                            left join nas_grades g on g.id = astm.gradeid
                            where a.id = '1' or a.id = '2'
                            order by g.grade;
                            

                             

                             

                            I get the correct recordset back [HOORAY!]

                             

                            id     alloyname     label          symbol     grade          thisValue
                            "1"     "Nickel"     "Nickel"     "Ni"     "201"          "0.502835162841"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "201"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "301/301L"     "0.754252744262"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "301/301L"     "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "304/304L"     "1.005670325683"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "304/304L"     "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "309"          "1.508505488524"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "309"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "310"          "2.388467023496"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "310"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "316L"          "1.257087907103"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "316L"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "321"          "1.131379116393"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "321"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "409"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "409"          "0.000000000000"
                            "2"     "FerroCr"     "Chromium"     "Cr"     "430"          "((ep.estPrice - t.value) * (astm.astm/100) * 1.2)"
                            "1"     "Nickel"     "Nickel"     "Ni"     "430"          "0.000000000000"
                            
                             
                            

                             

                             

                            The Nickel values are all being calculated correctly, HOWEVER, Chromium is still showing the calculation stored in the nas_alloys table.

                            Any ideas on how to get MySQL to evaluate that field?

                             

                            [then I somehow need to get it to SUM each set of grades!]

                             

                            -thanks

                            -sean