Link to home
Start Free TrialLog in
Avatar of williamj67
williamj67

asked on

SQL LINE CONTINUATION ISSUE

Could someone please tell me why this sql statement works fine when placed in one continuous line.
                 
   SQL.ExecQuery("Select p.lastname, p.datedeath, COALESCE(r.feetotal, 0) As feetotal, COALESCE(s.paytot, 0) As paytotal, COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0)  As Balance from client p left join (Select clientid, sum(fee) As Feetotal from fees group by clientid)r On r.clientid = p.id left join (Select clientid, sum(payment) As paytot from Financial group by clientid)s On s.clientid = p.id where (COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0) > 0) And p.datedeath between '" & dtarfrom.Text & "' and '" & dtarto.Text & "' order by p.datedeath asc")

Open in new window


but not when set up like below in multiple lines even though intellisense says all is good.
   
   'SQL.ExecQuery("select p.lastname, p.datedeath, COALESCE(r.feetotal,0) as feetotal, COALESCE(s.paytot,0) as paytotal,COALESCE(r.feetotal,0)-COALESCE(s.paytot,0)  AS Balance" _
                    '              "from client p left join (select clientid,sum(fee) as Feetotal from fees group by clientid)r on r.clientid = p.id left join (select clientid,sum(payment) as paytot from Financial group by clientid)s on s.clientid = p.id" _
                    '              "where  (COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0) > 0) And p.datedeath between '" & dtarfrom.Text & "' and '" & dtarto.Text & "'  order by p.datedeath asc")

Open in new window

P.S. I know it is blocked out.  I must have the breaks in wrong positions but it runs just doesn't work.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Remove the double quotes (") and single quotes ('):
'SQL.ExecQuery("select p.lastname, p.datedeath, COALESCE(r.feetotal,0) as feetotal, COALESCE(s.paytot,0) as paytotal,COALESCE(r.feetotal,0)-COALESCE(s.paytot,0)  AS Balance
            from client p left join (select clientid,sum(fee) as Feetotal from fees group by clientid)r on r.clientid = p.id left join (select clientid,sum(payment) as paytot from Financial group by clientid)s on s.clientid = p.id
            where  (COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0) > 0) And p.datedeath between '" & dtarfrom.Text & "' and '" & dtarto.Text & "  order by p.datedeath asc")

Open in new window

Use the return key to create a new line. You can also use a variable and build the SQL command with the variable and the use the variable in the ExecQuery procedure.
Avatar of williamj67
williamj67

ASKER

I know it's blocked out.(single quotes)  Intellisense will not let the double quotes be removed.  I added an ampersand to each line to extend the line.  That also runs but doesn't work.  Here is the code
When this is in one line works fine.  There is something about breaking up coalesece statements that it doesn' like.

 SQL.ExecQuery("select p.lastname, p.datedeath, COALESCE(r.feetotal,0) as feetotal, COALESCE(s.paytot,0) as paytotal,COALESCE(r.feetotal,0)-COALESCE(s.paytot,0)  AS Balance" _
                                 & "from client p left join (select clientid,sum(fee) as Feetotal from fees group by clientid)r on r.clientid = p.id left join (select clientid,sum(payment) as paytot from Financial group by clientid)s on s.clientid = p.id" _
                                & "where  (COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0) > 0) And p.datedeath between '" & dtarfrom.Text & "' and '" & dtarto.Text & "'  order by p.datedeath asc")

Open in new window

Then you need a space before or after breaking the line so the command will not be concatenated with the previous word from the line before:
 SQL.ExecQuery("select p.lastname, p.datedeath, COALESCE(r.feetotal,0) as feetotal, COALESCE(s.paytot,0) as paytotal,COALESCE(r.feetotal,0)-COALESCE(s.paytot,0)  AS Balance " _
            & "from client p left join (select clientid,sum(fee) as Feetotal from fees group by clientid)r on r.clientid = p.id left join (select clientid,sum(payment) as paytot from Financial group by clientid)s on s.clientid = p.id " _
            & "where  (COALESCE(r.feetotal, 0) - COALESCE(s.paytot, 0) > 0) And p.datedeath between '" & dtarfrom.Text & "' and '" & dtarto.Text & "'  order by p.datedeath asc")

Open in new window

There is a space before the continuation symbol and a space after.   This is more complicated than the basic.  There is something about where it gets continued.  Certain sql statements can't be broken up.  I have tried various break points with no success.   If it breaks a line in the middle of a statement sql doesn't recognize it under certain conditions.  I need to know where I need to put the breaks In this statement.  where I have them now doesn't work.
Again, it runs fine doesn't work.  When I put it back to one line, works fine.
Hi williamj67;

From Microsoft Documentation, please note the underline section.
Use the line-continuation character, which is an underscore (_), at the point at which you want the line to break. The underscore must be immediately preceded by a space and immediately followed by a line terminator (carriage return).
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I appreciate your help, but this already has the underscore in there.  This statement is part of a project with 20K lines of code  and has many sql statements that are continued.  I know how to continue sql to the next line.  there is something about the structure of this statement that is creating this situation.  It's not all that important as it works fine in a single line.  It is just difficult to read and I would like it to be like all the others.  This is about where it is continued.  Again intellisense doesn't have an issue. if it was a syntax issue in .net then .net would highlight it.
The continuation character must also be  immediately followed by a line terminator (carriage return). Have you checked that as well?
Yes I have checked and rechecked line continuation underscore, etc.   I have tried adding quotes and ampersands like others suggested, I've tried just line continuation.  .net doesn't have an issue with the syntax it just doesn't work unless it is in one line.  I read somewhere that SQL doesn't like line continuations in certain statements depending on where the line is continued.  I think that is the issue here.  it has something to do with the joins.
For Vitor,  This doesn't work as a variable running cmd.executenonquery either.   Works in one line not multiple.

Thank you all for your assistance, I am not going to spend more time on this.  I don't know how to close this without an answer.
I don't know how to close this without an answer.
Just request the question to be deleted.
Cheers
After placing the sql in a variables and correcting for the spacing, I was able to get this to run.  Thanks  Vitor