What are the challenges you face when working across database platforms? Take the survey
Options

JOIN no longer on new line?

Dave PendletonDave Pendleton Posts: 104 Bronze 3
edited June 12, 2009 4:47AM in SQL Prompt Previous Versions
My JOIN clauses are no longer on a new line, and no setting seems to affect this.

What happened?

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    Thanks you for your post.

    Layout settings for Join conditions can be configured by selecting SQL Prompt Options.
    Kindly check the settings by navigating to SQL Prompt --> Options --> Layout --> Data Statements --> Place Join Condition on a new line.

    This should solve your purpose kindly check if the appropriate settings are done!

    You can also find few other layout options for Join conditions in the same tab like Align JOIN with FROM and Align join condition with JOIN

    Kindly let us know if you have any other queries regarding SQL Prompt.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    Dave PendletonDave Pendleton Posts: 104 Bronze 3
    Actually, I was referring to the JOIN clause itself, not the JOIN condition.

    When formatting several large files the behavior would occur randomly; sometimes the JOIN would either share a line with the FROM or another JOIN. Other times the JOIN would be on it's own line.

    Closing the file, reopening it, and reformatting would sometimes fix the issue.
  • Options
    Actually, I have the opposite problem - it seems as if SQL Prompt adds a newline after FROM and JOIN and puts the rest of the FROM and JOIN clauses on the next line, like this:

    SELECT
    fp.PatientID
    , tc.ProgID
    , fp.DataCollectionDate
    FROM
    dbo.FormPat fp
    JOIN
    dbo.TxCyclePhase tcp ON fp.TxCyclePhase = tcp.TxCyclePhaseID
    JOIN
    dbo.TxCycle tc ON tcp.TxCycle = tc.TxCycleID
    JOIN
    dbo.Patient p ON fp.PatientID = p.PatientID

    whereas what I want is this:

    SELECT
    fp.PatientID
    , tc.ProgID
    , fp.DataCollectionDate
    FROM dbo.FormPat fp
    JOIN dbo.TxCyclePhase tcp ON fp.TxCyclePhase = tcp.TxCyclePhaseID

    Can anyone help me with this? Many thanks!
    Steve Rosenbach
    Sr. Database Administrator
    University of Maryland
    School of Medicine
    Dept of Psychiatry
  • Options
    Anu DAnu D Posts: 876 Silver 3
    edited June 12, 2009 4:48AM
    Thanks for your post Steve.

    I tried replicating the issue and following settings made it work.
    Check the following settings:
    1. SQL Prompt --> Options --> Layout --> Data statements --> Uncheck Place Join condition on new line.
    2. SQL Prompt --> Options --> Layout --> Data statements --> Check Align JOIN with FROM.

    My query before setting:
    SELECT
    fp.PatientID
    , tc.ProgID
    , fp.DataCollectionDate
    FROM
    dbo.FormPat fp
    JOIN
    dbo.TxCyclePhase tcp ON fp.TxCyclePhase = tcp.TxCyclePhaseID
    JOIN
    dbo.TxCycle tc ON tcp.TxCycle = tc.TxCycleID
    JOIN
    dbo.Patient p ON fp.PatientID = p.PatientID

    Query after setting:

    SELECT fp.PatientID
    , tc.ProgID
    , fp.DataCollectionDate
    FROM dbo.FormPat fp
    JOIN dbo.TxCyclePhase tcp ON fp.TxCyclePhase = tcp.TxCyclePhaseID
    JOIN dbo.TxCycle tc ON tcp.TxCycle = tc.TxCycleID
    JOIN dbo.Patient p ON fp.PatientID = p.PatientID

    Kindly let me know if that worked for you.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    Anu DAnu D Posts: 876 Silver 3
    Hello Dave,

    Thanks for update and sorry you are having problem with Layout functionality of SQL Prompt.

    I tried replicating your issue but was not able to.
    Could you kindly explain me stepwise pattern to replicate it?
    If you want to explain it and send some test queries kindly email us at support@red-gate.com and we can take it forward from there?

    Thank you for your patience with this issue.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.