View data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

View data

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-02 : 14:57:46

We have a View that is stored on our server. It uses data from other 'Views' that are also stored on the server.

All of our views are old, poorly written, and time consuming.

I need some data that is similar to an existing view, so I am trying to rewrite it as either a stored procedure or a SELECT statement that I can use in my VS2005 report.

What I have below is gramatically the same, except that it excludes several redundant rows that I do not need; however, I get the following error:

quote:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is what I am trying to run in Query Analyzer:

SELECT FIRSTNAME+' '+LASTNAME+' ('+NUM+')' As 'Operator', Serial_Number, Date_Time, System_ID,
       (SELECT Serial_Number, COUNT(DISTINCT date_time) + 1 AS 'NumTests'
        FROM Test_Results
        WHERE (Serial_Number=TR.Serial_Number) AND (Date_Time<TR.Date_Time) AND (System_ID LIKE '%Decay%')
       ) AS 'FirstTest', Test_Result
FROM Test_Results TR LEFT OUTER JOIN EmployeeInfo ON TR.OP_ID=NUM
WHERE (System_ID Like '%Decay%')
ORDER BY Date_Time DESC

The parameters FIRSTNAME, LASTNAME, and NUM are char strings located in EmployeeInfo.
Serial_Number [varchar(20)], Test_Results [varchar(255)] and Date_Time are in Test_Results.

How can I write this query?

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-02 : 15:17:54

I think I got it: I just moved the secondary SELECT clause to the FROM section, i.e.

SELECT FIRSTNAME+' '+LASTNAME+' ('+NUM+')' As 'Operator',
  Serial_Number,
  COUNT(DISTINCT Date_Time) + 1 AS 'NumTests',
  Date_Time,
  System_ID,
  Test_Result
FROM (
  SELECT OP_ID, System_ID, Test_Result, Serial_Number, Date_Time
  FROM Test_Results PD
  WHERE (System_ID LIKE '%Decay%')
) TR LEFT OUTER JOIN EmployeeInfo ON TR.OP_ID=NUM
GROUP BY LASTNAME, FIRSTNAME, NUM, System_ID, Date_Time, Serial_Number, Test_Result
ORDER BY Date_Time DESC

If anyone looks at this can sees a flaw in my logic or a faster technique, please comment!

Regards,
Joe

Avoid Sears Home Improvement

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-02 : 15:25:13

Can you explain what logic you need here ? I ask because you have "Date_Time<TR.Date_Time" there in your first query.

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-02 : 16:02:07

Honestly, I don't understand that one either.

The Test_Results table holds the Date_Time when tests were done, and the existing View was written that way.

My guess is it was some trick to get the earliest Date_Time for a given Serial_Number.

The person who originally wrote that code has not worked here for over 5 years, and there is no documentation.

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources