a problem with - 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

SQL Server Development (2000)

a problem with

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2003-12-07 : 21:45:49

Hi Folks,

I have a phone card table with 3 types of cards. Type 1: for example 'AB100000000', this card length is 10 and the first 2 characters are card name. the following 2 digits are face value. and the rest are digits; Type 2: such as 'ABCD0100000000', this card length is 14 and the first 4 characters are card name, and then next 3 digits are face value. and rest are 7 digits; Type 3, for example :'ABCDEF0100000000', with total length 16 and first 6 characters as card name and next 3 digits are face value.

My question is : after you get the first and last serial number of a bunch of cards(such as from AB10000000 to AB10199999). how could you select them out in program? Before the Type 3 card in the card table, i was using script like :
Select * from cardtable where substring(serialnumber, 1, 4) = 'AB10'and substring(serialnumber, 5, 6) between 0 and 199999
to get the result. After I created the Type 3 card into cardtable, the above script doesn't work any more. because the "substring(serialnumber, 5, 6) between 0 and 199999" part will cause problem when it meets Type 3 cards in the table and couldn't convert 'EF010..' into integer.

Any solution to solve this dilemma?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-07 : 22:34:07

Why are you storing 3 separate data types into one data type?

While ehorn's solution will give you the ability to quickly get the "type" of card in your design, it will not stop data corruption unless you write about 5 different RI constraints. That fact alone should cause alarm bells.

The query isn't the problem your design is.

If you are not interested in changing design, have a look at the CASE statement.

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 22:38:53

David,

Agreed, which is why I deleted my reply suggesting adding a typeid of tinyint to the card table.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-07 : 22:41:30

I understand ehorn. Sometimes you just want to give a solution that allows them to keep "hacking" away, and sometimes you put on your DB nazi hat and get serious.

DavidM

"SQL-3 is an abomination.."

bigbelly
Starting Member

39 Posts

Posted - 2003-12-07 : 23:22:22

This is reply to ehorn's initial reply:

That was exactly what I did. I have a cardtype table with cardtypeID
as PK and it's the foreign key at card table. And in the card type table, i have all what you recommended information. But still when u use

select * from card where substring(serialnumber, 1, 4) = 'AB10'
and substring(serialnumber, 5, 6) between 0 and 199999

it will have problem. I think because the above script HAVE to scan the whole table. So when it encounters cards like 'ABCDEF010....', the above script will have problem.

quote:

then your query becomes
SELECT <columns>
FROM cardtable
WHERE card_type = <typeid>

The result I want is not only return all cards of a particular type, it's according to what user inputted "From" and "To" to search and do lots of insert and update. But first of all, i need to find out whether those cards are valid according to business rule.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-07 : 23:33:21

bigbelly,

Here is 2 different ways...


select * 
from card 
where substring(serialnumber, 1, 4) = 'AB10'
and (ISNUMERIC(substring(serialnumber, 5, 6)) = 1 and substring(serialnumber, 5, 6) between 0 and 199999) --Add logic for not numeric

select * 
from card 
where substring(serialnumber, 1, 4) = 'AB10'
and CASE ISNUMERIC(substring(serialnumber, 5, 6))  
	WHEN 1 THEN CAST(substring(serialnumber, 5, 6) AS INT)  
	ELSE 2 END BETWEEN 0 AND 199999

EDIT: Closing Code tag

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 23:33:50

quote:
Originally posted by byrmol

I understand ehorn. Sometimes you just want to give a solution that allows them to keep "hacking" away, and sometimes you put on your DB nazi hat and get serious.

But it does raise a question for me.
Given the following table design:

create table cardtype
(
typeid tinyint CONSTRAINT PK_typeid PRIMARY KEY CLUSTERED,
cardname varchar(10),
facevalue varchar(10)
)

insert into cardtype
select 1,'AB','01'
union select 2,'ABCD','010'
union select 3,'ABCDEF','010'

create table cardtable
(
typeid tinyint FOREIGN KEY (typeid) REFERENCES cardtype(typeid),
cardnumber int PRIMARY KEY CLUSTERED
)

How could you insure the cardnumbers stay within the defined domain of the given types

type1 between 0 and 999999
type2 between 0 and 999999
type3 between 0 and 9999999

I am not aware of a composite CHECK constraint so how would this best be handled?
INSTEAD OF trigger, a different table design, etc... ?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-07 : 23:44:58

If you are using SQL 2K then User Defined Functions are the answer.


create table cardtype
(
typeid tinyint CONSTRAINT PK_typeid PRIMARY KEY CLUSTERED,
cardname varchar(10),
facevalue varchar(10),
StartRange int NOT NULL,
EndRange INT NOT NULL, CHECK (EndRange > StartRange)
)
GO
CREATE FUNCTION IsValidCardType
(
@typeid  TINYINT,
@CardNumber INT
)
RETURNS BIT
AS
BEGIN
DECLARE @Bit BIT
SET @Bit = 0
IF EXISTS(SELECT 1 FROM CardType WHERE TypeID = @Typeid 
		AND @CardNumber BETWEEN StartRange AND EndRange)
	SET @Bit =1
RETURN @Bit
END
GO

create table cardtable
(
typeid tinyint FOREIGN KEY (typeid) REFERENCES cardtype(typeid),
cardnumber int PRIMARY KEY CLUSTERED, CHECK (dbo.IsValidCardType(typeid, cardnumber) = 1)
)
GO
insert into cardtype
select 1,'AB','01', 0, 99999
union select 2,'ABCD','010', 0, 99999
union select 3,'ABCDEF','010', 0, 999999

--Breaks Constraint
Insert Cardtable values (1, 8888888)
--OK
Insert Cardtable values (1, 88888)

DavidM

"SQL-3 is an abomination.."

bigbelly
Starting Member

39 Posts

Posted - 2003-12-07 : 23:49:14

quote:
Originally posted by byrmol

Why are you storing 3 separate data types into one data type?

==> probably I didn't describ clearly in my first post. The story is like this:

In the card table, SerialNumber is PK and it's varchar. and there is a foreign key CardTypeID which refers to CardType Table, which has several columns. like cardname, facevalue, cardlength....
When the database was designed in the first place, we only have several card type but all with the same format, like 'AB10000000'or 'BC50123456'.

As business expanded, they created another format. which is like 'ABCD0101234567'. which is a 14 characters length and its first 4 characters are cardname and following 3 digits represent facevalue.

And now we are taking over several other companies. their card format are all different. So we have to put them into our system according to our rule(which is have a cardname and facevalue). For example a card initially only have 10 digits as its serialnumber. If now we want to change it to 19 characters by adding a card name(such as ABCDEF) and face value(such as 010) in front of it, that's where i start to have problem with my initial code.

Idealy what i want is allow them to create whatever cardname and face value they like. and the whole system should be able to handle any kind of cases.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 23:51:27

quote:
If you are using SQL 2K then User Defined Functions are the answer.

So it behaves as a composite CHECK constraint.

Very cool!!

Thanks David,

Jay

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-07 : 23:53:51

bigbelly,

I know how that songs goes...

There is almost always a trade off between flexibility and data integrity. Take your pick and beware the risks!

DavidM

"SQL-3 is an abomination.."

bigbelly
Starting Member

39 Posts

Posted - 2003-12-08 : 00:45:03

Thanks a lot for all u guys contribution.

David, what you recommended
select *
from card
where substring(serialnumber, 1, 4) = 'AB10'
and CASE ISNUMERIC(substring(serialnumber, 5, 6))
WHEN 1 THEN CAST(substring(serialnumber, 5, 6) AS INT)
ELSE 2 END BETWEEN 0 AND 199999
is really a cool one. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources