Best Advice

I have a table containing 65 fields. One of the fields is a varchar(6) field
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am sing in this case ane you '111111' the
client number
Thanks
"Aaron [SQL Server MVP]" wrote:

> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am sing in this case ane you '111111' the
> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a