RE: [plum] SQL help

[Mark Fuqua]

Hey Tim,

 

I am afraid I can't help with a sql statement like that.  But there are tons of sql specific lists. 

 

This is a good place to start.

 

http://www.sqlservercentral.com/

 

 

And if I remember correctly, you are an oracle guy...

 

http://forums.oracle.com/forums/index.jspa

 

I looked, your sql looks good to me.  But that doen't mean much.  Post the answer when you get it.

 

Mark Fuqua

 

 

 

-----Original Message-----
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