Jump to content

CASE result set problems


Recommended Posts

Hey, I am trying to plug into a third party database, and am having problems getting my CASE statement to work.

 

I have been at this for a while now, even tried using a WITH and didn't get anywhere.  Basicly I am looking for a result set that will ahve the "Comment" set to yes or no depending on if the sheetID is in both the Files table and the tbl_dialog table.  Problem is, that while the CASE is outputing Yes and No appropriately for different sheetID's it is also causing 4700 records to be returned for each sheetID and taking an age to perform the querie.

 

Hope someone can help, here's the SQL in it's latest incarnation:

SELECT
Files.SheetID, Files.PenName, Files.FileDate, Files.FileName, 
Users.UserName, 
Folders.FolderName,
'Comment' = CASE WHEN ((SELECT Files.sheetID) IN (SELECT Files.SheetID FROM dbo1.Files INNER JOIN dbo2.tbl_dialog ON (dbo1.Files.SheetID = dbo2.tbl_dialog.sheetID))) 
THEN 'Yes' 
ELSE 'No' 
END 

FROM 
dbo1.Files AS Files 
INNER JOIN dbo1.Sheet AS Sheet 
ON (Files.SheetID = Sheet.SheetID)
LEFT JOIN dbo1.Pad AS Pads
ON (Sheet.PadID = Pads.PadID) 
LEFT JOIN UserFolders AS Folders
ON (Files.AssignedFolderID = Folders.FolderID)
RIGHT JOIN dbo1.Users AS Users
ON (Users.UserID = Folders.UserID)
, dbo2.tbl_dialog


WHERE Pads.PadPrefix = 'String' 
AND (FileStatus <> 1 AND Files.SheetID > 100000) 

GROUP BY Files.SheetID, Files.FileStatus, Files.PenName, Files.FileDate, Files.FileName, 
Users.UserName, 
Folders.FolderName, 
dbo2.tbl_dialog.sheetID

ORDER BY Files.SheetID DESC

 

Some data has been manipulated, but the structure is intact.

Link to comment
Share on other sites

Hi

 

Basic problem seems to be that you are doing a JOIN onto tbl_dialog without specifying on what columns. Bet that table has the same number of rows as you are getting duplicates on sheetIDs. Also the GROUP BY columns do not seem to match those that are selected. Also think you can use a JOIN rather than the subselect (I am assuming that tbl_dialog with have multiple rows for each sheet ID)

 

My SQL server is a bit rusty and nothing handy to try it out on, but hopefully this will give you an idea.

 

SELECT Files.SheetID, Files.PenName, Files.FileDate, Files.FileName, Users.UserName, Folders.FolderName,
CASE WHEN dialog.SheetID IS NULL THEN 'No' ELSE 'Yes' END AS Comment
FROM dbo1.Files AS Files 
INNER JOIN dbo1.Sheet AS Sheet ON (Files.SheetID = Sheet.SheetID)
LEFT JOIN dbo1.Pad AS Pads ON (Sheet.PadID = Pads.PadID) 
LEFT JOIN UserFolders AS Folders ON (Files.AssignedFolderID = Folders.FolderID)
RIGHT JOIN dbo1.Users AS Users ON (Users.UserID = Folders.UserID)
LEFT OUTER JOIN (SELECT DISTINCT dialog.SheetID FROM dbo2.tbl_dialog) dialog ON (Files.SheetID = dialog.SheetID)
WHERE Pads.PadPrefix = 'String' 
AND (FileStatus <> 1 AND Files.SheetID > 100000) 
ORDER BY Files.SheetID DESC

 

All the best

 

Keith

Link to comment
Share on other sites

Hey Keith, many thanks for taking a look.  I'll give it a try when I'm back in the office tomorrow.  But there are over 9400 rows in tbl_dialog ( I did a SELECT COUNT(*) from the table when I got the random 4700 results per sheet returning as I instantly thought that it was looping the select from that table for every sheet ID) although I had thought that linking the JOIN on the sheetID field within the case would have been enough.  I did try a LEFT JOIN at one point, but it only returned the sheets that had comments on them, although I can't remember if I tried it in conjunction with the CASE or not.

Link to comment
Share on other sites

Keith, you're a STAR! worked perfectly.

 

Although I'm still not sure where the 4700 was coming from, so I'm going to spend a couple hours working through the code to see if I can see what exactly I did wrong.  After that It's onto the real fun stuff of pulling the user name of the last comment posted from a cross ref table on user ID only when the CASE returns 'Yes'. 

 

Anyone know if you can nest CASE statements?  :shrug:

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.