IF statement with substring query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IF statement with substring query

Author

Topic

trwagner1
Starting Member

2 Posts

Posted - 2005-07-20 : 18:33:40

I'm having a problem doing a SQL query in the query analyzer and was hoping I might get some suggestions.

What I'm trying to do is build a table in ASP.net. I want to set up a view that will feed the data to the datagrid in asp.net.

One field in my table contains variable information on a serial number.

Example: BL894AI7125, 89AI6534

I would like to test the string for the instance of BL. If it's there, then only return the last 8 characters in the string. If it's not there, then just return the string. In addition, only records of items marked "Active" should be returned.

I've set up a SQL query that I thought would work, but I always get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here's the script I'm using.

USE COINVENTORY
IF (SELECT SUBSTRING([SERIALNUM], 1, 2) FROM dbo.[INVENTORY]) = 'BL'

BEGIN
SELECT TOP 200 [Date], [Time], LEFT([SERIALNUM], 3)
FROM dbo.[INVENTORY]
WHERE ([Inv-Status-Type] = 'ACTIVE')
ORDER BY [Date] DESC
END

ELSE
Begin

SELECT TOP 200 [Date], [Time], [SERIALNUM]
FROM dbo.[INVENTORY]
WHERE ([Inv-Status-Type] = 'ACTIVE')
ORDER BY [Date] DESC
END

Thanks

Ted

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-20 : 19:02:32

Ted,

It doesn't like the SELECT being a part of the IF. You can do the following:

DECLARE @serial VARCHAR(2)
SELECT @serial = SUBSTRING([SERIALNUM], 1, 2) FROM dbo.[INVENTORY]
IF @serial = 'BL'
   blah blah blah...

HTH,

Tim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 19:05:46

It doesn't like the fact that your SELECT statement in the IF is returning more than one row and you are comparing them to one value 'BL'.

You can use CASE instead:



SELECT TOP 200 
	[Date], 
	[Time], 
	SERIALNUM = 
		CASE 
			WHEN SUBSTRING([SERIALNUM], 1, 2) = 'BL' THEN LEFT([SERIALNUM], 3) 
			ELSE SERIALNUM 
		END
FROM dbo.[INVENTORY]
WHERE ([Inv-Status-Type] = 'ACTIVE')
ORDER BY [Date] DESC

Tara

trwagner1
Starting Member

2 Posts

Posted - 2005-07-20 : 19:10:20

Thanks to both of you. I see now.

Thank you

Ted

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 19:12:08

Also if you are returning this information to the client, it would be best to this in the presentation layer. Strip out the BL from the client app when found. The less formatting SQL Server has to do the better.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources