May 3, 2013 at 5:45 am
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
May 3, 2013 at 7:25 am
maxlezious (5/3/2013)
Hi, I have a select statement and i am selecting the results of the select statement as @clientNameNow 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?
May 3, 2013 at 7:35 am
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
May 3, 2013 at 7:43 am
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
May 3, 2013 at 7:47 am
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 ClientNameFROM 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2013 at 7:52 am
Thank you for your great advice Lynn, I will create a new stored procedure like you adviced.
May 4, 2013 at 1:48 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply