JOIN no longer on new line?

Dave Pendleton

My JOIN clauses are no longer on a new line, and no setting seems to affect this.

What happened?

Anu D

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.

Dave Pendleton

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.

srosenbach

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!

Anu D

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.

Anu D

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.