convert jet 4.0 sql statement to sql server

[Guest_imported]

i have a number of sql statements that work fine with jet 4.0 but do not work with sql server. my application needs to be able to use both jet 4.0 and sql server. here is an example of one of them:

SELECT ID AS [Common1ID], Trim(IIf(IsNull(LastName), '', LastName) + IIf(IsNull(FirstName), '', IIf(FirstName = '', '', ', ' + FirstName) + IIf(IsNull(MiddleName), '', ' ' + MiddleName))) + chr(13) + chr(10) + IIf(IsNull(Address), '', Address) +chr(13) + chr(10) + IIf(IsNull(City), '', City) + IIf(IsNull(State), '', ',' + State) + IIf(IsNull(Zip), '', Zip) AS [Name], Trim(IIf(IsNull(FirstName), '', FirstName) + IIf(IsNull(MiddleName), '', ' ' + MiddleName) + IIf(IsNull(LastName), '', LastName)) AS [Signature] FROM Common

if anyone could create a sql server sql statement that would return the same results as this jet 4.0 statement it would be great. thanks.

 

[bbosley]

May I make a suggestion? Use the SQL to return the fields only and code your application to do the concatenation. Your development language should have much better string handling capabilities than SQL itself.

Hope this helps.

bbosley

 

[Guest_imported]

bbosley, i may end up doing that, but only as a last resort because i have almost 2000 forms that need to be filled automatically from a common record. right now i have one set of code that runs every form because i do all string manipulation in the sql statement. if i have my code do the string manipulation i will need to write 2000 procedures and if something needs to change in the code i will need to change it in 2000 places instead of one. any idea how to make an IIf statement in sql server?

 

[tlbroadbent]

Here is one way to convert the query. I haven't parsed this query to test the syntax. Hopefully, it will work or at least be close to what you need.

Notes:[ol][li]char() replaces chr()
[li]IsNull works differently. In T-SQL the function replaces Null values with a selected value.
[li]Case statements replace IIf
[li]For simplicity, I left off the "trim" function. Use rtrim and/or ltrim in T-SQL.[/ol]SELECT
ID AS Common1ID,
IsNull(LastName, '') +
Case IsNull(FirstName, '')
When '' Then ''
Else ', ' + FirstName
End +
Case IsNull(MiddleName, '')
When '' Then ''
Else ' ' + MiddleName
End +
char(13) + char(10) +
IsNull(Address, '') +
char(13) + char(10) +
IsNull(City, '') +
Case IsNull(State, '')
When '' Then ''
Else ', ' + State
End +
' ' + IsNull(Zip, '')) AS Name,
IsNull(FirstName, '') +
Case IsNull(MiddleName, '')
When '' Then ''
Else ' ' + MiddleName
End +
Case IsNull(LastName, '')
When '' then ''
Else ' ' + LastName
End AS Signature

FROM Common Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Guest_imported]

tlbroadbent,

thanks for the response, i couldn't quite get it to work so a co-worker of mine came up with this function:

FUNCTION dbo.IIF(@vchValue varchar(255), @vchTrue varchar(255), @vchFalse varchar(255))
RETURNS varchar(255)
BEGIN
DECLARE @vchTemp varchar(255)

SET @vchTemp = ISNULL(@vchValue, '')

IF LEN(@vchTemp) = 0
SET @vchTemp = @vchFalse
ELSE
SET @vchTemp = @vchTrue

RETURN @vchTemp
END

thanks for the help.

 

[tlbroadbent]

I fail to see what the "function" accomplishes and why it is necessary.

What program are you using to execute the queries? Do you have Query Nalyzer installed?

What happened when you attempted to execute the query? Did you get syntax errors? Dis it fail to return results? Did it return incorrect results? Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Guest_imported]

tlbroadbent,

i executed it in query analyser and the syntax was incorrect. maybe it was something small but i just didn't have the time to debug it and the IIf function provided by my co-worker works perfect for what i'm trying to do, so don't take it personally it just happens to work better for me and whether i use it or not, i appreciate your help.

i am using vb to execute the queries and the point of that function is to return one string if the field value is null or zero length or return a different string if the field value is not null and is not zero length. example:

i want to return a name formated like this: LastName, FirstName MiddleName

and if the FirstName is Null or zero length then i want just the LastName

so i'd write something like this:

SELECT dbo.IIF(LastName, LastName, '') + dbo.IIF(FirstName, ', ' + FirstName + dbo.IIF(MiddleName, '', ' ' + MiddleName), '') AS [Name] FROM Common

thanks again.

 

[tlbroadbent]

Nothing personal at all - just seeking understanding. A purpose of the forums is to share knowledge. I like to know why something failed as well as if it worked.

After closer insepction, I understand the function. I'll have to compare performance of queries using Case statemnts and this new function. You may convert me.

Thanks, Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm