replace <> in query - 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 2008 Forums

Transact-SQL (2008)

replace <> in query

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-12 : 10:34:09

[code]
select *
FROM VTEXTERNAL.dbo.MOVIE M
RIGHT JOIN (SELECT Film_strCode,MAX(Person_strCode) as Person_strCode FROM VISTAHO.dbo.tblFilmPerson WHERE FPerson_strType='D' GROUP BY Film_strCode) FP
ON M.MOV_CODE=RIGHT(FP.Film_strCode,6) COLLATE Latin1_General_CS_AS
LEFT JOIN VISTAHO.dbo.tblPerson P
ON P.Person_strCode = FP.Person_strCode
LEFT JOIN (SELECT PRM_CODE,PRM_DESC
FROM VEXTERNAL.dbo.PARAMETRIC
WHERE PRM_CODE LIKE '07%'
) PAR
ON PAR.PRM_DESC = ltrim(isnull(P.Person_strFirstName,'') + ' ' + P.Person_strLastName) COLLATE Latin1_General_CS_AS
WHERE (PRM_CODE<>MOV_DIRECTOR
AND
PRM_CODE IS NOT NULL) OR (MOV_DIRECTOR IS NULL AND PRM_CODE IS NOT NULL)
AND MOV_CODE IS NOT NULL
[/code]

"WHERE (PRM_CODE<>MOV_DIRECTOR "
This will kick the query execution time from 1 second to 34 seconds. I am trying to change it with not exists or EXCEPT or a left join and then null check.
I am having trouble with the syntax, can anyone convert "(PRM_CODE<>MOV_DIRECTOR " to any of teh above?
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 08:52:11

please post some sample data.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 10:06:26

Hi. what i would like is an alternate to "<>" but i can post everything anyhow...


USE [VT_External]
GO

/****** Object:  Table [dbo].[MOVIE]    Script Date: 13/2/2015 5:00:17 µµ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MOVIE](
	[MOV_CODE] [dbo].[D_CODEMOV] NOT NULL,
	[MOV_TITLEEN] [dbo].[D_DESCL] NOT NULL,
	[MOV_TITLEGR] [dbo].[D_DESCL] NOT NULL,
	[MOV_TITLEPUB] [dbo].[D_DESCS] NOT NULL,
	[MOV_CATEGORY] [dbo].[D_CODEPRM] NULL,
	[MOV_GROUP] [dbo].[D_CODEPRM] NULL,
	[MOV_RATING] [dbo].[D_CODEPRM] NULL,
	[MOV_DURMOV] [dbo].[D_INTEGER] NULL,
	[MOV_DURADV] [dbo].[D_INTEGER] NULL,
	[MOV_DIRECTOR] [dbo].[D_CODEPRM] NULL,
	[MOV_PRODUCER] [dbo].[D_CODEPRM] NULL,
	[MOV_DISTRIBUTOR] [dbo].[D_CODEPRM] NULL,
	[MOV_PRIORITY] [dbo].[D_INTEGER] NULL,
	[MOV_SYNOPSIS] [dbo].[D_NOTES] NULL,
	[MOV_RELDATE] [dbo].[D_DATE] NULL,
 CONSTRAINT [PK_MOVIE] PRIMARY KEY CLUSTERED 
(
	[MOV_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [VISTAHO]
GO

/****** Object:  Table [dbo].[tblPerson]    Script Date: 13/2/2015 5:03:08 µµ ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[tblPerson](
	[Person_strCode] [varchar](10) NOT NULL,
	[Person_strFirstName] [nvarchar](30) NULL,
	[Person_strLastName] [nvarchar](30) NOT NULL,
	[Person_strURLToDetails] [varchar](255) NULL,
	[Person_strURLToPicture] [varchar](255) NULL,
	[Person_dtmModifiedDate] [datetime] NOT NULL,
	[lFilmDirectorId] [int] NULL,
	[lFilmStarId] [int] NULL,
 CONSTRAINT [PK_tblPerson] PRIMARY KEY NONCLUSTERED 
(
	[Person_strCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblPerson] ADD  DEFAULT (getdate()) FOR [Person_dtmModifiedDate]
GO


USE [VISTAHO]
GO

/****** Object:  Table [dbo].[tblFilmPerson]    Script Date: 13/2/2015 5:02:14 µµ ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[tblFilmPerson](
	[Film_strCode] [char](10) NOT NULL,
	[Person_strCode] [varchar](10) NOT NULL,
	[FPerson_strType] [varchar](1) NOT NULL,
	[FPerson_dtmModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tblFilmPerson] PRIMARY KEY NONCLUSTERED 
(
	[Film_strCode] ASC,
	[Person_strCode] ASC,
	[FPerson_strType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblFilmPerson] ADD  DEFAULT (getdate()) FOR [FPerson_dtmModifiedDate]
GO

ALTER TABLE [dbo].[tblFilmPerson]  WITH CHECK ADD  CONSTRAINT [fkFilm_FilmPerson] FOREIGN KEY([Film_strCode])
REFERENCES [dbo].[tblFilm] ([Film_strCode])
GO

ALTER TABLE [dbo].[tblFilmPerson] CHECK CONSTRAINT [fkFilm_FilmPerson]
GO

ALTER TABLE [dbo].[tblFilmPerson]  WITH CHECK ADD  CONSTRAINT [fkPerson_FilmPerson] FOREIGN KEY([Person_strCode])
REFERENCES [dbo].[tblPerson] ([Person_strCode])
GO

ALTER TABLE [dbo].[tblFilmPerson] CHECK CONSTRAINT [fkPerson_FilmPerson]
GO


USE [VISTAHO]
GO

INSERT INTO [dbo].[tblFilmPerson]
           ([Film_strCode]
           ,[Person_strCode]
           ,[FPerson_strType]
           ,[FPerson_dtmModifiedDate])
     VALUES
           (<Film_strCode, char(10),>
           ,<Person_strCode, varchar(10),>
           ,<FPerson_strType, varchar(1),>
           ,<FPerson_dtmModifiedDate, datetime,>)
GO


USE [VISTAHO]
GO

INSERT INTO [dbo].[tblPerson]
           ([Person_strCode]
           ,[Person_strFirstName]
           ,[Person_strLastName]
           ,[Person_strURLToDetails]
           ,[Person_strURLToPicture]
           ,[Person_dtmModifiedDate]
           ,[lFilmDirectorId]
           ,[lFilmStarId])
     VALUES
           (<Person_strCode, varchar(10),>
           ,<Person_strFirstName, nvarchar(30),>
           ,<Person_strLastName, nvarchar(30),>
           ,<Person_strURLToDetails, varchar(255),>
           ,<Person_strURLToPicture, varchar(255),>
           ,<Person_dtmModifiedDate, datetime,>
           ,<lFilmDirectorId, int,>
           ,<lFilmStarId, int,>)
GO


USE [VT_External]
GO

INSERT INTO [dbo].[MOVIE]
           ([MOV_CODE]
           ,[MOV_TITLEEN]
           ,[MOV_TITLEGR]
           ,[MOV_TITLEPUB]
           ,[MOV_CATEGORY]
           ,[MOV_GROUP]
           ,[MOV_RATING]
           ,[MOV_DURMOV]
           ,[MOV_DURADV]
           ,[MOV_DIRECTOR]
           ,[MOV_PRODUCER]
           ,[MOV_DISTRIBUTOR]
           ,[MOV_PRIORITY]
           ,[MOV_SYNOPSIS]
           ,[MOV_RELDATE])
     VALUES
           (<MOV_CODE, D_CODEMOV,>
           ,<MOV_TITLEEN, D_DESCL,>
           ,<MOV_TITLEGR, D_DESCL,>
           ,<MOV_TITLEPUB, D_DESCS,>
           ,<MOV_CATEGORY, D_CODEPRM,>
           ,<MOV_GROUP, D_CODEPRM,>
           ,<MOV_RATING, D_CODEPRM,>
           ,<MOV_DURMOV, D_INTEGER,>
           ,<MOV_DURADV, D_INTEGER,>
           ,<MOV_DIRECTOR, D_CODEPRM,>
           ,<MOV_PRODUCER, D_CODEPRM,>
           ,<MOV_DISTRIBUTOR, D_CODEPRM,>
           ,<MOV_PRIORITY, D_INTEGER,>
           ,<MOV_SYNOPSIS, D_NOTES,>
           ,<MOV_RELDATE, D_DATE,>)
GO

Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 10:08:41

Forgot one


USE [VT_External]
GO

/****** Object:  Table [dbo].[PARAMETRIC]    Script Date: 13/2/2015 5:07:59 µµ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PARAMETRIC](
	[PRM_CODE] [dbo].[D_CODEPRM] NOT NULL,
	[PRM_DESC] [dbo].[D_DESCS] NULL,
	[PRM_STR1] [dbo].[D_DIAK] NULL,
	[PRM_STR2] [dbo].[D_DIAK] NULL,
	[PRM_STR3] [dbo].[D_DIAK] NULL,
	[PRM_STR4] [dbo].[D_DIAK] NULL,
	[PRM_INT1] [dbo].[D_INTEGER] NULL,
	[PRM_INT2] [dbo].[D_INTEGER] NULL,
	[PRM_INT3] [dbo].[D_INTEGER] NULL,
	[PRM_INT4] [dbo].[D_INTEGER] NULL,
	[PRM_CODEC]  AS (substring([prm_code],1,2)),
	[PRM_CODEN]  AS (substring([prm_code],3,4)),
 CONSTRAINT [PK_PARAMETRIC] PRIMARY KEY CLUSTERED 
(
	[PRM_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [VT_External]
GO

INSERT INTO [dbo].[PARAMETRIC]
           ([PRM_CODE]
           ,[PRM_DESC]
           ,[PRM_STR1]
           ,[PRM_STR2]
           ,[PRM_STR3]
           ,[PRM_STR4]
           ,[PRM_INT1]
           ,[PRM_INT2]
           ,[PRM_INT3]
           ,[PRM_INT4])
     VALUES
           (<PRM_CODE, D_CODEPRM,>
           ,<PRM_DESC, D_DESCS,>
           ,<PRM_STR1, D_DIAK,>
           ,<PRM_STR2, D_DIAK,>
           ,<PRM_STR3, D_DIAK,>
           ,<PRM_STR4, D_DIAK,>
           ,<PRM_INT1, D_INTEGER,>
           ,<PRM_INT2, D_INTEGER,>
           ,<PRM_INT3, D_INTEGER,>
           ,<PRM_INT4, D_INTEGER,>)
GO

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 10:37:29

Trying this but will just bring no results:


WHERE (NOT EXISTS(select prm_code from movie inner join parametric on movie.mov_director = parametric.prm_code)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 10:47:37

Sorry I asked for data! Didn't realize you have a bunch of user-defined types, e.g.

[MOV_CODE] [dbo].[D_CODEMOV] NOT NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 11:23:47

Questions:

How may rows does your query return *without* the WHERE clause?

How many rows *with* the WHERE clause?

Can you describe in words the filtering effect you need?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 11:25:04

I will try to get some if not in the weekend, then on Monday but won't you know how to just change the "<>" ?
I am also trying this:


select * 
FROM VEXTERNAL.dbo.MOVIE M
RIGHT JOIN  (SELECT Film_strCode,MAX(Person_strCode) as Person_strCode FROM VISTAHO.dbo.tblFilmPerson WHERE FPerson_strType='D' GROUP BY Film_strCode) FP
ON M.MOV_CODE=RIGHT(FP.Film_strCode,6) COLLATE Latin1_General_CS_AS
LEFT JOIN .VISTAHO.dbo.tblPerson P
ON		P.Person_strCode = FP.Person_strCode
LEFT JOIN	(SELECT	PRM_CODE,PRM_DESC
		FROM	VEXTERNAL.dbo.PARAMETRIC
		WHERE	PRM_CODE LIKE '07%'
		) PAR
ON		PAR.PRM_DESC = ltrim(isnull(P.Person_strFirstName,'') + ' ' + P.Person_strLastName) COLLATE Latin1_General_CS_AS

WHERE (mov_code not in(select mov_code from movie inner join parametric  on MOV_DIRECTOR = PRM_CODE)
AND 
PRM_CODE IS NOT NULL) OR (MOV_DIRECTOR IS NULL AND PRM_CODE IS NOT NULL)
AND MOV_CODE IS NOT NULL

strangly this will bring zero data but where i do this:

 select * from movie where mov_code not in(select mov_code from movie inner join parametric  on MOV_DIRECTOR = PRM_CODE)

I get data. Can you confirm that at least this is correct and it may be the other equalities issue?
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 11:27:33

I would just ignore the user defined.
I get 1214 without the where and 10 with the where.
I need to filter so PRM_CODE<>MOV_DIRECTOR + the other filters that i do not need to change . I am not sure how else i can describe this.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 11:29:51

I'm just having trouble understanding what you are trying to achieve with the WHERE clause. I can see that you have an intermediate result set that is a result of the joins. From that set, you want to filter out some rows, correct? Would you please describe the rows you would need to have excluded and the reasons? I think that with such a description, we can work towards an efficient solution.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 11:34:45

I am not really sure what to say :(
I exclude every row that has an equality on PRM_CODE and MOV_DIRECTOR. These rows must not be inserted in the result.
Hey, i am just leaving job so can we pick this up Tomorrow or on Monday?
Thank you very much for trying to help.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-13 : 11:36:14

Please surer mega ignore every other filter. Just a change to the specific one is what i need. Ignore everything else, the table user defined, everything.
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 12:01:05

Ok, Just a thought, instead of your current where clause, how about changing the last join


LEFT JOIN
(
    SELECT PRM_CODE
         , PRM_DESC
     FROM dbo.PARAMETRIC
     WHERE PRM_CODE LIKE '07%'
)PAR
ON PAR.PRM_DESC = LTRIM(ISNULL(P.Person_strFirstName, '') + ' ' + P.Person_strLastName)COLLATE Latin1_General_CS_AS
AND PAR.PRM_CODE = M.MOV_DIRECTOR

WHERE PAR.PRM_CODE is null and PAR.PRM_DESC is not null

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-16 : 07:14:45

[code]
select *
FROM VT_EXTERNAL.dbo.MOVIE M
RIGHT JOIN (SELECT Film_strCode,MAX(Person_strCode) as Person_strCode FROM VISTAHO.dbo.tblFilmPerson WHERE FPerson_strType='D' GROUP BY Film_strCode) FP
ON M.MOV_CODE=RIGHT(FP.Film_strCode,6) COLLATE Latin1_General_CS_AS
LEFT JOIN VISTAHO.dbo.tblPerson P
ON P.Person_strCode = FP.Person_strCode
LEFT JOIN
(
SELECT PRM_CODE
, PRM_DESC
FROM dbo.PARAMETRIC
WHERE PRM_CODE LIKE '07%'
)PAR
ON PAR.PRM_DESC = LTRIM(ISNULL(P.Person_strFirstName, '') + ' ' + P.Person_strLastName)COLLATE Latin1_General_CS_AS
-- PRM_CODE = MOV_DIRECTOR

WHERE (
PRM_CODE IS NOT NULL)
OR (MOV_DIRECTOR IS NULL AND PRM_CODE IS NOT NULL)
AND MOV_CODE IS NOT NULL
order by mov_code
[/code]
If i use PRM_CODE = MOV_DIRECTOR it will bring zero rows.
If i remove it it will play but we have an issue here, because it just happens that there is no mov_director that is not null, if we had a mov_director that was not null
and had the same code with prm_code it would include them. That is the use of PRM_CODE<>MOV_DIRECTOR. To remove null or not null directors with the same prm_code.
Any thoughts?
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-16 : 07:22:39

Will maybe this do? Not in?


select * 
FROM VT_EXTERNAL.dbo.MOVIE M
RIGHT JOIN  (SELECT Film_strCode,MAX(Person_strCode) as Person_strCode FROM VISTAHO.dbo.tblFilmPerson WHERE FPerson_strType='D' GROUP BY Film_strCode) FP
ON M.MOV_CODE=RIGHT(FP.Film_strCode,6) COLLATE Latin1_General_CS_AS
LEFT JOIN VISTAHO.dbo.tblPerson P
ON		P.Person_strCode = FP.Person_strCode
LEFT JOIN	(SELECT	PRM_CODE,PRM_DESC
		FROM	VT_EXTERNAL.dbo.PARAMETRIC
		WHERE	PRM_CODE LIKE '07%'
		) PAR
ON		PAR.PRM_DESC = ltrim(isnull(P.Person_strFirstName,'') + ' ' + P.Person_strLastName) COLLATE Latin1_General_CS_AS
WHERE (PRM_CODE not in(MOV_DIRECTOR)
AND 
PRM_CODE IS NOT NULL) OR (MOV_DIRECTOR IS NULL AND PRM_CODE IS NOT NULL)
AND MOV_CODE IS NOT NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-16 : 10:16:02

[code]
WHERE (PRM_CODE not in(MOV_DIRECTOR)
[/code]

is exactly the same as:

[code]
WHERE PRM_CODE <> MOV_DIRECTOR
[/code]

You should have implemented my changes as I posted them and not added in your WHERE clause. Let's look at it:

[code]
WHERE PAR.PRM_CODE is null and PAR.PRM_DESC is not null
[/code]

This says, filter the results from the LEFT JOIN (mine, not yours) looking for cases where there is no match on MOV_DIRECTOR but there is a match on PRM_DESC. Is that what you are after?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-17 : 03:18:06

Hi.
Won't this not take in account cases where mov_director is equal to PRM_DESC ?
Also as i've said, if i use PRM_CODE = MOV_DIRECTOR it will bring zero rows.
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 07:14:18

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-18 : 02:54:10

Ok. So (i tisnk you ment to write WHERE PAR.PRM_CODE is null and M.MOV_DIRECTOR is not null)
when i use you query i get 0 results. If i exclude WHERE PAR.PRM_CODE is null and M.MOV_DIRECTOR is not null i get 127 results but the original will return 130. I include some codes that are not in the original query and exclude some doubles.
I'm not sure how can i go by.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-02-18 : 03:00:22

Results that your query excludes (and i need) have everything the same but the PRM_CODE (mov_director is null), apart of some results that are included in your query and not included in the original.What i also see in your query is that i get all the PRM_CODES as NULL. The original has the values. Please note that i excluded the WHERE PAR.PRM_CODE is null and M.MOV_DIRECTOR is not null in order to get the 127 values. Else i get zero values.
Thanks.
P.S. If we cannot do something with this then do not bother as i have excluded some job runs so this does not run as frequently thus not creating many issues now, even with the <> included.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 08:53:43

It could be that I don't understand your whole requirement here. It could also be that you're struggling with a LEFT JOIN and the difference between the matching predicates (the ON condition) and the filtering predicates (the WHERE clause). I worked up a simple example to show the difference:


declare @tblPerson table (Person_strFirstName varchar(50), Person_strLastName varchar(50))
declare @movie table(MOV_Director varchar(50))
declare @parametric table(PRM_DESC varchar(50), PRM_CODE varchar(50))

insert into @tblPerson(Person_strFirstName, Person_strLastName) values
('Brad', 'Pitt'),
('Angelina', 'Jolie')

insert into @movie(mov_director) values
('George Lucas'),
('Stephen Spielberg')

insert into @parametric(prm_desc, prm_code) values
('Brad Pitt', 'George Lucas'),
('Angelina Jolie', 'Stephen Spielgberg')

select * 
from @movie m
left join @parametric par
  on m.MOV_Director = par.prm_code
 where par.prm_code is null -- comment/uncomment this line

If you run this code and then run it again with the WHERE clause commented out, you'll see the difference. The idea is this:

In a LEFT JOIN, the join matches the rows on the LEFT side (the @movie table in my example) with rows on the right side (the @parameter table). If there are matching rows on the right side, those columns appear in the result set. If there is some row on the left side for which there is no matching row on the right side, the left-side row appears in the results but all the right-side columns are NULL. That's where the filter comes in (the WHERE clause). Without the WHERE clause, we see these NULLs. With the WHERE clause, we filter them out. You can use this behavior to solve your problem.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources