Cross Apply?

SELECTtd.FirstName,

td.LastName,

td.City,

td.State,

td.ZIPCode,

Result.CityFirstName,

Result.State,

Result.ZipCode,

Result.Distance,

zu.LAT,

zu.LNG,

zu.LAT_RADIANS,

zu.LNG_RADIANS

FROMMyTable1.dbo.App_TelephoneDetails AS td

INNER JOINZipUSA AS zu ON zu.ZIP_CODE = td.ZipCode

CROSS APPLYUfn_GetLocationsInRadius(zu.LAT, zu.LNG, 25) AS Result

WHEREtd.ZipCode = '12345'

OP didn't state if the Fk was a constraint or not.

And since OP used a correlated subquery in his original post, I had to raise a flag for this issue.

If...

To replace the functionality 100%, we should use LEFT JOIN.

The correlated subquery might return NULL if there is no match.

UPDATEmt

SETmt.FKID = x.FKID

FROMMyTable AS mt

LEFT JOINMyFK2Table AS x ON x.ID =...

No need to invoke CLR (only if you want greater speed).

Free RC4 encryption here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258

Free MIME64/BASE64 encoding here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67831

Free ROT algorithm herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67752

CREATE FUNCTION dbo.fnExtractDigitSequence

(

@ColumnDelimiter CHAR(1) = ',',

@Filter VARCHAR(8000) = '[0-9]',

@UserData VARCHAR(8000) = NULL

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE@Position INT,

@ValidChar TINYINT,

@LastValidChar TINYINT

SELECT@Position = LEN(@UserData),

@LastValidChar = 0

WHILE @Position > 0

SELECT@ValidChar =CASE

WHEN SUBSTRING(@UserData, @Position, 1) COLLATE Latin1_General_BIN LIKE...

Huh?

Is this the twilight zone? I already posted that answer for both

1.5 and 3 hours ago...

Thanks for the feedback.

I have posted an explanation why.

What you need is this

SELECTp.Name,

p.theValue,

COUNT(*) AS Items

FROMYourTableNameHere AS s

UNPIVOT(

theValue

FOR theColumn IN (s.[Column1], s.[Column2], ... , s.[Column18])

...

I think you should have told us from the beginning.

DECLARE@Sample TABLE (Name VARCHAR(4), Column1 INT, Column2 INT, Column3 INT)

INSERT@Sample

SELECT'John', 1, 1, 2 UNION ALL

SELECT'Tom', 0, 1, 2 UNION ALL

SELECT'Mary',...

How can alias wit prefix I work?

And the multiple WHEREs?

What I think Sergyi meant is this

SELECT@Ret = COALESCE(l4.ID, l3.ID, l2.ID, l1.ID, 0)

FROMOrg AS o

LEFT JOINOrg_Lvl4 l4 on l4.Lvl4 =...

return coalesce(@ret , 0)

because the SELECT parts may return NULL !

Or no row at all which case @ret is untouched and keeps it's orginal...

SELECTName,

CASE WHEN Column1 = 1 THEN 1 ELSE 0 END

+ CASE WHEN Column2 = 1 THEN 1 ELSE 0 END

+ CASE WHEN Column3 = 1 THEN 1 ELSE 0 END...

SELECTRTRIM(LTRIM(COALESCE(Last_Name, '') + COALESCE(' ' + First_Name, '') + COALESCE(' ' + Middle_Name, ''))) AS Name,

MGR_First_Name,

MGR_Last_Name

INTOEmployeeData

FROMEP_Data

Hrrmm...

What if there are more than one record with TriggerField = 5 ???