![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
[SA 7.5.502] - Copy SQL Code as: |
|
I'm not quite sure if this is proper when converting the statement to VBScript:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Dim sql
sql = "SELECT " & vbCrLf _
& " RunNo, " & vbCrLf _
& " RunID, " & vbCrLf _
& " InstrumentID, " & vbCrLf _
& " RunStartDate, " & vbCrLf _
& " Analyst, " & vbCrLf _
& " Comments, " & vbCrLf _
& " Made, " & vbCrLf _
& " Updated, " & vbCrLf _
& " UpdateBy, " & vbCrLf _
& " IcalDate, " & vbCrLf _
& " ColumnID, " & vbCrLf _
& " CalID, " & vbCrLf _
& " [Active], " & vbCrLf _
& " CurveEq, " & vbCrLf _
& " MadeBy, " & vbCrLf _
& " AutoRef, " & vbCrLf _
& " AnalRunStamp, " & vbCrLf _
& " InUse, " & vbCrLf _
& " InUseBy " & vbCrLf _
& "FROM " & vbCrLf _
& " [DHL].[dbo].[AnalRun]" |
Generally code continuations in VBScript/VBA are simply "& _ " and not "& vbCrLf _"
All this seems to do is 'prettify' the SQL statement when if you actually print the statement out.
Where removing the leading "&" and the trailing vbCrLF gets you something more like:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Dim sql
sql = "SELECT " & _
" RunNo, " & _
" RunID, " & _
" InstrumentID, " & _
" RunStartDate, " & _
" Analyst, " & _
" Comments, " & _
" Made, " & _
" Updated, " & _
" UpdateBy, " & _
" IcalDate, " & _
" ColumnID, " & _
" CalID, " & _
" [Active], " & _
" CurveEq, " & _
" MadeBy, " & _
" AutoRef, " & _
" AnalRunStamp, " & _
" InUse, " & _
" InUseBy " & _
"FROM " & _
"AnalRun" |
It there really a point to 'prettifying' the SQL statement for execution?
If no, is there a way to adjust the syntax of the "Copy SQL as.."?
|
|
Thu Oct 06, 2016 12:10 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Hi. Yes you can customize it. In the same menu where you click Copy SQL Code as -> VBScript, click the last menu item Modify Language Formats...
Hope this helps.
|
|
Thu Oct 06, 2016 12:49 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
Ok getting closer.
Now I just have a trailing and leading space in columns:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Dim sql
sql = "SELECT " & _
" RunNo, " & _
" RunID, " & _
" InstrumentID, " & _
" RunStartDate, " & _
" Analyst, " & _
" Comments, " & _
" Made, " & _
" Updated, " & _
" UpdateBy, " & _
" IcalDate, " & _
" ColumnID, " & _
" CalID, " & _
" [Active], " & _
" CurveEq, " & _
" MadeBy, " & _
" AutoRef, " & _
" AnalRunStamp, " & _
" InUse, " & _
" InUseBy " & _
"FROM " & _
" [DHL].[dbo].[AnalRun]" |
Any way to remove that leading space from column names? It doesn't apply to the "FROM" criteria.
|
|
Thu Oct 06, 2016 1:52 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
They will be gone if you remove leading and trailing spaces in the Line Separator option
|
|
Thu Oct 06, 2016 2:47 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
They will be gone if you remove leading and trailing spaces in the Line Separator option |
There is no trailing space in the line separator:
*Edit: removing the leading space from the top quote (" & _) removes the space after the comma in the columns..
|
|
Thu Oct 06, 2016 2:49 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Remove spaces around quotes, and it should do what you want.
|
|
Thu Oct 06, 2016 2:52 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Remove spaces around quotes, and it should do what you want. |
I've done that.
So, it turns out:
IF I'm converting a statement I wrote with no whitespace before the columns such as this:
It converts fine.
But if I right click a table and do "copy code as" I get the leading spaces on the columns no matter how I set my line separator preferences.
|
|
Thu Oct 06, 2016 2:55 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
HOWEVER:
If I format my code with Ctrl-F11 and then convert it, it gets even wonkier.
Perhaps the conversion should not take formatted whitespace into account?
|
|
Thu Oct 06, 2016 2:56 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
Here's a video of me converting a table DDL to VBScript:
http://screencast.com/t/JRyLAZl0
Note the leading spaces. Again no matter how I adjust my line separator, I can get rid of the trailing spaces after column commas but not leading spaces before the column name inside the quotation marks.
|
|
Thu Oct 06, 2016 3:00 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
It literally takes every line in the highlighted text, appends to each line the text specified in Line Separator, concatenates all that back and finally adds prefixes and suffixes from Statement Prefix and Statement Suffix options. The mechanics of that function are pretty simple.
|
|
Thu Oct 06, 2016 3:01 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
These leading spaces are in your code in the editor. If you remove them, they will be gone from the results. There isnt any automatic trimming or any kind of code parsing.
Last edited by SysOp on Thu Oct 06, 2016 3:13 pm; edited 1 time in total |
|
Thu Oct 06, 2016 3:03 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
These leading spaces are in your code in the editor. If you remove them, the will be gone from the results. There no automatic trimming or any kind of code parsing. |
Right, but there's still at least one leading space being added in certain instances (conversion of full tables for example).
|
|
Thu Oct 06, 2016 3:04 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
I'm sorry I'm not following.
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Conversion of full tables |
what's that?
|
|
Thu Oct 06, 2016 3:15 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
I'm sorry I'm not following.
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Conversion of full tables |
what's that? |
Watch my screencast above where I convert the table DDL to a select statement (vbscript)
here's the URL again: http://screencast.com/t/JRyLAZl0
|
|
Thu Oct 06, 2016 3:17 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
Thu Oct 06, 2016 3:22 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|