non sequenced output of query results

I have a query which can return 0-n records in the format

empid cid cidvalue

empid is the primary look up for the table with cid as a secondary so PK for the table is (empid,cid)

i need to output this information in a report but the report layout does not allow for looping over the query to output the cidvalue

the output is based on cid as a key, i need to be able to run the query once and using the value of cid lookup cidvalue so that it can be outptut in the proper location on the report
LVL 25
James RodgersWeb Applications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tacobell777Commented:
empid is the primary look up for the table with cid as a secondary so PK for the table is (empid,cid)

i need to output this information in a report but the report layout does not allow for looping over the query to output the cidvalue

-----

Why does it not allow looping over the cidvalue? Whats the error if any?

-----

the output is based on cid as a key, i need to be able to run the query once and using the value of cid lookup cidvalue so that it can be outptut in the proper location on the report

-----

I am not 100% sure what it is your after, maybe I just don't understand the question, can you rephrase?

Just some wild guesses:

SELECT empid, cid, cidvalue, empid + cid AS myKey
FROM yourtable

<cfloop query="yourQuery">
<cfset myKey = yourQuery.empid & yourQuery.cid>
#variables.myKey#  #yourQuery.empid#
</cfloop>

0
James RodgersWeb Applications DeveloperAuthor Commented:
no i cant loop over the query as the output is not in a sequential format

if you consider that i get 4 rows of returned values from my standard query
EMPID   CID      CIDVALUE
111111 SADE   long text description od the contents of this field
111111 DFIO    even more of a long text description od the contents of this field
111111 KUTI    a not so long text description od the contents of this field
111111 WSSD  a very short text field

the fields might have to be displayed on the report as
-----------------------------------------------------
DFIO                                                           |
-----------------------------------------------------|
some other content                                      |
-----------------------------------------------------|
SADE                                                          |
-----------------------------------------------------|
other content                                               |
still more other content                                 |
and still more dynamic content                       |
-----------------------------------------------------|
WSSD                                                          |
-----------------------------------------------------|
KUTI                                                            |
-----------------------------------------------------|
                                                                   |
and still more dynamic content                       |
-----------------------------------------------------|

so looping over the query is not possible, i waqnt to use the CID as a lookup for CIDVALUE and output it at the specified location on the report
0
anandkpCommented:
take all the values in a list [ValueList] & then play around with the list as u want to
OR
u cld also use arrays for this !

<cfquery datasource="MyDatasource" name="MyQuery">
  SELECT FirstName, LastName FROM MyTable
</cfquery>

<cfoutput>
#MyQuery["FirstName"][3]#
</cfoutput>
This example displays the value of the "FirstName" column, third row.

With this - u can access the values in a non sequential format & can get ur reporting done as per the need !

K'Rgds
Anand
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

hartCommented:
will this do..

hit the query... and do this
<html>
<head>
<script language="javascript">  
   <cfloop query="queryname">
      var lst_#empid# = '#EmpID#';
      var lst_#cid# = '#cid#';
      var lst_#cid#value = '#cidvalue#';
   </cfloop>
function showVal()
{
   var nCid = document.frm.selcid.options[document.frm.selcid.selectedIndex].value;
   document.all.showcidvalue.innerHtml = eval("lst_" + nCid + "");
}
</script>
</head>
<body>
   <form name="frm">
        <select name="selcid">
           <cfoutput query="queryname" onchange="showVal()">
                <option value="#cid#">#cidvalue#</option>
           </cfoutput>
       </select>
       <div id="showcidvalue"></div>
   </form>
</body>
</html>

Regards
Hart
0
James RodgersWeb Applications DeveloperAuthor Commented:
sorry hart, not what i need

i need to output cidvalue form the server side, the report has to be complete and ready to print as soon ans the html is rendered by the browser

Anand, your answer looks like what i am looking for ... just need to test

thanks
0
James RodgersWeb Applications DeveloperAuthor Commented:
Anand,
used what you suggested and came up with this
<cfquery name="getFreeText" ..... >
      SELECT CID, CIDVALUE
      FROM HR_ToolBox_CDPG_FreeText
      WHERE EMPID =<cfqueryparam value="#empLookupId#">
</cfquery>

<cfset lookupList=#ValueList(getFreeText.CID)#>

<cfif listFind(lookupList,'SPS')>
#getFreeText['CIDVALUE'][listFind(lookupList,'SPS')]#
</cfif>

and it seems to do what i need...

thanks
0
anandkpCommented:
coooooool  .... way to go !

Looks like u've merged - ValueList & Array ... Neat one !

Cheers
Anand
0
James RodgersWeb Applications DeveloperAuthor Commented:
thanks for the good word

...and the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.