Default value in a table
-
I'm having a bit of a problem.
I have a table that has a field for phone numbers in it. What I'm wanting to do is have a different field in the same record use part of that phone number for sorting. Therefore I'm trying to have the field "Sort" have a default value of right(phone,2) and am not getting EM to like the way I'm trying to enter this. Any ideas on how best to do this so that I'm not having to run a query afterwards to set this value, and instead can have it automatically do it as data is entered.
-
What is the datatype for your phone# ?! If you only want to sort on the last 2 digits then you can do that without having an additional sort field....(what datatype is the sort field ?!)....
**ASCII stupid question, get a stupid ANSI !!!** -
Sample data is definitly required here... And why would you want to sort on the last 2 digits?? are those the extension of the phone number?
If yes, then I strongly suggest you move that info to its own column and simple sort on that.
-
Phone is varchar(10) and Sort is an int value. (9015551212 would be a sample) with Sort being 12.
I'm talking doing it in Enterprise manager although I can always create the table via T-SQL.
Also, the reason I do it this way is that I work for a Telemarketing company. If we sort across a state with the last 2 digits of someone's phone number, I can reasonably assume I'm getting a very random sampling of an area against the entire state versus trying to call in any type of sequential order.
-
Select top 25 percent Telephone from dbo.YourTable order by NewId() will do the job just fine.
-
Also here's and exemple of select the top pourcent from a group... not too sure it would cope well with a random sampling though :
SELECT O.XType
--, count(*) AS TotalHits_Found
, O.name
, (SELECT CEILING(COUNT(*) * 0.9) FROM dbo.SysObjects O4 WHERE O4.XType = O.XType) as [90%]
, (SELECT COUNT(*) FROM dbo.SysObjects O5 WHERE O5.XType = O.XType) AS [100%]
FROM dbo.SysObjects O
WHERE EXISTS (SELECT * FROM dbo.SysObjects O2 WHERE O2.XType = O.XType AND O2.id = O.id and O2.id IN (SELECT TOP 90 PERCENT id FROM dbo.SysObjects O3 WHERE O3.XType = O.XType ORDER BY O3.id))
--GROUP BY O.XType
ORDER BY O.XType
, O.Name
-
Remi,
I think it's getting way off topic for what I need. I can sort and get the leads and such I'm needing fairly easily without creating a "long" SQL statement such as what you have.
I'm simply trying to be the "lazy dba" and set up the table such that it automatically populates a field with the appropriate data rather than me having to go in and do a second pass through the table to populate the field.
If EM or SQL doesn't lend itself to this easily, then I can absolutely do it with the second pass method, I was just hoping that SQL would be on-par with MS Access (I've done this back in Access when I wasn't dealing with 20M records).
-
Then this is your friend :
Select top 25 percent Telephone from dbo.YourTable order by NewId()
Can't be simpler than that.
Anything wrong with that?
-
Do you mean - you want your "sort" field to be automtically populated with last 2 digits of phone# ?!
Then (in EM) - under "formula" for sort field in table design, you say RIGHT(phone#, 2)....
**ASCII stupid question, get a stupid ANSI !!!** -
Remi - "pourcent" ?!?! tsk! tsk! so full of typos today...<;-)
**ASCII stupid question, get a stupid ANSI !!!** -
You try writing things in 3 different languages without making a translation mistake once in a while... The strange thing is that it checked out without error in QA (errors only where running the statement).
Thanx for pointing it out.
-
Also speaking about typos... : "automtically "??????
-
what's the third language ?!
I'll be happy to point out any more spelling errors if you want (I notice them all the time ...I only keep quiet 'cos that's the way I am)....
<:-)
**ASCII stupid question, get a stupid ANSI !!!** -
Il'l trie two bee moer caerfule
. -
thgamble1, did you intent to index the computed column so that you have a quicker range search?
You could create index on the computed column and the state so that the sampling can be returned much faster.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply