Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Oct 2010
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Grid View and use of 'AS' in query.

    This is my query:

    SELECT [name1], [name2], [mail_street1], [mail_street2], [mail_city], [mail_state], [mail_zip], [phone], [website] FROM [flat_list] WHERE (([mail_state] = 'NJ') AND ([rl] = 'x' OR [rs] = 'x')) ORDER BY [name1], [name2]

    This is what I am trying to accomplish: [mail_street1], [mail_street2], [mail_state], [mail_zip] (Combine into one field).

    I am working on a couple of assumptions right now. One being that when I use the 'AS' the Grid View will be smart enough to reformat itself to reflect the fact that there is data that used to be in multiple columns that is now one. Two is that the use of 'AS' is actually possible with this query. I am honestly quite lost as to how to implement the 'AS' to combine said data. Can someone provide some input on this? Thank you.

  • #2
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    The answer may be different for other database but for SQL Server you probably want something like this:

    select name1, name2, mail_street1 + ' ' + mail_street2 + ' ' + mail_state + ' ' + mail_zip as [Mailing Address]... etc.

    You can keep the square brackets as in your original example, but they are only necessary if your field names are reserved words, or they contain spaces, or other punctuation characters.

  • #3
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am really can't exact understand your topic.But i have one solution that you can replace your query with this.
    SELECT [name1] as Firstname, [name2] as Lastname, [mail_street1] a Email_Address, [mail_street2], [mail_city] as City, [mail_state] as State, [mail_zip] as Zip, [phone] as Phone, [website] FROM [flat_list] WHERE (([mail_state] = 'NJ') AND ([rl] = 'x' OR [rs] = 'x')) ORDER BY [name1], [name2]


  •  

    Posting Permissions

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