Can I query a query?

[scripter73]

Hi,

I have a query that reads a database table and returns 2 codes to me. Code1 is a single alpha character, and the Code2 is a text number whose length could range from 1 character to 5 characters.

My problem is that I need that second code to always be 5 characters, so I need to pad it with leading zeros. For ex: if length = 4, pad with one 0; if length=3, two 0s, etc.
How can I "update" my query, or obtain another query that has my corrected Code2? Below is my code. I'm able to get the length of my Code2, and now I am stuck.


<CFQUERY name=&quot;agentdata&quot; datasource=&quot;#session.dsn#&quot;>
select CODE1, CODE2
from TABLEA
where ID = 'G01200'
order by CODE1, CODE2
</CFQUERY>

Show Agents:<br>

<CFOUTPUT query=&quot;agentdata&quot;>
<cfset #session.agentlength# = Len(#agentdata.CODE2#)>
#agentdata.CODE1##agentdata.CODE2#, Length = #session.agentlength#<br>
</CFOUTPUT>


Thanks in advance,
scripter73
Change Your Thinking, Change Your Life.

 

[GunJack]

Hey Scripter,

You can do query a query in CF 5.0 but I think all you need is to use querySetCell() to modify the original query. I think this will do what you need.

<cfoutput query=&quot;agentdata&quot;>
<cfif len(agentdata.code2) lt 5>
<cfset querySetCell(agentdata,&quot;code2&quot;,&quot;#repeatstring(&quot;0&quot;,5-len(agentdata.code2))##agentdata.code2#&quot;,agentdata.currentrow)>
</cfif>
</cfoutput>

Put this after your agentData query and this should clean up the &quot;code2&quot; field.

Hope this helps,
GJ

 

[scripter73]

Hi GunJack,

I tried your suggestion and it worked like a dream! I can't believe I've been working with queries without this function. You truly opened my eyes.

Thanks for your great help,
scripter73
Change Your Thinking, Change Your Life.

 

[GunJack]

Glad I could help!

GJ