James Rodgers
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.option s[document .frm.selci d.selected Index].val ue;
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
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.option
document.all.showcidvalue.
}
</script>
</head>
<body>
<form name="frm">
<select name="selcid">
<cfoutput query="queryname" onchange="showVal()">
<option value="#cid#">#cidvalue#</
</cfoutput>
</select>
<div id="showcidvalue"></div>
</form>
</body>
</html>
Regards
Hart
ASKER
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
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
ASKER
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(getF reeText.CI D)#>
<cfif listFind(lookupList,'SPS') >
#getFreeText['CIDVALUE'][l istFind(lo okupList,' SPS')]#
</cfif>
and it seems to do what i need...
thanks
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(getF
<cfif listFind(lookupList,'SPS')
#getFreeText['CIDVALUE'][l
</cfif>
and it seems to do what i need...
thanks
coooooool .... way to go !
Looks like u've merged - ValueList & Array ... Neat one !
Cheers
Anand
Looks like u've merged - ValueList & Array ... Neat one !
Cheers
Anand
ASKER
thanks for the good word
...and the help
...and the help
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>