Solved

Composite column idea needed

Posted on 2009-04-02
11
224 Views
Last Modified: 2012-05-06
I have an identity field that increments , for example, 1234, 1235, 1236.

I have another column that has country code. When I pull up this number, I can concat the country code to the number: UK1234, US1235

So, 2 coulmns: one country code, one number.

**Now, the user enters UK1234 (not pulling up data, but searching for data). I'm thinking about parsing out the string section and just do a search for the number. Then see if that number matches the country-code.

Is there a better way of doing this?
0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 24052503
If your always using the identify field as the "number" there is no way it can be duplicated.  

if the user is always inputting the 6 character Country code + 4 digit ID i'd just search on the 4 digit ID
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24052506
I assume you mean parsing the string the user enters and then searching on the two different columns independently, right?
0
 
LVL 7

Author Comment

by:Camillia
ID: 24052819
Auric1983 - that wont work. For example, we have 2 tabs on our screen. One for US, one for UK.
We have  numbers : US1234 and UK5678.  User goes to tab US and enters 5678 (this belongs to UK tho) and the wrong country code row is pulled up.

chapmandew: you're right.But my coworker says that's a slow search. He says maybe I can do a "calculated" search. Or even create a new column to hold US1234 but he says that's extra storage.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Camillia
ID: 24052868
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24052981
Is it two seperate queries that run each tab Farzadw?

If so you could just hardcode the country code for each tab

tab1 : select * from table where customerid=5678 and Country='UK'
tab2: select * from table where customerid=1234 and Country='US'
0
 
LVL 7

Author Comment

by:Camillia
ID: 24052983
but how would a "calculated" column work in doing a search?? so, user enters UK1234. I have a calculated column..then what??
0
 
LVL 16

Accepted Solution

by:
Auric1983 earned 500 total points
ID: 24052994
select * from table having (countrycode+identity) = 'UK1234'

keep in mind if your Country code field is greater than 2 characters and is a CHAR datatype you will need to rtrim() the field to remove any whitespace at the end.
0
 
LVL 7

Author Comment

by:Camillia
ID: 24053042
Hardcoding is out of the question.

>> select * from table having (countrycode+identity) = 'UK1234'
so have a calculated column and that's how i do a search when user enters UK1234??
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24053053
sure, if both your columns are indexed it shouldn't add that much overhead to your search
0
 
LVL 7

Author Comment

by:Camillia
ID: 24053114
Thanks..

 Chap: any other thoughts?
0
 
LVL 7

Author Comment

by:Camillia
ID: 24055498
how do I do the computed column formual??

 This is the sql statement;
 select table1.code + cast (table2.number as varchar(20))
  from table1 inner join table2 on table1.id = table2.sid

I tried that SQL statement as the computed field (note the computed field is Identity) but doesnt work.

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question