Beginner needing TSQL help

[seckner]

Pretty new to SQL so I'm looking for the correct way of looping. What I mean is I have a small part of a statement:

,CASE WHEN [PN1] = NULL THEN ' ' ELSE [PN1] END
** lot's more **

Now in the language I know I could do something like this - where the %x% just substitutes the x value:
For x = 1 to 20
,CASE WHEN [PN%x%] = NULL THEN ' ' ELSE [PN%x%] END
** lot's more **
Next

What I have is 20 full statements of the exact same code, just incrementing the part number. Seems there has to be a more efficient way.

Help is very much appreciated!

 

[gmmastros]

When you think about SQL, you should try to get out of the habit of looping. Loops are generally a lot slower in SQL than they are in other languages. SQL is highly optimized for set based operation.

Your code (in the first block) will compile and run, but it will not generate the results you expect. In words, what you want is... When the value in the column is NULL, return an empty string, otherwise return the value in the column. In SQL, you cannot compare anything to NULL. Think of NULL as "Unknown". You can determine if a value is null by using the IS keyword, like this:

[tt],CASE WHEN [PN1] [!]IS[/!] NULL THEN ' ' ELSE [PN1] END[/tt]

There is a simpler way to write this, too.

[tt], Coalesce([PN1], '') As [PN1][/tt]

The Coalesce function will return the first parameter in the list of parameters that is not null. So, if PN1 is not null, it will be returned by the coalesce function. if PN1 is null, then it moves on to the second parameter (which is an empty string) and returns that instead.

What I have is 20 full statements of the exact same code, just incrementing the part number. Seems there has to be a more efficient way.

If you have a table with 20 columns representing part numbers, you're doing it wrong. I would strongly encourage you to read a couple articles about [google]database normalization[/google]. The better way to store this data is to have a separate table for the parts. Each part would get it's own row in this other table. By having a separate table, you will likely reduce storage space because you are not storing empty data (when you have a widget with just 2 part numbers), and you will not be limited to 20 parts (per widget).

I could show you how to loop. I do use looping in my own code, but I use it sparingly. I have approximately 2000 stored procedures in the database, and I would bet that less than 20 have loops in them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

seckner,

I agree with George but the real question for you to answer to get to a solution is for you to tell us what you are trying to do in words along with any tables and layouts involved (columns and maybe some sample data).

One more thought there is another function you can use instead of coalesce... Isnull. It takes exaclty two parameters... It returns the second parameter when the first is false.

, Isnull([PN1], '') As [PN1]

 

[gmmastros]

It returns the second parameter when the first is false.

Just to clarify, It returns the second parameter when the first is [!]NULL[/!].

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[seckner]

Thank you sir! I have 2 big problems, 1 is I'm not a dbase guy (yet anyhow) and I've inherited a very old UNIX back end, so we're forced into 'mirroring' their data structure. When we get the data from it we have 20 parts fields, 20 parts cost fields, 20 parts description fields, etc... forever. We actually have 20 Description fields... and the financial information is worse. To feed our data back to it we have to use it as it exists. Some day we'll be big enough to get rid of it but that's not today so I'm forced to use and return what we have. I didn't know about the Coalesce function - sweet! So, to turn the 20 parts fields I get into something workable for our customers I have 10 lines of code 20 times... it works, I just think there has to be a 'smarter' way than 200 lines of query to just change a number.

Again, thank you!

 

[gmmastros]

I can understand and appreciate your position. I often times hear from developers that have no control over the structure of their data. I still encourage you to read up on database normalization. It's not too complicated and will certainly help you when you get in to a situation where you have an opportunity to design your own tables.

Like I said earlier, it is possible to do looping to accomplish this, but it is (in my opinion) not such a good thing to do. The looping process would need to run through your columns and build a dynamic SQL string, and then you would execute the dynamic SQL. Truth be told, by the time you end up writing all this, you could have copy/paste/modified your way through it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[seckner]

As messy as it is this is one of the 20 'Parts' statements in the query I've inherited. My goal is to simplify it if I can and try to make it more manageable for me. This is day #1 for me at SQL so I'm looking at it as a true outsider. Just what I've gotten from both of you in the last few minutes has already shown me some problems here.

,CASE WHEN [PartNumber10] = NULL THEN ' ' ELSE [PartNumber10] END
,RIGHT('000'+CONVERT(varchar(3), ISNULL(QuantityPart10,0)),3) AS PartQuantity10
,CASE WHEN WarrantyTypePart10 = '1' THEN '00000000' ELSE
CASE WHEN [DescriptionPart10] IN ('LABOR CREDIT','RPP DISCOUNT','ESTIMATE FEE CREDIT','DISCOUNT','RETAIL PRICE POINT DISCOUNT','COUPON','COUPON5','COUPON10') THEN '-' ELSE '' END +
RIGHT('00000000' + REPLACE(CONVERT(nvarchar(9), CONVERT(decimal(9,2), ISNULL(PricePart10,0))), '.', ''),8) END AS PartPrice10
,CASE WHEN [DescriptionPart10] = NULL THEN ' ' ELSE [DescriptionPart10] END

I might be wrong but this just seems inefficient times 20. Again, thanks for your time and patience.

 

[seckner]

Using what I've learned so far I've cleaned up a BUNCH and it's starting to make some sense. Given this line (yes, 20 times):

CASE WHEN OCode = '17' THEN
'"' + RIGHT(RTRIM(REPLACE(
CASE WHEN CommentLine1 = NULL THEN '' ELSE RTRIM( REPLACE( CommentLine1,'"','')+ ' ') END +

I know that the "CommentLine1 = NULL" is wrong but I can't figure out how to put it so the ELSE portion will still work.

The 20 Comment Lines are all up to 65 characters in length so I ultimately need to put them all into 1 long string and getting rid of any double quotes.

 

[gmmastros]

Just change the = to is.

change [tt]CommentLine1 = NULL[/tt]

To

[tt]CommentLine1 [!]IS[/!] NULL[/tt]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom