asked on
using queries and conditions in cfscript
Good morning, Im trying to get some help with using a CFLOOP statement or some other way within a cfscript statement, while building spreadsheets using Coldfusion. I have a query inside of the cfscript, and I call that query and get the results that I am looking for when I put a field in the where statement called FY...so give me all of the records where FY = FY21. However, what Im trying to do now is I want to remove the where fy statement in the query, and then use some sort of if statement to build the spreadsheet so that I dont have to repeat the query for each FY solely becasue of the FY condition. My query is below:
FYS=QueryExecute("SELECT
SS.ISEC_POC,
SS.CUSTOMER_ORG,
SS.PROJ_NO,
SS.PURPOSE,
SS.FUND_COMP,
budget_miprs_poc.mipr_to,
budget_tech_poc.tech_poc_name,
budget_miprs_poc.mipr_poc_name,
ss.jono,
mca_jonos.jono_cn,
ss.mipr_number,
ss.mipr_type,
ss.DATE_MIPR_SENT,
ss.inc_dec,
X.INCSENTDATE,
X.COMMITED,
x.COMMITED1,
ss.mdep,
X.OBLIGATED,
ss.BALANCE,
X1.CONT_AWD_DATE,
X1.CONT_REV_DATE,
STUFF((SELECT TOP 3 '/ ' + CAST(CONVERT(CHAR(8),US.last_remark_update,1) AS varchar) + ' ' + US.remarks
FROM MCA.unob_remarks US
WHERE US.MIPR_ID = SS.MIPR_ID
ORDER BY US.last_remark_update DESC
FOR XML PATH('')), 1, 1, '') [SECTORS],
SS.MIPR_ID,
max(unob_remarks.last_remark_update) as lastremarkupdate,
SS.FY,
SS.date_fund_sent
FROM MCA.BUDGET_MIPRS_SENT SS
left join mca.budget_miprs_poc
on (SS.mipr_id = budget_miprs_poc.mipr_id)
left join mca.budget_tech_poc
on (ss.mipr_id = budget_tech_poc.mipr_id)
left join mca.mca_jonos
on (ss.jono = mca_jonos.jono)
left join mca.unob_remarks
on (ss.mipr_id = unob_remarks.mipr_id)
LEFT JOIN
(SELECT budget_miprs_transactions.mipr_id as miprid,
max(cast(CONVERT(CHAR(8),BUDGET_MIPRS_TRANSACTIONS.INC_SENT_DATE,1) AS varchar)) AS INCSENTDATE,
SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT,0)) AS COMMITED,
SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT1,0)) AS COMMITED1,
SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.AMOUNT_OBLIGATED,0)) AS OBLIGATED
FROM mca.budget_miprs_transactions
where BUDGET_MIPRS_TRANSACTIONS.inc_dec is null
GROUP BY budget_miprs_transactions.mipr_id) X
ON x.miprid = ss.mipr_id
LEFT JOIN
(SELECT budget_contract.mipr_id as miprid,
min(cast(CONVERT(CHAR(8),budget_contract.CONTRACT_REV_AWARD_DATE,1) AS varchar)) AS CONT_REV_DATE,
min(cast(CONVERT(CHAR(8),budget_contract.CONTRACT_PROJ_AWARD_DATE,1) AS varchar)) AS CONT_AWD_DATE
FROM mca.budget_contract
GROUP BY budget_contract.mipr_id) X1
ON x1.miprid = SS.mipr_id
WHERE
ss.MIPR_NUMBER <> 'MIPR0'
AND ss.MIPR_NUMBER <> 'pending'
AND ss.MIPR_NUMBER <> 'Hold'
AND x.COMMITED <> x.OBLIGATED
AND SS.ISEC_POC = 'lastname'
GROUP BY SS.MIPR_ID, SS.CUSTOMER_ORG, ss.state, SS.ISEC_POC, SS.PROJ_NO, SS.PURPOSE, SS.FUND_COMP, budget_miprs_poc.mipr_to, budget_tech_poc.tech_poc_name, budget_miprs_poc.mipr_poc_name, ss.jono,
mca_jonos.jono_cn, ss.mipr_number, ss.mipr_type, ss.DATE_MIPR_SENT, ss.inc_dec, X.INCSENTDATE, X.COMMITED, x.COMMITED1, ss.mdep, X.OBLIGATED, ss.BALANCE, X1.CONT_AWD_DATE, X1.CONT_REV_DATE, SS.FY, SS.date_fund_sent
ORDER BY ss.JONO asc, ss.MIPR_NUMBER ASC, 1",[],{datasource="SQL_MCA"});
And I thought I could use:
cfloop(query=FYS, group="MIPR_ID"){
if(FY is "FY20")
{
SpreadSheetAddRows(mySheet,FYS);
}
}
to get just the records for FY20, but its giving them all to me. Basically I only want to add rows to my spreadsheet where FY = 'FY20'
Im not accustomed to working much in cfscript and have a feeling Im missing something in my "if" statement.
Any help would be greatly appreciated!!
There's nothing obviously wrong with it and the expression works fine on trycf, suggesting something else is the issue:
https://trycf.com/gist/f32001c1eda709fe353be32c852a08a8/acf2021?theme=monokai
ORDER BY ss.JONO asc, ss.MIPR_NUMBER ASC, 1",[],{datasource="SQL_MCA"});
...
cfloop(query=FYS, group="MIPR_ID"){
Though it wouldn't cause the behavior you're describing, one thing that stands out is that the "group" feature requires query data be sorted by the grouped column(s) first. It doesn't look like the base query is doing that. So while it does need to be fixed, it doesn't explain the issue you described with the failure of the expression if(FY is "FY20").
Could you post a sample of the query results? Just the relevant columns: MIPR_NUMBER, MIPR_ID and FY values.
ASKER
for(i=1; i LTE FYS.RecordCount; i=i+1){
if(FYS.FY[i] eq 'FY21') {
gave me exactly what I was looking for, but I will look more closely at the cfscript tool. Thanks!!
Second, if you may NEED TO do script (or simply prefer it), you could still write it in tags first (making sure it works) and then CONVERT that code to script.
And here's great news: you can do that conversion AUTOMATICALLY using the excellent free tool, https://cfscript.me, run by Pete Freitag. Or it can help you to try some small snippet of code you think could approximate what you're doing, rather than try the whole query.
Let us know if that gets you going. If not, there would be more info needed to offer a good answer--and there are indeed several ways someone may propose you could go about solving things. That's why I think my suggestions above would be more expedient for you. (Someone here may prefer to offer a direct suggestion, in the meantime.)