Run 2 SQL Queries in one connection

[ChrisMcD]

Hello,

I am using a view as my Data Source to simplify the # of sources I need. I am trying to put both the view and the select statement in the same Source so if I update the report - the view will get updated also.

Here is what I have i the data source:

ALTER VIEW [dbo].[PaidTimeOffReport] AS
SELECT
Current_Employee_PaidTimeOff.Payroll_Year,
Current_Employee_PaidTimeOff.Process_Num,
Current_Employee_PaidTimeOff.Company_Num,
Current_Employee_PaidTimeOff.Employee_Num,
Current_Employee.NAME,
Current_Employee.Status,
Current_Employee.Work_Status,
Current_Employee.Default_Distribution1,
Current_Employee.Default_Distribution2,
Current_Employee.Default_Distribution3,
Current_Employee.Default_Distribution4,
Current_Employee.Default_Distribution5,
Current_Employee_PaidTimeOff.PTO_ID,
Current_Employee_PaidTimeOff.Accrued,
Current_Employee_PaidTimeOff.Benefit_Eligibility_Date,
Current_Employee_PaidTimeOff.Carryover,
Current_Employee_PaidTimeOff.Earned,
Current_Employee_PaidTimeOff.Taken
FROM Current_Employee_PaidTimeOff
JOIN Current_Employee ON
Current_Employee.Company_Num = Current_Employee_PaidTimeOff.Company_Num
WHERE
Current_Employee.Employee_Num = Current_Employee_PaidTimeOff.Employee_Num AND
Current_Employee.Process_Num = Current_Employee_PaidTimeOff.Process_Num AND
Current_Employee.Payroll_Year = Current_Employee_PaidTimeOff.Payroll_Year

GO

SELECT * FROM PaidTimeOffReport
WHERE Payroll_Year = 2010
AND Process_Num = 0
{lvCoEmp}

GO

It does not like the GO's but they are necessary to run both statements. Is there a way to run both of the statements in the same source?

As I was trying different things I also got an error about putting Alter View in a batch query, but could not find any info on how to do that in the dcumentation.

Thanks for the help,
Chris

[Alex K.]

Hello,

I am trying to put both the view and the select statement in the same Source so if I update the report - the view will get updated also.

We think that this action is a bit not correct. The report designer must only show data without making any changes in the database, any changes in the database should be made in your application, but not in the report.

In this case, you can merge the two queries and use the following query:

Code: Select all

SELECT Current_Employee_PaidTimeOff.Payroll_Year,
       Current_Employee_PaidTimeOff.Process_Num,
       Current_Employee_PaidTimeOff.Company_Num,
       Current_Employee_PaidTimeOff.Employee_Num,
       Current_Employee.NAME,
       Current_Employee.Status,
       Current_Employee.Work_Status,
       Current_Employee.Default_Distribution1,
       Current_Employee.Default_Distribution2,
       Current_Employee.Default_Distribution3,
       Current_Employee.Default_Distribution4,
       Current_Employee.Default_Distribution5,
       Current_Employee_PaidTimeOff.PTO_ID,
       Current_Employee_PaidTimeOff.Accrued,
       Current_Employee_PaidTimeOff.Benefit_Eligibility_Date,
       Current_Employee_PaidTimeOff.Carryover,
       Current_Employee_PaidTimeOff.Earned,
       Current_Employee_PaidTimeOff.Taken
  FROM Current_Employee_PaidTimeOff
   JOIN Current_Employee ON
         Current_Employee.Company_Num = Current_Employee_PaidTimeOff.Company_Num
 WHERE
       (Current_Employee.Employee_Num = Current_Employee_PaidTimeOff.Employee_Num AND
        Current_Employee.Process_Num = Current_Employee_PaidTimeOff.Process_Num AND
        Current_Employee.Payroll_Year = Current_Employee_PaidTimeOff.Payroll_Year) AND
       (Current_Employee_PaidTimeOff.Payroll_Year = 2010 AND 
        Current_Employee_PaidTimeOff.Process_Num = 0)

Thank you.