Can I do an IF inside a SELECT, or else how do I do this query? help! 1

[BeanDog]

Here's the idea: I have a tblAd, which has a upc field. It has an outer join with tblItemNumber's upc column. A UPC may or may not have an itemnumber, so I used this outer join. In tblItemNumber, there is a field, itemnumberlistID, which tells what user's item number this is. So you see, a upc could have zero or more item numbers, up to any amount, but only ONE PER USER. At first I used:<br>------------------------------<br>... WHERE (dbo.tblItemNumber.itemnumberlistID = @pItemNumberListID OR<br>dbo.tblItemNumber.itemnumberlistID IS NULL)<br><br>------------------------------<br>Only problem is, this doesn't return a row if the tblItemNumber.itemnumberlistID is, say, 3 and the @pItemNumberListID is 1. I was thinking, if I could somehow fit it in like this:<br>------------------------------<br>... WHERE (dbo.tblItemNumber.itemnumberlistID = @pItemNumberListID OR<br>(IF dbo.tblItemNumber.itemnumberlistID NOT NULL AND dbo.tblItemNumber.itemnumberlistID &lt;&gt; @pItemNumberListID<br>BEGIN<br>dbo.tblItemNumber.itemnumber = NULL <br>END)<br>------------------------------<br>I am really frustrated, as you can probably tell. I need it to return the itemnumber field null if the itemnumberlistID isn't a certain value, but I STILL NEED THE REST OF THE ROW!<br><br>please help me...<br><br>&lt;whimper&gt; <br><br>

 

[foxdev]

Mr. Dog, I didn't completely follow the logic for which rows you want to select, but the technical answer to your question is to use a CASE construct within your SELECT.<br><br>Here is an example from BOL:<br><br><FONT FACE=monospace><br>SELECT&nbsp;&nbsp;'Price Category' = <br>&nbsp;&nbsp;&nbsp;CASE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN price IS NULL THEN 'Not yet priced'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN price &lt; 10 THEN 'Very Reasonable Title'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN price &gt;= 10 and price &lt; 20 THEN 'Coffee Table Title'<br>&nbsp;&nbsp;&nbsp;ELSE 'Expensive book!'<br>&nbsp;&nbsp;&nbsp;END,<br>&nbsp;&nbsp;&nbsp;CAST(title AS varchar(20)) AS 'Shortened Title'<br>FROM titles<br>ORDER BY price<br></font> <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[gummadi]

Hi,<br><br>&nbsp;Take out the total ---<br>... WHERE (dbo.tblItemNumber.itemnumberlistID = @pItemNumberListID OR<br>dbo.tblItemNumber.itemnumberlistID IS NULL)<br>--- condition from your selct statement ,because you want select row&nbsp;&nbsp;irrespective of whether it is null,matching or not matching,but only thing is you want to have null value in the column if value is not matching and if the value is null.<br><br>In select clause at <br><br>select tblItemNumber.itemnumberlistID&nbsp;&nbsp;<br><br>replace it with<br><br>select&nbsp;&nbsp;&nbsp;'item_id' = <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;case when&nbsp;&nbsp;tblItemNumber.itemnumberlistID = @pItemNumberListID&nbsp;&nbsp;then @pItemNumberListID&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end,<br><br>--<br>so , 'item_id' = <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;case when&nbsp;&nbsp;tblItemNumber.itemnumberlistID = @pItemNumberListID&nbsp;&nbsp;then @pItemNumberListID&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end,<br><br>should be replaced for tblItemNumber.itemnumberlistID&nbsp;&nbsp;<br>in your select starement without where condition<br>I think this will work<br><br><br>bye <br><br>gummadi<br><br><br><br><br><br><br>&nbsp;