asked on

SQL - SP needs help

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

vasto

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]

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

5teveo

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

is missing from statement...

MBoy2

ASKER

Thanks