Need Help - 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 2012 Forums

Transact-SQL (2012)

Need Help

Author

Topic

GreenWizard
Starting Member

2 Posts

Posted - 2015-04-30 : 08:40:10

Requesting help with modifying a query. I'm optimistic this has a quick fix (e.g. adding a 1 line syntax).
Thanks so much!

DESCRIPTION:

We have a stored procedure that outputs BOM of a part number corresponding custom fields (see code below).?

PROBLEM:

?There is a need to modify the query so it outputs BOM of a part number WHERE no Field2 is null. Meaning, if the part number doesn't have a Field2, then it doesn't show up in the query.

***************** SQL CODE **********************

SELECT CAST(RTRIM(B1.PARENT)
+ ' ('
+ CASE WHEN SE.FIELD2 IS NOT NULL THEN SE.FIELD2 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD3 IS NOT NULL THEN SE.FIELD3 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD4 IS NOT NULL THEN SE.FIELD4 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD5 IS NOT NULL THEN SE.FIELD5 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD6 IS NOT NULL THEN SE.FIELD6 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD7 IS NOT NULL THEN SE.FIELD7 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD8 IS NOT NULL THEN SE.FIELD8 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD9 IS NOT NULL THEN SE.FIELD9 ELSE '' END
+ ')' AS VARCHAR(255)) AS 'PARENT',
RTRIM(B1.PARTNUMBER)
+ ' ('
+ CASE WHEN B1.FIELD2 IS NOT NULL THEN B1.FIELD2 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD3 IS NOT NULL THEN B1.FIELD3 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD4 IS NOT NULL THEN B1.FIELD4 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD5 IS NOT NULL THEN B1.FIELD5 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD6 IS NOT NULL THEN B1.FIELD6 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD7 IS NOT NULL THEN B1.FIELD7 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD8 IS NOT NULL THEN B1.FIELD8 + ', ' ELSE '' END
+ CASE WHEN B1.FIELD9 IS NOT NULL THEN B1.FIELD9 ELSE '' END
+ ')' AS 'PARTNUMBER1',
RTRIM(B2.PARTNUMBER)
+ ' ('
+ CASE WHEN B2.FIELD2 IS NOT NULL THEN B2.FIELD2 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD3 IS NOT NULL THEN B2.FIELD3 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD4 IS NOT NULL THEN B2.FIELD4 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD5 IS NOT NULL THEN B2.FIELD5 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD6 IS NOT NULL THEN B2.FIELD6 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD7 IS NOT NULL THEN B2.FIELD7 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD8 IS NOT NULL THEN B2.FIELD8 + ', ' ELSE '' END
+ CASE WHEN B2.FIELD9 IS NOT NULL THEN B2.FIELD9 ELSE '' END
+ ')' AS 'PARTNUMBER2',
RTRIM(B3.PARTNUMBER)
+ ' ('
+ CASE WHEN B3.FIELD2 IS NOT NULL THEN B3.FIELD2 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD3 IS NOT NULL THEN B3.FIELD3 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD4 IS NOT NULL THEN B3.FIELD4 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD5 IS NOT NULL THEN B3.FIELD5 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD6 IS NOT NULL THEN B3.FIELD6 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD7 IS NOT NULL THEN B3.FIELD7 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD8 IS NOT NULL THEN B3.FIELD8 + ', ' ELSE '' END
+ CASE WHEN B3.FIELD9 IS NOT NULL THEN B3.FIELD9 ELSE '' END
+ ')' AS 'PARTNUMBER3',

FROM TEST.dbo.BOM_TREE B1
INNER JOIN TEST2.dbo.STOCKEXT SE ON B1.PARENT = SE.PARTNUMBER
LEFT JOIN TEST.dbo.BOM_TREE B2 ON B1.PARTNUMBER = B2.PARENT
LEFT JOIN TEST.dbo.BOM_TREE B3 ON B2.PARTNUMBER = B3.PARENT

WHERE B1.PARENT = @PARTNUMBER
ORDER BY B1.PARENT, B1.PARTNUMBER, B2.PARTNUMBER, B3.PARTNUMBER

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 08:55:21

Please post CREATE TABLE statements and test data as INSERT INTO statements, then post your desired results with that data.

Here are some great posting guidelines: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Basically, we need more than a query in order to give a complete, correct answer.

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 10:39:46

Can't help with the question without some more information, as gbritton has asked, but I find:


SELECT	CAST(RTRIM(B1.PARENT) 
+ ' (' 
+ CASE WHEN SE.FIELD2 IS NOT NULL THEN SE.FIELD2 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD3 IS NOT NULL THEN SE.FIELD3 + ', ' ELSE '' END
+ CASE WHEN SE.FIELD4 IS NOT NULL THEN SE.FIELD4 + ', ' ELSE '' END 
+ CASE WHEN SE.FIELD5 IS NOT NULL THEN SE.FIELD5 + ', ' ELSE '' END 
+ CASE WHEN SE.FIELD6 IS NOT NULL THEN SE.FIELD6 + ', ' ELSE '' END 
+ CASE WHEN SE.FIELD7 IS NOT NULL THEN SE.FIELD7 + ', ' ELSE '' END 
+ CASE WHEN SE.FIELD8 IS NOT NULL THEN SE.FIELD8 + ', ' ELSE '' END 
+ CASE WHEN SE.FIELD9 IS NOT NULL THEN SE.FIELD9 ELSE '' END 
+ ')' AS VARCHAR(255)) AS 'PARENT',

both hard to read and looks, to me, that it may be very inefficient. Personally I would recode that as:


SELECT	CAST(RTRIM(B1.PARENT) 
+ ' (' 
+ COALESCE(SE.FIELD2 + ', ', '')
+ COALESCE(SE.FIELD3 + ', ', '')
+ COALESCE(SE.FIELD4 + ', ', '')
+ COALESCE(SE.FIELD5 + ', ', '')
+ COALESCE(SE.FIELD6 + ', ', '')
+ COALESCE(SE.FIELD7 + ', ', '')
+ COALESCE(SE.FIELD8 + ', ', '')
+ COALESCE(SE.FIELD9, '')
+ ')' AS VARCHAR(255)) AS 'PARENT',

and it looks to me as though it will always leave a trailing "," (unless FIELD9 has a value) so perhaps this might look more attractive (assuming that lower numbered fields are always present)


SELECT	CAST(RTRIM(B1.PARENT) 
+ ' (' 
+ COALESCE(SE.FIELD2, '')
+ COALESCE(', ' + SE.FIELD3, '')
+ COALESCE(', ' + SE.FIELD4, '')
+ COALESCE(', ' + SE.FIELD5, '')
+ COALESCE(', ' + SE.FIELD6, '')
+ COALESCE(', ' + SE.FIELD7, '')
+ COALESCE(', ' + SE.FIELD8, '')
+ COALESCE(', ' + SE.FIELD9, '')
+ ')' AS VARCHAR(255)) AS 'PARENT',

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources