SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 if...else not working
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

gongxia649
So Suave

Azores
344 Posts

Posted - 09/20/2006 :  17:03:06  Show Profile  Visit gongxia649's Homepage  Reply with Quote
declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'

select * from @table

if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))

print 'address starts with a number'
else
print 'address starts with a letter'



i want to write a code that prints a msg if the string starts with a number else print another msg.

Edited by - gongxia649 on 09/20/2006 17:05:25

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/20/2006 :  17:16:25  Show Profile  Reply with Quote
Looks like you're confusing IF with CASE. Check these out:

declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'


select * from @table

select ad_str1
	,case
		when substring(ad_str1, 1, 1) like '[0-9]' 
		then 'address starts with a number'
		else 'address starts with a letter'
		end
from	@table

declare @ad_str1 varchar(20)
select top 1 @ad_str1 = ad_str1 from @table

if substring(@ad_str1,1,1) like '[0-9]'
	print @ad_str1 + ': address starts with a number'
else
	print @ad_str1 + ': address starts with a letter'



Be One with the Optimizer
TG
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 09/20/2006 :  17:20:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
Not much point in using SubString, in fact it's less optimal than:

select ad_str1
,case
when ad_str1 like '[0-9]%'
then 'address starts with a number'
else 'address starts with a letter'
end
from @table


This is semantically equivalent and it avoids the non-sargable SubString function, on a large table with an indexed column it should be dramatically faster.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/20/2006 :  21:03:54  Show Profile  Visit gongxia649's Homepage  Reply with Quote
TG, why doesn't run without storing the field in a variable?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/20/2006 :  22:12:38  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

TG, why doesn't run without storing the field in a variable?



IF expects to evaluate a boolean expression (true or false). Your "exression":
if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))
does not result in a boolean expression but rather a result set. Also, you've got an extra parenthises at the end.

Be One with the Optimizer
TG
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/21/2006 :  16:42:15  Show Profile  Visit gongxia649's Homepage  Reply with Quote

truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable




declare @ad_str1 varchar(20)
select @ad_str1 = ad_str1 from dbo.TempAddressParsingTable

if substring(@ad_str1,1,1) like '[a-z]'
exec proc1
else
exec proc2

The code should execute proc1 if the field ad_str1 first letter is a letter, otherwise exec proc2.
The code runs fine, but it's not doing what the proc are suppose to do. But if i run the SP alone, it will do what is suppose to do. Why is this happening? How can i fix it?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/21/2006 :  16:49:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by gongxia649

Why is this happening? How can i fix it?
You could start learning basic algorithms.
select @currentvoter = min(id_voter),
       @maxvoter = max(id_voter)
from   tempaddressparsingtable

while @currentvoter <= @maxvoter
  begin
    select @ad = ad_str
    from   tempaddressparsingtable
    where  id_voter = @currentvoter

    if @ad like '[a-z]%'
       exec proc1 @ad
    else
       exec proc2 @ad

    select @currentvoter = min(id_voter)
    from   tempaddressparsingtable
    where  id_voter > @currentvoter
  end

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/21/2006 16:51:18
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  08:21:57  Show Profile  Visit gongxia649's Homepage  Reply with Quote
truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable



declare @currentvoter varchar(20)
declare @maxvoter varchar(20)
declare @ad varchar(20)


select @currentvoter = min(id_voter) from tempaddressparsingtable
select @maxvoter = max(id_voter)from tempaddressparsingtable

while @currentvoter <= @maxvoter
begin
select @ad = substring(ad_str1, 1,1)
from dbo.tempAddressParsingtable
where id_voter = @currentvoter

if substring(@ad, 1,1) like '[a-z]%'

exec usp_Trio_AddressParsing1
else
exec usp_NDS_AddressParsing1

select @currentvoter = min(id_voter)
from dbo.tempaddressparsingtable
where id_voter > @currentvoter
end

I don't know what i'm doing wrong. When i run the code, the SP are not doing the job. But if I exec them alone, they work fine.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  08:37:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why, Oh Why, are you changing the code and logic in the suggestions?
And you are totally clueless why the code doesn't work after your changes? I am a peaceful person, but you are starting to make me dislike you.

Why do you use SUBSTRING twice? Do you not trust us who tries to help you?
If the stored procedures are not "doing the job", have you thought of the possibility that my suggestion is right and your stored procedures has errors when executed?

Always, when debugging, replace code to calling other stored procedures with a PRINT statement, like the one below. This way you will be convinced that my suggestion is good enough and the appropriate stored procedure is called.

Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?
SET NOCOUNT ON

DECLARE @TAPT TABLE (id_voter VARCHAR(2), ad_num VARCHAR(2), ad_str1 VARCHAR(20))

INSERT	@TAPT
SELECT '1', null, '102 OLD COUNTY ROAD' UNION ALL
SELECT '21', null, '1 UNTY ROAD' UNION ALL
SELECT '31', null, 'route 1' UNION ALL
SELECT '14', null, 'pob 23' UNION ALL
SELECT '15', null, 'database apt3' UNION ALL
SELECT '2','30', 'SHAW ROAD apt #9'

SELECT * FROM @TAPT

DECLARE	@CurrentVoter VARCHAR(2),
	@MaxVoter VARCHAR(2),
	@ad VARCHAR(20)

SELECT	@CurrentVoter = MIN(id_voter),
	@MaxVoter = MAX(id_voter)
FROM	@TAPT

WHILE @CurrentVoter <= @MaxVoter
	BEGIN
		SELECT	@ad = ad_str1
		FROM	@TAPT
		WHERE	id_voter = @CurrentVoter

		IF @ad LIKE '[a-z]%'
			--EXEC usp_Trio_AddressParsing1
			PRINT 'Address ' +  @ad + ' does start with a character!'
		ELSE
			--EXEC usp_NDS_AddressParsing1
			PRINT 'Address ' +  @ad + ' does not start with a character!'

		SELECT	@CurrentVoter = MIN(id_voter)
		FROM	@TAPT
		WHERE	id_voter > @CurrentVoter
END

Peter Larsson
Helsingborg, Sweden

EDIT: Spelling errors

Edited by - SwePeso on 09/22/2006 08:59:34
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  08:46:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Output from the code above gives following
Address 102 OLD COUNTY ROAD does not start with a character!
Address pob 23 does start with a character!
Address database apt3 does start with a character!
Address SHAW ROAD apt #9 does start with a character!
Address 1 UNTY ROAD does not start with a character!
Address route 1 does start with a character!
As you can the, the result is correct. Where is the error, you think?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  08:57:05  Show Profile  Visit gongxia649's Homepage  Reply with Quote
peso, i didnt see the parameter next to the SP. I have to do some changes now. But my SP doesnt receive any parameter.


Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?

I thought the SP know how to work with them after the IF statement has ran.

Edited by - gongxia649 on 09/22/2006 09:01:53
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  09:06:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by gongxia649

I thought the SP know how to work with them after the IF statement has ran.
I am aware you thought so and didn't know, that's why I provided a suggestion for a correct execution to you.

No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/22/2006 09:08:51
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  09:27:23  Show Profile  Visit gongxia649's Homepage  Reply with Quote
Peso,
"No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure."

Does that mean I have to go to my SP and change the whole thing?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  09:27:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How would I know? I don't have a crystal ball.

It all depends on what your stored procedures does!

If the case is that your stored procedure usp_Trio_AddressParsing1 already knows how to deal with addresses that starts with characters (all addresses in the table, not just one row) then you might add a WHERE clause in the SP, as

where address like '[a-z]%'


If the case is that your stored procedure usp_NDS_AddressParsing1 already knows how to deal with addresses (all addresses in the table, not just one row) that start with a non-character, then you might add a WHERE cluse in the SP, as

where address like '[^a-z]%'

or

where address like '[0-9]%'


Then all you need is two calls to either SP, and this topic is obselete to start with.


We can't tell you what to do. We give valuable suggestions. Our suggestions are based on the information you provide us.
Not once in this entire topic have you told us what the two stores procedures do. So we think you know what you are doing with the loop and all, as in the original posting.

Maybe there were no need for a loop at all!?
It all depends on what your stored procedures does!


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/22/2006 09:38:32
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/22/2006 :  09:47:48  Show Profile  Reply with Quote
IF 1<>1 THEN PRINT 'NOT WORKING' ELSE PRINT 'WORKING'



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  09:48:24  Show Profile  Visit gongxia649's Homepage  Reply with Quote
usp_Trio_AddressParsing1 = parse the address that no number in front
usp_NDS_AddressParsing1 = parse the address that has numbers in front

i added "where address like '[0-9]%'" and "where address like '[a-z]%'" to all the rows in my sp. But still doesnt' do that job.

If i run the SP alone. e.g. i run only usp_Trio_AddressParsing1, it will parse the address
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  09:50:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
the address

or

the addresses


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  09:55:29  Show Profile  Visit gongxia649's Homepage  Reply with Quote
peso, addresses

you know what, i think its working.
I dont even neeed to use IF cause the SP knows when to parse the addresses with number in front and without a number in front.


so, i just go


truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable
usp_Trio_AddressParsing1
usp_NDS_AddressParsing1


and bang it works.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  09:57:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I know.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/22/2006 :  09:59:14  Show Profile  Visit gongxia649's Homepage  Reply with Quote
sorry agian to wasted your time.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 09/22/2006 :  10:00:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well, it might not be a wate of time, if you have improved you T-SQL skills and learned something from this mistakes and never do the same mistake again.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/22/2006 10:01:34
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000