DERIVED COLUMN SYNTAX HELP

[pms18066]

I am trying to create a derived column to concatenate a few fields together. The first field should ony contain all of the characters beginning with the 3rd character... ie
Right(colA, len(colA)-2)

The following syntax is throwing an error and I cant figure out what is wrong... The fields do contain NUlls for some records....Can any one help??

RIGHT((ISNULL(WARRANT_NUMBER_GS) ? " " : WARRANT_NUMBER_GS),LEN((ISNULL(WARRANT_NUMBER_GS) ? " " : WARRANT_NUMBER_GS) - 2)) + " " + (ISNULL(SOLICITOR_GS) ? "" : SOLICITOR_GS) + " " + (ISNULL(COURT_REPORTER_GS) ? "" : COURT_REPORTER_GS) + " " + (ISNULL(INCIDENT_NUMBER_GS) ? "" : INCIDENT_NUMBER_GS)

 

[SQLBill]

First, it helps if you post the error message.

Second, the syntax for ISNULL is ISNULL(1st expression, 2nd expression).

Example: ISNULL(Column1, 'Nothing')

Your script does not have the second expression with any ISNULL.

Example:
(ISNULL(INCIDENT_NUMBER_GS) ? "" : INCIDENT_NUMBER_GS)

You have only one expression for the ISNULL and that is INCIDENT_NUMBER_GS. Nothing else is inside the parenthesis. What is the script supposed to do if INCIDENT_NUMBER_GS is NULL?

Maybe you mean this?

ISNULL(INCIDENT_NUMBER_GS, '?')

-SQLBill




The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[pms18066]

THIS IS NOT SQL ISNULL(). THIS CODE IS INSIDE OF AN EXPRESSION FOR A DERIVED COLUMN. THE FIX FOR THIS WAS TO USE A SCRIPT COMPONENT AND THEN USE .NET TO GET THE PIECE OF THE STRING I WANTED. THANKS FOR TAKING A LOOK EVERYONE.

 

[MDXer]

This type of work maybe easier to do on the server as part of a source query if the source is SQL Server.