Help with 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

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 13:03:20

Can anyone help me in writing the below query with a union ...instead of using or in the join condition,

SELECT B.BUILD_IND, C.JOB_NAME AS EC_JOB_NAME,
B.BUILD_ID, A.JOB_NAME, B.PHONE_BUILD,B.SUB_SYSTEM

FROM dbo.DW_T_BATCH_RECORDS AS A

INNER JOIN
dbo.DW_T_BUILD_VERSIONS AS B ON

CHARINDEX(B.PHONE_BUILD + '_', A.JOB_NAME) > 0

OR CHARINDEX(B.PHONE_BUILD + 'Blueprint', A.JOB_NAME) > 0

OR B.PHONE_BUILD = A.JOB_NAME INNER JOIN

dbo.DW_T_EC_JOB AS C

ON C.JOB_ID = A.JOB_ID AND C.JOB_NAME = A.JOB_NAME

WHERE (A.BATCH_ENV = 'Ecommander') AND (B.BUILD_IND = 'Target')

AND (A.JOB_NAME NOT LIKE 'SBA%') OR (B.BUILD_IND = 'SBA')

AND (A.JOB_NAME LIKE 'SBA%')

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:37:14

I think your existing query can be simplified as below...

SELECT B.BUILD_IND, C.JOB_NAME AS EC_JOB_NAME, 
B.BUILD_ID, A.JOB_NAME, B.PHONE_BUILD,B.SUB_SYSTEM
FROM dbo.DW_T_BATCH_RECORDS AS A 
INNER JOIN dbo.DW_T_BUILD_VERSIONS AS B ON A.JOB_NAME LIKE B.PHONE_BUILD + '%'
INNER JOIN dbo.DW_T_EC_JOB AS C ON C.JOB_ID = A.JOB_ID AND C.JOB_NAME = A.JOB_NAME
WHERE (A.BATCH_ENV = 'Ecommander') AND (B.BUILD_IND = 'Target') 
AND (A.JOB_NAME NOT LIKE 'SBA%') OR (B.BUILD_IND = 'SBA') 
AND (A.JOB_NAME LIKE 'SBA%')

Why would you like to use UNION instead of a JOIN??

Also this condition , I think, is going to negate everything and not bring back any results.

quote:
AND (A.JOB_NAME NOT LIKE 'SBA%') OR (B.BUILD_IND = 'SBA') AND (A.JOB_NAME LIKE 'SBA%')

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 13:54:09

Here i have 3 condition for the first join to be satisfied

CHARINDEX(B.PHONE_BUILD + '_', A.JOB_NAME) > 0

OR CHARINDEX(B.PHONE_BUILD + 'Blueprint', A.JOB_NAME) > 0

OR B.PHONE_BUILD = A.JOB_NAME

and one condition for the 2nd join

ON C.JOB_ID = A.JOB_ID AND C.JOB_NAME = A.JOB_NAME

I dont want to use "or", instead of using "or" can i use "union" ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:57:47

I just re-wrote the first join to this...

quote:
FROM dbo.DW_T_BATCH_RECORDS AS A
INNER JOIN dbo.DW_T_BUILD_VERSIONS AS B ON A.JOB_NAME LIKE B.PHONE_BUILD + '%'

This wont work?

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 14:10:10

Actually the jobname and phone build are separated by "_" or the word "blueprint" in my data and also i have some jobnames same as phonebuild thats the reason i have those 3 conditions there...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 14:23:04

Yeah but using "LIKE" should take care of all those 3 conditions, no? Did you try the query?

Can you post some sample data from those two tables.

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 14:36:32

Yeah "like" will give the data which i do not require also ,
for eg , i have the following 5 jobs associated to the
Phonebuild:M7201JSDCAAAAD4432

Jobnames :1.M7201JSDCAAAAD4432_17MAR2008_044045pm
2.M7201JSDCAAAAD4432
3.M7201JSDCAAAAD4432blueprint_17MAR2008_044045pm
4.M7201JSDCAAAAD4432tgt_17MAR2008_044045pm
5.4M7201JSDCAAAAD4432S_17MAR2008_044045pm

I do not require the jobs 4 and 5...

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2009-12-08 : 15:18:46

Try LIKE '[ ^ ]'. Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

WHERE au_lname LIKE 'de[^l]%' returns all author last names starting with de and where the following letter is not l.

In your case:
WHERE A.JOBNAME LIKE B.PHONEBUILD+'[^ts]%'

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 15:29:45

Thank you for reply , actually thats an example of phone build , i have different phone builds in my data and data can be updated with more phone builds which i do not know how there look like , i have used charindex function to indicate that phonebuild can be found any where in the job name but the common format followed is , phonebuild in jobaneme is separated by an "_" or "blueprint"

I have got the results as i have expected with charindex, but by using so many "or"'s in my query the performance is being dropped drastically, thats the reason i am looking to write the same query using union operator instead of "or"

rds207
Posting Yak Master

198 Posts

Posted - 2009-12-08 : 15:55:08

Please Help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources