Options

Format SQL mistaking 'at time zone' as column alias

naumadnaumad Posts: 19 Bronze 1
edited March 22, 2017 11:48AM in SQL Prompt
Format SQL is mistaking the 'at time zone' clause as a column alias and attempts to add 'AS' before 'at time zone'.
· Share on Twitter

Comments

  • Options
    James RJames R Posts: 104 Silver 4
    Hi naumad,

    Thanks for your feedback.
    We could do with a bit more information to reproduce this bug, could you post an example script where the issue occurs?

    Thanks,
    James
    Software Engineer
    Redgate Software
    · Share on Twitter
  • Options
    naumadnaumad Posts: 19 Bronze 1
    I have a few queries where I convert a timestamp to utc and then to a local timezone (from a joined table) as such:

    convert(date, x.timestamp at time zone 'UTC' at time zone tz_name) as timestamp_local

    SQL Prompt will sometimes try to add 'AS' before each 'at' in the statement.
    · Share on Twitter
  • Options
    James RJames R Posts: 104 Silver 4
    Hi naumad,

    Unfortunately I still can't reproduce this locally. Could you tell me a few more things?

    1) What version of SQL Prompt are you using?

    2) What option do you have set for SQL Prompt > Options > Actions > Apply column alias style?

    3) Does this issue still occur if you just use the inbuilt timezones (e.g 'UTC') and not the local timezone from the joined table?

    Kind regards,
    James
    Software Engineer
    Redgate Software
    · Share on Twitter
  • Options
    naumadnaumad Posts: 19 Bronze 1
    1) What version of SQL Prompt are you using?
    SQL Prompt 7.5.0.675

    2) What option do you have set for SQL Prompt > Options > Actions > Apply column alias style?
    column AS alias

    3) Does this issue still occur if you just use the inbuilt timezones (e.g 'UTC') and not the local timezone from the joined table?

    If I use a literal value, this problem does not occur.
    · Share on Twitter
  • Options
    Michael ClarkMichael Clark Posts: 123 Silver 4
    Just a quick note -- we are tracking this alias bug as SP-6200
    Michael Clark
    Technical Lead, SQL Monitor
    · Share on Twitter
  • Options
    Michael ClarkMichael Clark Posts: 123 Silver 4
    Hi naumad,

    Like James, I'm having trouble reproducing this one. Would it be possible to have a larger script where the problem occurs?

    Would it be possible to try our new formatting engine and let us know if the problem still occurs? This is currently an experimental feature which you can turn on by going to "SQL Prompt > Options > Labs > Experimental Features" and selecting the "Use new formatting styles" checkbox. That will help us narrow down the cause.

    Thanks,

    Michael Clark
    Technical Lead, SQL Prompt
    Michael Clark
    Technical Lead, SQL Monitor
    · Share on Twitter
Sign In or Register to comment.