Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

non sequenced output of query results

Posted on 2003-12-08
8
Medium Priority
?
150 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:James Rodgers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 9900727
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
 
LVL 25

Author Comment

by:James Rodgers
ID: 9900801
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
 
LVL 17

Accepted Solution

by:
anandkp earned 2000 total points
ID: 9901866
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 11

Expert Comment

by:hart
ID: 9902058
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
 
LVL 25

Author Comment

by:James Rodgers
ID: 9903682
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
 
LVL 25

Author Comment

by:James Rodgers
ID: 9903811
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
 
LVL 17

Expert Comment

by:anandkp
ID: 9903842
coooooool  .... way to go !

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

Cheers
Anand
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9903886
thanks for the good word

...and the help
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question