What a Nightmare these filters...

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 2008 Forums

Transact-SQL (2008)

What a Nightmare these filters...

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2011-10-12 : 11:30:11

I have a filter table that some idiot, don't know who, has setup with filters in it eg <> or &= and I need to use these in sql but not sure if this is possible or not, and if it is, is it going to be a complete nightmare to workout...

eg the table
Description StyleGroup ItemCode
TROUSERS 101
JUMPERS 102
SHIRTS 105 <>5647
SHIRTS COTTON 105 =5647
JACKETS 106
SUITS 108 <>1245&<>5465
SUITS VELVET 108 =1245&=5465

I need to uddate another table with the descriptions linking on StyleGroup, but then also on ItemCode, wondering if this should be
in 2 steps eg

-- first update
UPDATE [TheBud2]
SET [TheBud2].[Description] = b.[Description]
FROM [TheBud] b
WHERE [TheBud2].[StyleGroup] = b.[StyleGroup]

then a second to workout the styles

It needs to look at the item code filter so
if StyleGroup = 105 but the ItemCode in update table = 5647 don't put SHIRTS becaus eof this filter <>5647.
But below line if StyleGroup = 105 and itemcode =5647 then put SHIRTS COTTON.

eg table would look something like this at the end:

Description StyleGroup ItemCode
TROUSERS 101 1099
SHIRTS COTTON 105 5647
SHIRTS 105 1004
TROUSERS 101 1099
JUMPERS 102 1004
SUITS 108 1003
SUITS VELVET 108 1245
SUITS VELVET 108 5465
SUITS 108 1002

Or is can;t do it with a sql maybe split the filters and date out into
another temp table and then update joining to this table.

ARRGGHHH I'm going mad with this thing, sat here for hours trying to work it out.

Any help would be great

Cheers,

Roger

Create tables and test data:

--Tables
CREATE TABLE [dbo].[TheBud](
[Description] [varchar](30) NOT NULL,
[StyleGroup] [varchar](30) NOT NULL,
[ItemCode] [varchar](20) NOT NULL
)
GO
CREATE TABLE [dbo].[TheBud2](
[Description] [varchar](30) NOT NULL,
[StyleGroup] [varchar](30) NOT NULL,
[ItemCode] [varchar](20) NOT NULL
)
GO

--Insert into Table 1
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('TROUSERS','101','')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('JUMPERS','102','')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('SHIRTS','105','<>5647')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('SHIRTS COTTON','105','=5647')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('JACKETS','106','')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('SUITS','108','<>1245&<>5465')
GO
INSERT INTO [TheBud]
([Description],[StyleGroup],[ItemCode])
VALUES('SUITS VELVET','108','=1245&=5465')
GO

-- Insert into Table 2
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','101','1099')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','105','5647')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','105','1004')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','108','1003')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','108','1245')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','108','5465')
GO
INSERT INTO [TheBud2]
([Description],[StyleGroup],[ItemCode])
VALUES('','108','1002')
GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-12 : 11:53:20

Do you know how to use dynamic sql?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2011-10-12 : 11:57:17

dynamic sql not really, is this what is needed.
I've setup a few stored procs with variables...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-13 : 09:39:17

Find out whoever decided to store the data this way, then lock them in a room with Joe Celko for a few hours. Justice will be served.
Otherwise, not sure if Dynamic SQL would be needed.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources