General UDF to emulate IIF() ?
-
Can anyone point me to a UDF which would emulate the IIF() function available in other systems (e.g. VFP):
IIF(testval, trueval, falseval)
should be able to accept the three arguments, return the trueval if testval evaluates to true (or = 1) , the falseval otherwise.
Ideally it should handle returnvalues of strings, numerics or dates.. but I'd settle for one that just returned varchar..
An example of the type of thing I would like to be able to move to SQL server:
Select IIF(shipdate < getdate(), 'shipped','pending') as status from ....
Thanks,
Ilmar
-
You can use T-SQL's CASE sturucture
SELECT CASE WHEN shipdate < getdate() THEN 'shipped' ELSE 'pending' END as status
Once you understand the BITs, all the pieces come together
-
Thanks.. but what I'm looking for is a general solution which I can implement as a UDF.
I have a large number of Selects, Views, etc. to move from VisualFoxPro over to SQL server.. many of them use the IIF() construct.
What I"m looking for is a way to avoid re-coding all of them, by using a UDF which emulates the VFP IIF() function..
Where I'm at now is something like this:
--------------
CREATE FUNCTION dbo.IIF
(@test varchar(4000), @trueval varchar(4000) , @falseval varchar(4000))
RETURNS varchar(4000) AS
Begin
Declare @return varchar (4000)
If @test-2 = 1
Set @return = @trueval
Else
Set @return = @falseval
Return @return
END
----------
but this doesn't allow me to send an expression as the first argument which would then be evaluated as 1 or 0.., nor does it seem to be generic enough to return e.g. dates .. would I have to have one specific for each datatype?
Ilmar
-
You might be little out-o-luck, as far as pursuing it with a UDF, since you can not have dynamic SQL code in a T-SQL UDF
. I think you may have to "parse" through your code to be able to translate the Fox code. If I think of something else, I'll try and post it.
Once you understand the BITs, all the pieces come together -
bummer..
I wonder if it would work to create a udf which called an extended stored procedure to emulate the functionality of the IIF()...
But I assume that even if it worked, it would probably be a major performance hit
-
Ya, there may be ways around doing what you want, but all the things I can think of are a lot harder than writing a quick UDF search & replace parser, and would execute orders of magnitude slower than a CASE structure.
One instance where a built-id function may work, is the IsNULL or COALESCE, but in Fox this be like IIF(EMPTY(...), exp1, exp2).
Good luck.
Once you understand the BITs, all the pieces come together -
The best way to do is to pass your scripts file through a Preprocessor ( like for compiling C programs ). Just define a macro IFF(a,b,c) as case when a then b else c end, and the proprocessor will change your code wihout problems.
I also wrote a little vb script to replace IIF() into case. Just drag and drop the files you want to be parsed on the script. I did not have the time to get a correct regular expression, but a file like this :
select A = IIF ( 1 = 2 , ABC , "test,'" ),
B=IIF ( tst.a > 10 , 1 , tst.A - 5 )
from tst
Select IIF(shipdate < getdate() , 'shipped','pending') as status from tst
will be changed to this :
select A = case when 1 = 2 then ABC else "test,'" end,
B=case when tst.a > 10 then 1 else tst.A - 5 end
from tst
Select case when shipdate < getdate() then 'shipped' else 'pending' end as status from tst
The script creates a subfolder IIF2SQL_parsedfiles in the folder where the dropped files are located, and recreates a parsed version of the input files there. Here is the script ( I called it IIF2SQL.vbs )
'---------------------------------------------------------------------------------------
'
' Name: IIF2SQL.vbs
' Version: 1.0
' Date: 26/05/2004
' Author: Bert De Haes
' Description: Parses dropped files and changes IIF() syntax to SQL case syntax
'
'---------------------------------------------------------------------------------------
' Default settings. Change if needed
Folder="IIF2SQL_parsedfiles"
strexpr = """(?:[^""]|"""")*""" & "|" & "'(?:[^']|'')*'"
numexpr = "[+-]?[0-9]*(?:[.][0-9]+)?"
colname = "[^'()]+"
operator= "\s*(?:[-+*/%<>]|<>|!=)\s*"
simpleexpr = "\s*(?:" & strexpr & "|" & numexpr & "|" & colname & ")\s*"
simpleexpr2 = simpleexpr & "(?:" & operator & "\s*" & simpleexpr & ")*"
Func = "\s*[^'()]+\s*\(" & simpleexpr2 & "(?:\s*,\s*" & simpleexpr2 & ")*\s*\)\s*"
simpleexpr3 = "\s*(?:" & simpleexpr2 & "|" & Func & ")\s*"
expr = "(\s*" & simpleexpr3 & "(?:" & operator & "\s*" & simpleexpr3 & ")*)"
IIFexpr = "IIF\s*" & "\(\s*" & expr & "\s*,\s*" & expr & "\s*,\s*" & expr & "\s*\)"
' -- End default settings --
on error resume next
'Use filesystem objects
set fso = CreateObject("Scripting.FileSystemObject")
'Show Error
Sub HadError(msg)
WScript.Echo msg & "ERROR " & CStr(Err.Number) & " : " & Err.Description
End Sub
Function ParseIIF(filename)
on error resume next
ParseIIF = false
set InputFile = fso.OpenTextFile(filename)
if err.Number <> 0 then
HadError "fso.OpenTextFile(""" & filename & """)"
Exit Function
End if
InputString = InputFile.readall()
InputFile.close
set InputFile = nothing
set RegEx = New RegExp
if err.Number <> 0 then
HadError "set RegEx = New RegExp"
Exit Function
End if
RegEx.Pattern = IIFexpr
RegEx.Global = true
RegEx.IgnoreCase = true
RegEx.Multiline = true
if err.Number <> 0 then
HadError "set RegEx properties"
Exit Function
End if
if RegEx.test(InputString) then
if err.Number <> 0 then
HadError "RegEx.test()"
Exit Function
End if
OutputString = InputString
' exec
set Matches = RegEx.Execute(InputString)
if isobject(Matches) then
For Each Match in Matches
OutputString = Replace(OutputString,Match.Value,"case when " & Match.submatches(0) & " then " & Match.submatches(1) & " else " & Match.submatches(2) & " end")
next
end if
set Matches = nothing
else
OutputString = InputString
end if
if err.Number <> 0 then
HadError "parsing file " & filename
set RegEx = nothing
Exit Function
End if
set RegEx = nothing
InputString=""
' write result to now file
OutputFolder = fso.BuildPath(fso.GetParentFolderName(filename),Folder)
IF not fso.FolderExists(OutputFolder) then fso.CreateFolder(OutputFolder)
OutputFileName = fso.BuildPath(OutputFolder , fso.GetFileName(filename))
Set OutputFile = fso.CreateTextFile(OutputFileName, True)
OutputFile.write (OutputString)
OutputFile.Close
set OutputFile = nothing
OutputString = ""
ParseIIF = true
end function
argc = WScript.Arguments.Count
for i=0 to argc-1
InputFileName = WScript.Arguments(i)
if fso.FileExists(InputFileName) then
' transform
ParseIIF(InputFileName)
else
msgbox InputFileName & " is not a file"
end if
next
set fso = nothing
-
Thanks.. that's the direction I finally took also..
-
Ilmar,
This is probably not the answer you want to hear, but you will probably be better off in the long run to recode you VFP SQL using as much native TSQL as possible.
I am also a VFP programmer, and I understand your problem and frustration.
You will probably need to make use of stored procedures to handle some of the logic you have embeded in your VFP SQL, especially where you are dynamically changing the SQL code.
A good use of SP and UDF will solve your problem. Be sure to look at UDFs that return a table -- very powerful.
If you need help getting started, post your VFP SQL here and ask for suggestions on how to code in TSQL. Be sure you make good use of the SS Books Online.
Good luck.
Best Regards,
apollois
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply