Comparing Concatenated Fields - SQL Server Forums

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

Transact-SQL (2000)

Comparing Concatenated Fields

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 10:21:37

Please let me start off by saying, I am very very new to SQL. I did a search on this and did not find anything that addressed this question. I have a very elementary question...

Is it possible to combine two fields and compare them to two fields that are combined in another table? If so, how? I tried to use the "&" to connect the two fields and it does not return the information I need. I then tried the "+" and that didn't work either. Couldn't find any info in the TOAD help regarding operators or concatenation.

Not sure if you will be able to use this, but here is the code I am using...

select
B.area,
case when (substr(B.ptn,1,3) + substr(B.ptn,4,3))=(N.nxx + N.npa) then N.SUB_MARKET_CODE end as NPA_Submkt,
B.ptn,
substr(B.ptn,1,3) NPA,
substr(B.ptn,4,3) NXX
from bso.units B, nor.npa_nxx N

Thank you so much for your assistance!

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 10:32:26

I don't think you can refer to aliases like that. Plus if you're refering to NPA and NXX (which are substr(B.ptn,1,3) and substr(B.ptn,4,3), respectivley), then your case statement will always be True.

If NXX and NPA are columns in in mor.npa_nxx table, then you need to mak sure they are defined as Char(3) or you may have trouble with the comparisson.

Also what if your Case is not True? You'll get Null back on that.

One last note, why are you aliasing columns in Units as Columns that exists in npa_nxx (I mean that's if they exists)

Can you post your DDL for the tables?

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 11:24:46

Thanks for your reply!

Not sure what you mean by "can't refer to alias like that". The only ailas I have is naming the CASE statment. Is that what you mean?

I did return a lot of NULLs and didn't understand why. So thanks for clearing up that issue.

Two tables. One table has a field for PTN and it needs to be parsed out to be able to compare them to the other table that already has NPA (area code) and NXX (first three numbers of the phone number) pased out. How would I do this? Or can it not be done?

The NPA and NXX fields in nor are numeric. How would I redefine them as CHAR?

I am unfamiliar with how to post the DDL for the tables and not sure I should, since it is company data.

Will you still be able to assist me?

Thanks!

*************************
Got some code from Rob. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 11:48:37

DDL (Data Definition Language) will not contain data. Just structure. If you go in to Enterprise Manager and go to the database you're in you can right click on a table and choose All Tasks Then Generate SQL Script.

One other small problem I noticed is that you're doing a join, but have to predicates to indicate how the join is to occur.

Also, I'm sure this debate has been had before, but why are the phone numbers stored as numeric?

Anyhow how about:


    SELECT B.area
	  , CASE WHEN substr(B.ptn,1,3) + substr(B.ptn,4,3)=
		      Convert(varchar(3),N.nxx) + Convert(varchar(3),N.npa) 
		 THEN N.SUB_MARKET_CODE
		 ELSE 'N/A'							 
	    END AS NPA_Submkt
	  , B.ptn 
	  , substr(B.ptn,1,3) AS NPA
	  , substr(B.ptn,4,3) AS NXX 
      FROM  bso.units B
INNER JOIN  nor.npa_nxx N
	ON B.<some key column> = N.<some key Column> 


-- But Thinking about this more you may want to be doing:


    SELECT  B.area
	  , N.SUB_MARKET_CODE AS NPA_Submkt
	  , B.ptn 
	  , substr(B.ptn,1,3) AS NPA
	  , substr(B.ptn,4,3) AS NXX 
      FROM  bso.units B
INNER JOIN  nor.npa_nxx N
	ON substr(B.ptn,1,3) + substr(B.ptn,4,3) = Convert(varchar(3),N.nxx) + Convert(varchar(3),N.npa)

-- Not sure exactly though

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 11:51:21

Oh, and btw "concatenated" numerics aren't really, you're just doing Math.

Declare @x Int, @y Int
Select @x = 1, @y = 1
Select @x + @y
--Does not return "11", but 2

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 12:17:20

Even though I am using SQL, I am doing this through TOAD and not SQL server. So, I don't have Enterprise Manager for this.

select
B.area,
N.SUB_MARKET_CODE NPA_Submkt,
B.ptn,
substr(B.ptn,1,3) NPA,
substr(B.ptn,4,3) NXX
from bso.units B inner join nor.npa_nxx N
on substr(B.ptn,1,3) + (B.ptn,4,3) = convert(varchar(3),n.nxx) + convert(varchar(3),n.npa)

This is failing. INNER is highlighted and the query won't run.

Any ideas?

*************************
Got some code from Rob. Can anyone help?

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 13:17:27

Finally got the query to run without any errors, but it did not return any data. I realize this is P_SQL and perhaps I have posted in the wrong place. Here is the code that worked...

select
B.area,
N.SUB_MARKET_CODE NPA_Submkt,
B.ptn,
substr(B.ptn,1,3) NPA,
substr(B.ptn,4,3) NXX

from bsonarview.nci_nar_units_btv B, norsnapadm.npa_nxx_lr N

where
substr(B.ptn,1,3) || substr(B.ptn,4,3) = cast(n.npa as varchar(3)) + cast(n.nxx as varchar(3))

If anyone can assist, it will be greatly appreciated!

*************************
Got some code from Rob. Can anyone help?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-21 : 16:47:02

Am I missing something?


Why not:
where 
substr(B.ptn,1,3) = convert(varchar(3), n.npa)
AND
substr(B.ptn,4,3) = convert(varchat(3), n.nxx)

???

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 16:56:37

Nope, that'll work...can't see the forest for the trees sometime

I think her bigger problem is that sh'es working with Oracle...

What is TOAD?

Brett

8-)

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 19:39:10

quote:

Am I missing something?


Why not:
where 
substr(B.ptn,1,3) = convert(varchar(3), n.npa)
AND
substr(B.ptn,4,3) = convert(varchat(3), n.nxx)

???

- Jeff

Jeff:

Thanks, but that will return too much data. An Area Code (NPA) can belong to more than one Area and the NXX can as well. That was why I wanted to parse the one field and concatenate the other.

TOAD is an application that allows you to query an Oracle database, directly. Nothing wrong with Oracle. There are differences between T-SQL and P-SQL, but I have been able to convert my T-SQL queries with some slight syntax changes.

Thank you all for your assistance with this. If I do find a solution, I will post it here. Just in case anyone cares!

*************************
Got some code from Rob. Can anyone help?

Edited by - ledell on 04/21/2003 19:56:50

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-21 : 19:49:27

Just so that you know, this is a SQL Server site. But usually we can help you with Oracle too if it's just SQL questions you are asking.

Tara

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2003-04-21 : 19:55:45

quote:

Just so that you know, this is a SQL Server site. But usually we can help you with Oracle too if it's just SQL questions you are asking.

Tara

Thank you Tara. I appreciate you reminding me of that. The advice that I received was helpful

*************************
Got some code from Rob. Can anyone help?

Edited by - ledell on 04/21/2003 19:58:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources