Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: error in using alias column name as function parameter

    Hi friends,

    I am working on migrating view from Ms Access to SQL server.
    I got a query and modified it by removing IIF by CASE WHEN.
    I landed into following query:


    Code:
    SELECT  CASE WHEN <CONDITION>
    	    THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
    	    ELSE 0
    	END AS CurrentDateAdj,
    	Year(CurrentDateAdj) + '_' + 'some text and processing')
    FROM INCREMENTDATATABLE;

    Here DAY1 and YR1 are from INCREMENTDATATABLE.
    I am getting error that CurrentDateAdj not found. How can I fix this?
    Last edited by c2tarun; 05-16-12 at 07:01.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    SELECT
        CASE WHEN <CONDITION>
            THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
            ELSE 0
        END AS CurrentDateAdj,
        CAST(Year(CASE WHEN <CONDITION>
                      THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
                      ELSE 0
                  END) AS CHAR(4)) + '_' + 'some text and processing')
    FROM INCREMENTDATATABLE;
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Quote Originally Posted by imex View Post
    Hi,

    Try:

    Code:
    SELECT
        CASE WHEN <CONDITION>
            THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
            ELSE 0
        END AS CurrentDateAdj,
        CAST(Year(CASE WHEN <CONDITION>
                      THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
                      ELSE 0
                  END) AS CHAR(4)) + '_' + 'some text and processing')
    FROM INCREMENTDATATABLE;
    Hope this helps.

    This solution worked for this particular query.
    The problem is this query is used to create a view. That view is used by another view which is also using CurrentDateAdj
    any suggestions for this?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have mixed opinions on nesting views.
    Yes, they do reduce redundant code, but they tend to be inefficient and devilishly difficult to debug.
    Honestly, I usually end up copying logic and code rather than referencing views within other views.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    I do not know if I understand correctly, but I believe in the subsequent view you can use the alias (CurrentDateAdj).

Posting Permissions

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