Multi-step query question

[emueth]

I need to run the following query. The code runs fine in SQL but I'm not sure what to do with it in Cold Fusion. I am using SQL 7 and CF5. The query (with hardcoded values instead of variables) is:

DECLARE @emps varchar(50)
	
SELECT @emps = COALESCE(@emps + ';', '') + e.last_name  
FROM employee.dbo.employee e 
WHERE e.employee_id IN (1991,70665) 
	
SELECT *, @emps AS employees 
FROM job_no 
WHERE sim_id = 4

I suspect this may involve a CFTransaction but am not sure how to implement it. Everything I've done has given me a "...does not specify the name of a valid query" error.

 

[SidianConsulting]

I think we all need more information on what exactly you are trying to do. The first query makes use of an interesting technique using the COALESCE function; I've seen it before. It will return a semi-colon delimited list with two employee's last names (if in fact the two employee id's exist that match your IN caluse).

The second query looks like it returns all of the fields from the job_no table where the sim_id equals 4, and a field called "emps" that will list those two employee last names returned in the first query in the variable @emps.

What you want to do with the fields is another matter, so if your question is how do you manipulate the returned data, let us know what you want to do with it.

-Tek

 

[emueth]

Let me back up a bit and give a broader view of what I'm getting at. The client makes widgets and each widget can have multiple employees associated with it. This is stored in a table:
Widget Employee
1 7700
1 7590
1 7549

This application tracks bug reports on the widgets, so each widget can have multiple bugs

Widget Bug Report ID
1 10987
1 20945

I need a way of displaying reports where each row has all the data on the bug report (date, description, etc) and has all the employees associated with it in one concatenated list.

Widget Bug Employee
1 10987 Jones;Smith;Brown
1 20945 Jones;Smith;Brown
etc

The example code in my original post runs perfectly in SQL query analyzer. My problem is getting it to run in CF. Thanks for your help.

 

[tleish]

I believe ColdFusion only takes the first select statement with each <cfquery>. You could seperate the queries into 2 seperate <cfquery>'s and use the CF ValueList() function.

<cfquery name=&quot;query1&quot; ...>
SELECT e.last_name
FROM employee.dbo.employee e
WHERE e.employee_id IN (1991,70665)
</cfquery>

<cfquery name=&quot;query2&quot; ...>
SELECT *, '#ValueList(query1.last_name, &quot;;&quot#' AS employees
FROM job_no
WHERE sim_id = 4
</cfquery> - tleish