Link to home
Start Free TrialLog in
Avatar of diecasthft01
diecasthft01

asked on

Assigning a number to a query value in Coldfusion

Good morning, I was hoping to get some advice, assistance with a Coldfusion SQL Query, or array, or some process for wqhat I need to do. I have a simple query, and all that query is doing is getting years from a data table...I only want to have a single result so Im doing a distinct query...and every works fine...no problem there.

<CFQUERY DATASOURCE="SQL" NAME="GetCurPosFYs">

SELECT 

DISTINCT PROBE.FY

FROM MCA.PROBE

ORDER BY PROBE.FY ASC

</cfquery>

This will return the years 2021, 2022, 2023 and so on through 2029.

What id like to do now is assign a sequential number to each year that I can use as a variable in building some spreadsheets with Coldfusion.

So 2021 would be 1, 2023 would be 2, and so on....but if the query result starts at 2022, then 2022 would be assigned 1 and so on. I was looking at doing something with arrays in Coldfusion but I think Im going down the wrong path. Any ideas, help would be greatly appreciated.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Assuming SQL Server?

If you only have the one year per row, possibly ROW_NUMBER?

SELECT 
DISTINCT ROW_NUMBER() OVER(ORDER BY PROBE.FY), PROBE.FY
FROM MCA.PROBE
ORDER BY PROBE.FY ASC

Open in new window

Do you want these new values to be a new variable? Of what name? Should the name have the year and this index in it? Only this index? Or should it be an array of values? 


And would you want to skip numbers if there's a gap in years? 


Of course you can loop over the rows to process them, but note that you can also simply get all those rows' values as a list using valuelist(GetCurPosFYs.FY). With that list you could use other list functions to process that list. 


Maybe with more clarification I or others can offer more specific suggestions. 

Avatar of diecasthft01

ASKER

Yes, I believe I want the new values to be a new variable. The name shouldnt have the year, all I want is a sequential number (index) list based on the distint years returned from the query. I dont want to skip numbers if there is a gap in years. 


Im working with making some spreadsheets, and Im adding columns , so I want to then use that sequential number in some if statements, and I want the sequential number so I can so something like a if sequntial number is 5, then put the data in this column. I cant use the specific year from the query becasue if I do, and a particular year doesnt existing in the query, then the spreadsheet order of coumns gets messed up. Im trying not to hardcode the years in the spreadsheet.

Unless you need to fill in gaps where years are missing, like charlie is asking about, then, technically you don't really need to do anything if you use array notation when accessing the data in your your query for each row, because cf already assigns each record in a result set a "row number" of 1 to recordcount based on the order they come out of the query.

for example:

<CFQUERY DATASOURCE="SQL" NAME="GetCurPosFYs">
  SELECT 
  DISTINCT PROBE.FY
  FROM MCA.PROBE
  ORDER BY PROBE.FY ASC
</cfquery>
<cfoutput>
<table>
  <tr>
    <td>row number</td>
    <td>year</td>
  </tr>
  <cfloop from="1" to="#GetCurPosFYs.recordCount#" index="i">
  <tr>
    <td>#i#</td>
    <td>#GetCurPosFYs.FY[i]#</td>
  </tr>
  </cfloop>
</table>
</cfoutput>

Open in new window




I believe thats what Im looking for...

I'm still not 100% sure I understand what you are looking for but you could create a structure or array or whatever you need using the looping I suggested... another example here would give a structure where the keys are the years and thevalues are the row numbers:


<CFQUERY DATASOURCE="SQL" NAME="GetCurPosFYs">
  SELECT 
  DISTINCT PROBE.FY
  FROM MCA.PROBE
  ORDER BY PROBE.FY ASC
</cfquery>
<cfset yearSequences = structNew()>
<cfloop from="1" to="#GetCurPosFYs.recordCount#" index="i">
  <cfset yearSequences[GetCurPosFYs.FY[i]] = i>
</cfloop>
<cfdump var="#yearSequences#">

Open in new window


ASKER CERTIFIED SOLUTION
Avatar of Scott Bennett
Scott Bennett
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

As a side note.... I used to exclusively use that array notation when dealing with query results because in old versions of CF if you had nested loops on query results, the outer loops would sometimes lose their sequences and you would get unexpected results.... I'm sure Charlie experienced that too back in the old days :)

Yep, Scott. It's been nice to see some challenges like that go away as CF has evolved/matured. 


Indeed, there are still other ways that this problem could have been solved (since CFML is indeed SO flexible), and that's why I had pressed the OP originally for more on their goal (like you did as well). Still, I'm glad they feel they have a solution that will suffice.