July 29, 2008 at 7:56 am
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
July 29, 2008 at 8:37 am
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
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
July 29, 2008 at 9:00 am
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.
July 29, 2008 at 9:20 am
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
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
July 29, 2008 at 9:31 am
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
July 29, 2008 at 9:36 am
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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply