RE: [plum] SQL help

[Dan O'Keefe]

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 help

Hi 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