Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with CASE Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 5:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:02 AM
Points: 25, Visits: 69
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

Post #1449149
Posted Friday, May 3, 2013 7:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 20,734, Visits: 32,498
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?



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)
Post #1449190
Posted Friday, May 3, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:02 AM
Points: 25, Visits: 69
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

Post #1449196
Posted Friday, May 3, 2013 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:02 AM
Points: 25, Visits: 69
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

Post #1449203
Posted Friday, May 3, 2013 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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)
Post #1449206
Posted Friday, May 3, 2013 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:02 AM
Points: 25, Visits: 69
Thank you for your great advice Lynn, I will create a new stored procedure like you adviced.
Post #1449210
Posted Saturday, May 4, 2013 1:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



 
Post #1449460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse