dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] Can I write a view with IIF in it?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,093

    Resolved [RESOLVED] Can I write a view with IIF in it?

    I guess a long time ago, before IIF was invented (or available to us), my fellow developer wrote his own iif function that went like this:

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Create date: 8-May-2015
    -- Description:	Returns value 1 for True and Value 2 for false
    -- =============================================
    ALTER FUNCTION [dbo].[fn_IIF] 
    (
    	-- Add the parameters for the function here
    	@Test1 varchar(max),
    	@Test2 varchar(max),
    	@ReturnTrue varchar(max),
    	@ReturnFalse varchar(max)
    )
    RETURNS varchar(max)
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE @Result varchar(max)
    
    	-- Add the T-SQL statements to compute the return value here
    	IF @Test1 = @Test2
    	BEGIN
    		SET @Result = @ReturnTrue
    	END
    	ELSE
    	BEGIN
    		SET @Result = @ReturnFalse
    	END
    
    
    	-- Return the result of the function
    	RETURN @Result
    
    END
    It's been just about a year ago that he said, "I wrote IIF for SQL 2005, I think theres now a similar included function in TSQL anyway". I am just now getting around to eliminating his homegrown function and using SQL's.

    Is this just a syntax error or you can't use this function (any function) within a view?

    Code:
    ALTER VIEW [dbo].[qryP21_ImpactReport2]
    AS
    SELECT        TOP (100) PERCENT ... 
    						 
    						 --dbo.fn_IIF(ISNULL(P21.dbo.p21_view_document_line_serial.serial_number, ''), '', CONVERT(decimal(18, 0), P21.dbo.p21_view_invoice_line.qty_shipped), 1) AS Quantity, 
    						 IIF( ISNULL(P21.dbo.p21_view_document_line_serial.serial_number, '') = '',  CONVERT(decimal(18, 0), P21.dbo.p21_view_invoice_line.qty_shipped) ) AS Quantity, 
    ...
    When I try to alter this view, I get Incorrect syntax near ')' and it is pointing to the close paren right before AS Quantity. I am pretty sure my parens are lined up so I am not sure what is wrong, unless I simply can't do this.

    Thank You.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    March 2015 Winner kfcSmitty's Avatar
    Join Date
    May 2005
    Location
    Kingston, Ontario
    Posts
    2,153

    Re: Can I write a view with IIF in it?

    I may be misreading this, but it looks like you currently have

    IIF(boolean check, do this)

    You're missing your else, which is the "1" on the line above... it looks like you should have

    Code:
    IIF( ISNULL(P21.dbo.p21_view_document_line_serial.serial_number, '') = '',  CONVERT(decimal(18, 0), P21.dbo.p21_view_invoice_line.qty_shipped), 1) AS Quantity,

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,093

    Re: Can I write a view with IIF in it?

    Hunh. How I ever didn't see that on my own, I can't even begin to explain. Many thanks for being my eyes today!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width