IF - Then , Yes or No Statement

Question

Post reply

IF - Then , Yes or No Statement

pete.trudell

Right there with Babe

Points: 793
More actions
July 29, 2008 at 7:56 am

#188807

I am a novice, and I need to dupicate a formula used in Access in a Stored Procedure of our Database. The Access Column reads as : LINA:IIF([Start Time]>[Login Time],"No","Yes"). How do I write this for a Stored procedure? The Access frontend will not let me enter it in the Column Name as given above. All I get is the The above Code repeated in the column called LINA.

Appreciate any Help.

Pete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

pete.trudell (7/29/2008)

I am a novice, and I need to dupicate a formula used in Access in a Stored Procedure of our Database. The Access Column reads as : LINA:IIF([Start Time]>[Login Time],"No","Yes"). How do I write this for a Stored procedure? The Access frontend will not let me enter it in the Column Name as given above. All I get is the The above Code repeated in the column called LINA.

Appreciate any Help.

Pete

The TSQL equivalent of an Immediate IF is a CASE:

CASE WHEN [Start Time] > [Login Time] THEN "No" ELSE "Yes" END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pete.trudell Right there with Babe Points: 793 More actions · Answer 2

pete.trudell

Right there with Babe

Points: 793

July 29, 2008 at 9:00 am

#849868

Thank You , that worked fine. Now How do I refer to that Alias (and others) in other columns I create? Stored Procedures doesn't like to refer to anything but Table Columns.

Thanks Pete

PS: Is there a general Basic Stored Procedures White Paper or Reference available.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

I'm not entirely sure what you mean...but you would usually apply an alias to a CASE construct in a SELECT in one of two ways:

SELECT NewColumnName = CASE WHEN [Start Time] > [Login Time] THEN "No" ELSE "Yes" END, ...

or

SELECT CASE WHEN [Start Time] > [Login Time] THEN "No" ELSE "Yes" END as NewColumnName, ...

HTH

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pete.trudell Right there with Babe Points: 793 More actions · Answer 4

Sorry, In Access 2007 I am creating my Stored procedure for our SQL 2005 Database. The Da Vinci layout gives me the Tables, and from which I select the Columns for my Stored procedure. Those column I wish to create have an Alias Column so all I need do is Write the Code in the Name Column and the Assigned name in the Alias Column:

CASE WHEN [Start Time]>[Login Time] THEN 'No' ELSE 'Yes' END | LINA

In the results the records list LINA as a Column Header with the results of the code in the column (Yes or No).

However, I now need to reference the LINA Data in another created Column , but Access and SQL will not let me refernce a created column, only those Columns which are part of a Table.

Thanks , Pete

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi Pete, I'm really sorry I can't help you any further with this, I don't have the client tools you are using. I'm sure someone will hop in shortly and help you.

Good luck

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden