SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.5.502] - Copy SQL Code as:

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.5.502] - Copy SQL Code as:
Author Message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post [SA 7.5.502] - Copy SQL Code as: Reply with quote
I'm not quite sure if this is proper when converting the statement to VBScript:

Code:
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:

Code:

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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
Ok getting closer.

Now I just have a trailing and leading space in columns:

Code:
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
They will be gone if you remove leading and trailing spaces in the Line Separator option
Thu Oct 06, 2016 2:47 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
SysOp wrote:
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
Remove spaces around quotes, and it should do what you want.
Thu Oct 06, 2016 2:52 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
SysOp wrote:
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
SysOp wrote:
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
I'm sorry I'm not following.
Quote:
Conversion of full tables
what's that?
Thu Oct 06, 2016 3:15 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
SysOp wrote:
I'm sorry I'm not following.
Quote:
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
Sorry here's a smaller video:

http://screencast.com/t/WDgqUvLhld
Thu Oct 06, 2016 3:22 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.