IIF statement in View

[tucker61]

Is it possible to have a IIF statement in a view. Trying to get the below to work, but not being successful.

SELECT        dlv.Record_Type, dlv.[Option], dlv.Delivery_Reference, dlv.Contract, dlv.Supplier_Code AS Sup_Code, sup.Sup_Desc, dlv.Delivery_Date, dlv.Movement_Amount_Sign, dlv.Qty_Delivered, dlv.Expected_Date,
                         dlv.Original_Contract, dlv.Date_Updated, dlv.ID,iif(Left(dlv.Delivery_Reference,2)="01","Test")

 

[Gasman]

You also need the False part as well.

 

[CJ_London]

In sql, you don’t need to specify a false part of an iif statement. But you do need a FROM clause

Would also help if you said what the problem actually is since you will get an error if the delivery_reference is null or less than 2 characters in length

 

[Minty]

If this is in SQL Server, you would be much much better off learning to use the CASE WHEN THEN construct, it's much cleaner you can have as many conditions as you want and a ELSE option to catch other criteria and it's a lot easier to debug.

Something like

    SELECT
        dlv.Record_Type, dlv.[Option], dlv.Delivery_Reference,
        dlv.Contract, dlv.Supplier_Code AS Sup_Code,
        sup.Sup_Desc, dlv.Delivery_Date, dlv.Movement_Amount_Sign, dlv.Qty_Delivered,
        dlv.Expected_Date,  dlv.Original_Contract, dlv.Date_Updated,
        dlv.ID,
        CASE WHEN Left(dlv.Delivery_Reference,2) = '01'
            THEN 'Test' End As MyCalcField
           
    FROM YourTable

Syntax and more here
https://www.w3schools.com/sql/sql_case.asp

 

[tucker61]

If this is in SQL Server, you would be much much better off learning to use the CASE WHEN THEN construct, it's much cleaner you can have as many conditions as you want and a ELSE option to catch other criteria and it's a lot easier to debug.

Something like

    SELECT
        dlv.Record_Type, dlv.[Option], dlv.Delivery_Reference,
        dlv.Contract, dlv.Supplier_Code AS Sup_Code,
        sup.Sup_Desc, dlv.Delivery_Date, dlv.Movement_Amount_Sign, dlv.Qty_Delivered,
        dlv.Expected_Date,  dlv.Original_Contract, dlv.Date_Updated,
        dlv.ID,
        CASE WHEN Left(dlv.Delivery_Reference,2) = '01'
            THEN 'Test' End As MyCalcField
          
    FROM YourTable

Syntax and more here
https://www.w3schools.com/sql/sql_case.asp

Can this be done within the "View"?

 

[Minty]

Yes - a View is at it's most basic level is simply a Select query.
It can be as complicated as you like, but can't take parameters.

 

[Isaac]

I actually use IIF a lot since it came out because a lot of my reports have Y/N columns requested for the output.
for example if I'm left joining to a table, it is simpler to code

Column_YN = iif(joinedtable.column is not null,'Y','N')

This was a long time in coming to sql server and very appropriate for short statements with binary condition.

But Case is great for separating multiple conditions.

 

[Isaac]

you will get an error if the delivery_reference is null or less than 2 characters in length

 

[Minty]

I actually use IIF a lot since it came out because a lot of my reports have Y/N columns requested for the output.
for example if I'm left joining to a table, it is simpler to code

Column_YN = iif(joinedtable.column is not null,'Y','N')

This was a long time in coming to sql server and very appropriate for short statements with binary condition.

But Case is great for separating multiple conditions.

I know it's been available for some time (2014?) , but I'm just not massive fan in T-SQL as people have a habit of nesting them in Access and then trying to decipher them after the event is a bit of a mare.

 

[CJ_London]

Column_YN = iif(joinedtable.column is not null,'Y','N')

Assuming the query is to be used in a form or report I prefer to use the control format property to display Y or N

And for nested iif’s I prefer to use the switch function, or choose function if underlying values increment by 1

 

[Isaac]

I know it's been available for some time (2014?) , but I'm just not massive fan in T-SQL as people have a habit of nesting them in Access and then trying to decipher them after the event is a bit of a mare.

Totally agree, they can easily be misused or over used and it becomes pretty ugly

 

[Isaac]

Column_YN = iif(joinedtable.column is not null,'Y','N')

Assuming the query is to be used in a form or report I prefer to use the control format property to display Y or N

And for nested iif’s I prefer to use the switch function, or choose function if underlying values increment by 1

Oh, that's a good idea too. I am thinking in non-access pure sql reporting but yeah the control format is a great idea too