Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    78

    Question Unanswered: Column Alias in views

    Hi All,
    I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.

    e.g.
    Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;

    In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.

    My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?

    Thanks for your time.

    Regards:
    Prathmesh

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi

    try this

    Select field1 as Alias1, field2 as Alias2, field1 + ' ' + field2 as Alias3 from table1;


    hope this will solve ur problem
    Cheers....

    baburajv

  3. #3
    Join Date
    Oct 2003
    Posts
    78
    Hi,
    Ok, I think I need to explain a bit more detail. I have got a database table that stores data about different equipments. Each equipment is identified by 3 distinct fields Area, Type, No. So a particular equipment tag would be of type:
    Area+Type+No.

    Now at the same time the table also holds the description of the equipment which comes from 2 fields desc1 and desc2. So the whole equimment desc would be desc1+desc2

    Now on the reports the equiptag and equipment desc need to be concatenated to form one equipment number i.e. Area+type+No+Desc1+desc2

    So what I wanted to do was
    Select Area+type+No as Equiptag, Desc1+Desc2 As EquipDesc, EquipTag+EquipDesc As EquipNo from equipment;

    but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

    So, Is there any way to do this?

    Regards:
    Prathmesh

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Prathmesh
    Hi,

    So what I wanted to do was
    Code:
    Select 
      Area+type+No as Equiptag, 
      Desc1+Desc2 As EquipDesc, 
      EquipTag+EquipDesc As EquipNo 
    from 
      equipment;
    but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

    So, Is there any way to do this?
    To the best of my knowledge, you can't use an alias as part of a formula within the same SQL. You would either have to do this:
    Code:
    Select 
      Area+type+No as Equiptag, 
      Desc1+Desc2 As EquipDesc, 
      Area+type+No+Desc1+Desc2 As EquipNo 
    from 
      equipment;
    or you could try creating a subquery like this:

    Code:
    SELECT
      t.EquipTag,
      t.EquipDesc,
      t.EquipTag+t.EquipDesc As EquipNo 
    FROM
      (SELECT
         Area+type+No as Equiptag, 
         Desc1+Desc2 As EquipDesc
       FROM
         equipment) t
    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Oct 2003
    Posts
    78
    Thanks hmscott,
    The subquery idea is a good one. I'll give it a try. I was just curious if this could be done similar to Access or not. I must say, being an Access programmer, there are certain things in SQL Server which really annoy you. Most of my queries use this type of aliasing, so I now have to go and rewrite them to replace Aliasing.

    Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.

    Thanks.

    Regards:
    Prathmesh

  6. #6
    Join Date
    Feb 2005
    Posts
    78
    All databases are different. All databases have things that are worse than other databases or extra things that are better than other databases. There is no reason. What is included in the SQL Standard should be the same accross databases but for anything else ...

  7. #7
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi Prathmesh,

    try this

    SELECT ISNULL(columnwithnull,'') + nonnullcolumns from yourtable
    Cheers....

    baburajv

  8. #8
    Join Date
    Oct 2003
    Posts
    78
    Hi baburaj,
    Yep, that is what I am using now. However, I have decided on something else. I am planning to use SQL Server backend to Access frontend, because all my forms , reports, etc. are in Access.I am going to do all the complex join queries on SQL Server side as views and link the tables via odbc to Access using the Access "link tables" facility and the required formatting I will still do on Access side. This way I can have best of both worlds. I can make use of SQL server's performance and Access' formatting features.

    Thanks to all for your help and suggestion guys.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Prathmesh
    Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.
    Not entirely true - Access also provides the "+" concatenation operator where Null + "Something" = Null.
    Rather than thinking of it as a bind you need to think through the implications. The + operator is great, for example, when putting together a csv address string for presentation - you don't need to use a load of conditional statements to exclude the comma if, for example, the address has no House Name.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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