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

[Owainnorth]

I'm not sure how to  evaluate the variable when it is retrieved

Have you tried using the evaluate() function?

[Adam Cameron.]

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

[Dan_Bracuk]

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.

[sean69]

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

[Dan_Bracuk · Accepted solution]

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.

[sean69]

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

[sean69]

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?

[Dan_Bracuk]

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.

[sean69]

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

[sean69]

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