On 7/27/05, Dan O'Keefe <[EMAIL PROTECTED]
> wrote:
I believe that would be the cause then for the multiple records. Not sure unless I tried it with the data.
Yes, there are multiple records in the R_OperatingSystem table with the same DeviceName.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Tim Blankenship
Sent: Wednesday, July 27, 2005 9:27 AM
To: [email protected]
Subject: Re: [plum] SQL help
On 7/27/05, Dan O'Keefe < [EMAIL PROTECTED]> wrote:Tim,Is it a one to one relationship between tables or are there multiple DeviceName records in R_OperatingSystem ?Dan
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Tim Blankenship
Sent: Tuesday, July 26, 2005 3:23 PM
To: Plum Email List
Subject: [plum] SQL helpHi guys and gals I am trying to import some data for one of my plum apps but am having some issues.
I am geeting duplicate SN's even though I am using distinct so apparantly I am doing something wrong.
Any help would be great.
SELECT DISTINCT LTRIM(RTRIM(UPPER(dbo.R_Inventory.SerialNumber))) AS SN,
CASE dbo.R_OperatingSystem.OSVendor
WHEN 'Microsoft' THEN '3'
END as PLATFORM_ID,
CASE dbo.R_OperatingSystem.OSType
WHEN 'Windows 2000' THEN '3'
WHEN 'Windows NT' THEN '1'
WHEN 'Microsoft Windows Server 2003' THEN '2'
END as OS_ID,
CASE LEFT (dbo.R_OperatingSystem.SubDesc, 14)
WHEN 'Service Pack 1' THEN '3'
WHEN 'Service Pack 2' THEN '4'
WHEN 'Service Pack 3' THEN '8'
WHEN 'Service Pack 4' THEN '9'
WHEN 'Service Pack 5' THEN '21'
WHEN 'Service Pack 6' THEN '22'
END AS PATCH_ID
FROM dbo.R_Inventory INNER JOIN
dbo.R_OperatingSystem ON UPPER(LTRIM(RTRIM(dbo.R_Inventory.DeviceName)))= UPPER(LTRIM(RTRIM(dbo.R_OperatingSystem.DeviceName)))
WHERE dbo.R_Inventory.SerialNumber > '0'
ORDER BY SN
--
Tim Blankenship
[EMAIL PROTECTED]
www.vespri.com
--
Tim Blankenship
[EMAIL PROTECTED]
www.vespri.com
--
Tim Blankenship
[EMAIL PROTECTED]
www.vespri.com
