Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with CASE


Need help with CASE

Author
Message
maxlezious
maxlezious
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 230
Hi, I have a select statement and i am selecting the results of the select statement as @clientName

Now I want to do a like on the @clientName, so if @clientName is LIKE _P_ then return 3

I am not sure if the following code is correct


DECLARE @clientName VARCHAR (60)

SELECT DISTINCT @clientname = ClientName
FROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461)

CASE WHEN @clientname like
('_p_') THEN RETURN 3
ELSE 1


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24245 Visits: 37978
maxlezious (5/3/2013)
Hi, I have a select statement and i am selecting the results of the select statement as @clientName

Now I want to do a like on the @clientName, so if @clientName is LIKE _P_ then return 3

I am not sure if the following code is correct


DECLARE @clientName VARCHAR (60)

SELECT DISTINCT @clientname = ClientName
FROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461)

CASE WHEN @clientname like
('_p_') THEN RETURN 3
ELSE 1



Looking at the only SELECT statement, you have a problem. The way I read this is that yomay have more than 1 ClientNumber in tblSystem where SystemNum = 30461. This will result in multiple ClientName's being selected from tblClient based on ClientNumber existing in the list of client numbers returned in the subquery.

This is where your problem shows up, the variable @clientname will contain the name of the last clinet returned from tblClient that meets your criteria.

Would you please explain what it is you are actually trying to accomplish here?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
maxlezious
maxlezious
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 230
Thanks for your reply Lynn.

We have this working php code and i am trying to convert this into sql


$db_name = "DB_admin1" ;
$conn = mssql_select_db($db_name) or die('Connection to the Database failed!
') ;
$query = "SELECT DISTINCT ClientName FROM tblClient WHERE ClientNumber IN (SELECT ClientNumber FROM tblSystem WHERE SystemNum = ".$_SESSION['system'].")";
$result = mssql_query($query) ;
if($row = mssql_fetch_row($result)){
$clientName = $row[0];
if(is_numeric(strrpos(strtolower($clientName), '_p_')))



The following query selects only one client name as it should

SELECT DISTINCT ClientName
FROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461)



All I want is a case/if statement so if the client name has '_p_' in it, then return 3 otherwise 0
maxlezious
maxlezious
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 230
Is this correct way to do it

DECLARE @clientname VARCHAR (60)

SELECT DISTINCT @clientname = ClientName
FROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = @systemID)


SELECT CASE
WHEN @clientName LIKE '_p_'
THEN 3
ELSE 2
END


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16628 Visits: 17024
maxlezious (5/3/2013)
Thanks for your reply Lynn.

We have this working php code and i am trying to convert this into sql


$db_name = "DB_admin1" ;
$conn = mssql_select_db($db_name) or die('Connection to the Database failed!
') ;
$query = "SELECT DISTINCT ClientName FROM tblClient WHERE ClientNumber IN (SELECT ClientNumber FROM tblSystem WHERE SystemNum = ".$_SESSION['system'].")";
$result = mssql_query($query) ;
if($row = mssql_fetch_row($result)){
$clientName = $row[0];
if(is_numeric(strrpos(strtolower($clientName), '_p_')))



The following query selects only one client name as it should

SELECT DISTINCT ClientName
FROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461)



All I want is a case/if statement so if the client name has '_p_' in it, then return 3 otherwise 0



This provides close enough insight that I think I can offer some suggestions. First of all you really should not be using pass through like you are. You should instead use stored procedures for data retrieval. The advantages to this are many. First, your developers don't have to write their own sql (but they can still write their own procs if they are able). The bigger advantage is that you separate the data from the business logic. This means you could write a new front end for you application and not have to rewrite all of the data in addition.

For your specific query I think you can get rid of the subquery and joing to tblSystem instead. This code is untested because we don't have ddl but I think this should be close.


create procedure GetSomeNumber
(
@SystemNum int
) as

SELECT DISTINCT case when ClientName like '%_p_%' then 3 else 0 end as ReturnSomething
FROM tblClient c
join tblSystem s on c.ClientNumber = s.ClientNumber
WHERE s.SystemNum = @SystemNum



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
maxlezious
maxlezious
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 230
Thank you for your great advice Lynn, I will create a new stored procedure like you adviced.
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
Here's another approach you can try. The procedure depends on a simple iTVF to locate the letter locations which has the advantage of being able to find multiple instances within the same string being searched.

SAMPLE DATA



IF OBJECT_ID('tempdb..#ClientName') IS NOT NULL
DROP TABLE #ClientName

IF OBJECT_ID('tempdb..#SysRef') IS NOT NULL
DROP TABLE #SysRef

CREATE TABLE #ClientName (
[ID] INT NOT NULL
,[Name] NVARCHAR(100) NULL
,PRIMARY KEY([ID]))

INSERT INTO #ClientName
SELECT 1,'Jerry' UNION ALL
SELECT 2,'Andrew' UNION ALL
SELECT 3,'Apple' UNION ALL
SELECT 4,'Bates' UNION ALL
SELECT 5,'Doppler'

CREATE TABLE #SysRef (
[ID] INT NOT NULL
,[RefNum] NVARCHAR(100) NULL
,PRIMARY KEY([ID]))

INSERT INTO #SysRef (ID,RefNum)
SELECT 1,45678 UNION ALL
SELECT 2,54376 UNION ALL
SELECT 3,30420 UNION ALL
SELECT 4,47899 UNION ALL
SELECT 5,30421

SELECT * FROM #ClientName
SELECT * FROM #SysRef





INLINE TABLE FUNCTION TO FIND CHARS IN STRING



CREATE FUNCTION [dbo].[itvfFindPos]
(
@strInput VARCHAR(8000)
,@delimiter VARCHAR(5)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b
),
E4(N) AS (SELECT 1 FROM E2 a, E2 b
),
cteTally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
findchar (posnum,pos)
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY t.N) AS posnum
,CHARINDEX(@delimiter,@strInput,t.N) AS pos
FROM
cteTally t
WHERE
(SUBSTRING(@strInput,t.N,1) = @delimiter)
)
SELECT
posnum
,pos
FROM
findchar
WHERE
pos > 0
)
GO





SEARCH PROCEDURE



CREATE PROCEDURE dbo.FindSingleLetterInString
@SystemNum INT
,@LetterSearch CHAR(1)
,@ReturnVal INT
AS
BEGIN

SET NOCOUNT ON

SET @SystemNum = ISNULL(@SystemNum,0)
SET @LetterSearch = ISNULL(NULLIF(@LetterSearch,''),0)

SELECT DISTINCT
c.ID
,(CASE
WHEN pos IS NULL THEN 0
ELSE @ReturnVal
END) AS refID
,name
,refNum
FROM
#ClientName c
INNER JOIN
#SysRef s
ON c.ID = s.ID
OUTER APPLY
dbo.itvfFindPos(c.Name,@LetterSearch) fp
WHERE
(@SystemNum > 0 AND s.RefNum = @SystemNum)
OR @SystemNum = 0

END
GO




FINALLY...RUN SOME EXAMPLES



DECLARE @SystemNum INT
SET @SystemNum = 30420

EXEC dbo.FindSingleLetterInString @SystemNum,'p',3 --returns only names with 'p' that match the SystemNum and flags with '3'
EXEC dbo.FindSingleLetterInString NULL,'p',3 --returns ALL names with 'p' and flags with '3'
EXEC dbo.FindSingleLetterInString NULL,'r',9 --works the same with any char(1) value or output value





 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search