SQL - SP needs help

MBoy2
MBoy2 used Ask the Experts™
on
The last select (ObserverID) needs help.  I'm moving data from a really ugly db design to something more normalized.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Mote_GetAllV1NestData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mote_GetAllV1NestData];
GO
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestData]
AS
BEGIN
     SELECT
     [ID] As 'OriginalNestID',
     'IsNest' = CASE [Nest]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
                 END,
     'UnmarkedNest' = CASE [Verified]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
                 END,      
     [Ft# So#] As 'FTNumberSONumber',                        
       'NestLocationDate' = CASE      WHEN ISDATE(Cast([Date] as datetime)) = 1
                        THEN CONVERT(VARCHAR(10), [Date], 101)
                        ELSE '01/01/1900'
                        END,
       'ObserverID' = (SELECT [Mote_Observer].[ObserverID] WHERE [V1Nest].[Observer] =[Mote_Observer].[ObserverName])          
     FROM [V1Nest], [Mote_Observer]
END
GO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
SELECT [ID] AS 'OriginalNestID',
       'IsNest' = CASE [Nest]
                       WHEN 0 THEN '0'
                       WHEN 1 THEN '1'
                       ELSE '0'
                  END,
       'UnmarkedNest' = CASE [Verified]
                             WHEN 0 THEN '0'
                             WHEN 1 THEN '1'
                             ELSE '0'
                        END,
       [Ft# So#] AS 'FTNumberSONumber',
       'NestLocationDate' = CASE
                                 WHEN ISDATE(CAST([Date] AS DATETIME)) = 1 THEN
                                      CONVERT(VARCHAR(10), [Date], 101)
                                 ELSE '01/01/1900'
                            END,
       [Mote_Observer].[ObserverID] AS ObserverID
FROM   [Mote_Observer]
       INNER JOIN [V1Nest].[Observer]
            ON  [Mote_Observer].[ObserverName] = [V1Nest].[Observer]
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
At minimum...

       'ObserverID' = (SELECT [Mote_Observer].[ObserverID] WHERE [V1Nest].[Observer] =[Mote_Observer].[ObserverName])      

... should be ...

(SELECT [Mote_Observer].[ObserverID] FROM [V1Next] JOIN =[Mote_Observer] ON [V1Nest].[Observer] =[Mote_Observer].[ObserverName])  as ObserverID

... and that assumes that the above SQL will always return one record.

Also, all of the rest of your blocks should be <expression> as ColumnName, and not ColumnName = <expression>.
Top Expert 2012

Commented:
'ObserverID' = (SELECT [Mote_Observer].[ObserverID] WHERE [V1Nest].[Observer] =[Mote_Observer].[ObserverName])          

is missing from statement...

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial