December 14, 2006 at 7:43 am
I am trying to export data via a query and using a cursor in it. The problem is that I am getting a single answer as a result in the Text File.
I am using the following Query:
---------------------------------------------------------------------
DECLARE @ixProject int
DECLARE t_Export CURSOR FOR
Select ixProject from Project where
sProject = '1' OR sProject = '2' OR sProject = '3'
OPEN t_Export
FETCH NEXT FROM t_Export into @ixProject
WHILE @@FETCH_STATUS = 0
BEGIN
Select count(ixBug) as Case_Count from Bug
where fOpen = 1 AND
ixProject = @ixProject AND
ixFixFor = 23
FETCH NEXT FROM t_Export into @ixProject
END
CLOSE t_Export
DEALLOCATE t_Export
-----------------------------------------------------------------------
The Problem is when I run in Query Analyzer I get expected results, but when I run it in Exporting Backup, the output is just one record, whereas it should be multiple as to how many times the WHILE runs.
December 14, 2006 at 7:56 am
Can you explain, what you assume should be export.
I think if you can used the simple query without the cursor then it will be good.
I don't know what you want to accompolish but seeing your code i make the query
Select ixProject,count(ixBug) as Case_Count from Project Inner join Bug on Bug.ixProject=Project.SProject where
sProject In ('1','2','3') and fOpen = 1 And ixFixFor = 23
Let me know have this slove you problem
cheers
December 14, 2006 at 8:08 am
Well I actually want to retrieve data from Bug Table, based on the Id's retrieved from Project and another table.
The Problem is that there is one-to-many relationship of the Project Table and 2nd Table with the Bug Table.
However, I will check this query and respond to you - thanks for the help. Can we use Inner Join for achieving this task. An example if possible as I am a new one into databases.
December 14, 2006 at 8:16 am
Yes you can do with the inner join, but if you want that all projects list even though they have no bugs then you should need the LEFT OUTER JOIN inplace of INNER JOIN.
cheers
December 14, 2006 at 10:44 am
What is "Exporting Backup"? Is it a DTS package where you're using the query in a Transform Data task?
Greg
Greg
December 18, 2006 at 7:34 am
Selectcount(Bug_Table.ixBug) as Case_Count,
Bug_Table.ixProject as BTixP
from Bug Bug_Table
INNER JOIN Project Project_Table
on Bug_Table.ixProject = Project_Table.ixProject
INNER JOIN Status Status_Table
on Bug_Table.ixStatus = Status_Table.ixStatus
WHERE Bug_Table.fOpen = 1 AND
Bug_Table.ixFixFor = 23
Group by Bug_Table.ixProject
-- I am using the above mentioned query, can you please suggest a way as to how I can extract the column values in my result. A column Status Name and Project Name are available in Status and Project Table and I need them to be shown in the resultant output.
Can you please help me --> Bytw Ijaz -- thanks for the idea. It helped me a lot.
December 18, 2006 at 9:43 am
Here is your required query. Any field you need you can put in the select list and if you have used the group by then you should also need to add these columns in group by clause.
Select count(Bug_Table.ixBug) as Case_Count,
Bug_Table.ixProject as BTixP,Status.StatusName, Project.ProjectName
from Bug Bug_Table
INNER JOIN Project Project_Table
on Bug_Table.ixProject = Project_Table.ixProject
INNER JOIN Status Status_Table
on Bug_Table.ixStatus = Status_Table.ixStatus
WHERE Bug_Table.fOpen = 1 AND
Bug_Table.ixFixFor = 23
Group by Bug_Table.ixProject,Status.StatusName, Project.ProjectName
Best way, you should look the complete SELECT clause help in the Book online.
cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply