Member Avatar for leonaral

I'm getting the error Incorrect syntax near '=' when running the following query. I'm fairly new to SQL, but this looks like it's pretty straightforward. Basically, I have a table with smartphone information (A_INV_SMARTPHONE) and a table for Windows CE Pocket PC information (A_INV_WINDOWS_CE). Both tables contain columns for Manufacturer and DeviceModel. I want my query results to show one column each for Manufacturer and DeviceModel, therefore, I need the CASE statement to differentiate which table to pull the Manufacturer and DeviceModel information from. If a device is a Windows CE pocket PC device, the ClientCategory field will show a -2. If it is a smartphone, it will show a -7. The ClientCategory field is present in the A_CLIENT table, as well as the two tables mentioned above. Can someone help me figure out why I'm getting the syntax error with the =?

SELECT p.PhonePhoneNumber AS MobileNumber,p.CurrentNetwork AS Carrier,
Manufacturer = CASE c.ClientCategory
 WHEN c.ClientCategory = -2
 THEN ppc.Manufacturer
 ELSE sp.Manufacturer
 END,
Device Model = CASE c.ClientCategory
 WHEN c.ClientCategory = -2
 THEN ppc.DeviceModel
 ELSE sp.DeviceModel
 END,
c.OSVersion,c.OSShell,p.IMEI,d.ESN,c.ClientName,c.FirstConnectDate AS ProvisioningDate,c.ClientCategory
FROM A_CLIENT c
LEFT JOIN A_INV_PHONE p ON p.ClientUID = c.ClientUID
LEFT JOIN A_INV_DEVICE d ON d.ClientUID = c.ClientUID
LEFT JOIN A_INV_SMARTPHONE sp ON sp.ClientUID = c.ClientUID
LEFT JOIN A_INV_WINDOWS_CE ppc ON ppc.ClientUID = c.ClientUID
WHERE NOT c.ClientCategory = -1
ORDER BY p.PhonePhoneNumber
  • 1 Contributor
  • 1 Reply
  • 160 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by leonaral
Member Avatar for leonaral

I just figured out what I had wrong. I modified it to the following, and it's running with no errors now.

SELECT p.PhonePhoneNumber AS MobileNumber,p.CurrentNetwork AS Carrier,
(CASE WHEN c.ClientCategory = -2
 THEN ppc.Manufacturer
 ELSE sp.Manufacturer
 END) AS DeviceManufacturer,
(CASE WHEN c.ClientCategory = -2
 THEN ppc.DeviceModel
 ELSE sp.DeviceModel
 END) AS DeviceModel,c.OSVersion,c.OSShell,p.IMEI,d.ESN,c.ClientName,c.FirstConnectDate AS ProvisioningDate
FROM A_CLIENT c
LEFT JOIN A_INV_PHONE p ON p.ClientUID = c.ClientUID
LEFT JOIN A_INV_DEVICE d ON d.ClientUID = c.ClientUID
LEFT JOIN A_INV_SMARTPHONE sp ON sp.ClientUID = c.ClientUID
LEFT JOIN A_INV_WINDOWS_CE ppc ON ppc.ClientUID = c.ClientUID
WHERE NOT c.ClientCategory = -1
ORDER BY p.PhonePhoneNumber
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.