Copy link to clipboard
Copied
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
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.
Copy link to clipboard
Copied
I'm not sure how to evaluate the variable when it is retrieved
Have you tried using the evaluate() function?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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