SQLinForm Forum

Desktop Version Release "2012 April " available (:D (no replies)

guidomarcel — Fri, 27 Apr 2012 23:20:57 +0200

The new Desktop Version Release "2012 April " is available.

Dynamic sql does not work (no replies)

Martin — Wed, 25 Apr 2012 14:34:20 +0200

Formatting dynamic sql doesn't work with embedded single quotes

Unformatted: SELECT 'str' AS [Value]
Expected: SELECT @sql=N'SELECT ''str'' AS [Value]'
sqlinform: SELECT @sql=N'SELECT 'str' AS [Value]'

It also doesn't work the other way

Unformatted: SELECT @sql='SELECT ''str'' AS [Value]'
Expected: SELECT 'str' AS [Value]
sqlinform: SELECT str AS [Value]

Free Single User License for Translations (no replies)

guidomarcel — Tue, 03 Apr 2012 23:41:58 +0200

Hello,
I am offering a free SQLinForm Single User License for a qualified translation in Italian, Dutch, Spanish, Potuguese, Swedish, Korean,... of the SQLinForm Homepage. If you are interested, please contact me at info@sqlinform.com.

Regards
Guido

[started] Linebreak / alignment options? (1 reply)

Luthien — Thu, 29 Mar 2012 18:18:44 +0200

Hi,

I wonder if it is possible to also enable line-breaks / alignment for these two cases:

1) align comma-separated parameters in function calls, like for instance in this fragment:

...
FROM  (SELECT  nnp1.rowid                                                               rowid1
              , nnp2.rowid                                                              rowid2
       FROM     xwga_vrw_nnp nnp1
       JOIN     xwga_vrw_nnp nnp2
       ON       nnp1.ber_id                                         = nnp2.ber_id
       AND      nnp2.verw_id                                        > nnp1.verw_id
       AND      NVL(nnp1.new_failsurs_datb, nnp1.old_failsurs_datb) = NVL
                (nnp2.new_failsurs_datb, nnp2.old_failsurs_datb)
       WHERE    nnp1.verwerken                                                = 'J'
       AND      nnp1.time_processed                                     IS NULL
       AND      NVL(nnp1.new_failsurs_datb, nnp1.old_failsurs_datb) IS NOT NULL
       AND      nnp2.verwerken                                                = 'J'
       AND      nnp2.time_processed                                     IS NULL
       AND      NVL(nnp2.new_failsurs_datb, nnp2.old_failsurs_datb) IS NOT NULL
       ORDER BY nnp1.verw_id
              , nnp2.verw_id)
WHERE  rownum = 1;

-- ontdubbel FSS
p_ontdubbel_nnp(p_batch_id, f_bepaal_nnp_rij(rowid1), f_bepaal_nnp_rij(rowid2), 'FSS');

so that that would be something like this:

...
FROM  (SELECT   nnp1.rowid                      rowid1
              , nnp2.rowid                      rowid2
       FROM     xwga_vrw_nnp nnp1
       JOIN     xwga_vrw_nnp nnp2
       ON       nnp1.ber_id                   = nnp2.ber_id
       AND      nnp2.verw_id                  > nnp1.verw_id
       AND      NVL(nnp1.new_failsurs_datb
                  , nnp1.old_failsurs_datb) 
              = NVL(nnp2.new_failsurs_datb
                  , nnp2.old_failsurs_datb)
       WHERE    nnp1.verwerken                = 'J'
       AND      nnp1.time_processed               IS NULL
       AND      NVL(nnp1.new_failsurs_datb
                  , nnp1.old_failsurs_datb)   IS NOT NULL
       AND      nnp2.verwerken                = 'J'
       AND      nnp2.time_processed               IS NULL
       AND      NVL(nnp2.new_failsurs_datb
                  , nnp2.old_failsurs_datb)   IS NOT NULL
       ORDER BY nnp1.verw_id
              , nnp2.verw_id)
WHERE  rownum = 1;

-- ontdubbel FSS
p_ontdubbel_nnp(p_batch_id
              , f_bepaal_nnp_rij(rowid1)
              , f_bepaal_nnp_rij(rowid2)
              , 'FSS');

Which makes to overall length of the code a bit longer but more readable in my opinion; long lines are harder to follow with your eyes.

or 2) before the THEN in IF-THEN-ELSE conditions or CASE statements (there's an option to insert a break for the THEN keyword in CASE statements, but it does not seem to do anything?)
This is what I can get now:

CASE
WHEN r_signal.RT_NM_WORD = 'NI' THEN
   v_knight_id              := f_figure_out(r_signal.castle_id, p_batch_words) ;
WHEN r_signal.RT_NM_WORD = 'IT' THEN
   v_knight_id              := f_figure_out(r_signal.swamp_id, p_batch_words) ;
END CASE;

IF(p_rule_id = 1452) THEN
   engage_warp_drive('bicycle')
END IF;

and this is what I'd like to get:

CASE
WHEN r_signal.RT_NM_WORD = 'NI' 
THEN v_knight_id := f_figure_out(r_signal.castle_id
                               , p_batch_words) ;
WHEN r_signal.RT_NM_WORD = 'IT' 
THEN v_knight_id := f_figure_out(r_signal.swamp_id
                               , p_batch_words) ;
END CASE;

   
IF(p_rule_id = 1452) 
THEN
   engage_warp_drive('hamster')
END IF;

[started] Bug with Linebreaks After AND/OR (5 replies)

bbhulsey — Tue, 13 Mar 2012 21:34:36 +0100

Hello,

I have noticed that the "Linebreaks after AND/OR" option does not seem to work, at least not with the options I have selected. Linebreaks before AND/OR seems to work fine. I am using the desktop edition, input SQL is 'Any SQL', output SQL is VB String(2). Example from MS Access:

SELECT IIf([KitSource]=1,"BioTrack","BioKit") AS KitDB, tblKits.KitID, tblKits.DateSent, tblKits.DateReceived, tblKits.Expired, tblKits.SampleType, tblKits.Resample, tblKits.KitSource
FROM (tblKits LEFT JOIN tblEventBN ON tblKits.KitID = tblEventBN.KitID) LEFT JOIN tblEventBN_R ON tblKits.KitID = tblEventBN_R.KitID
WHERE (((tblKits.SubjectID)=100010027) AND ((tblEventBN.KitID) Is Null) AND ((tblEventBN_R.KitID) Is Null));

Formatted with Linebreaks after AND/OR (note that it has removed a space after each AND):
strSQL= "" & _
"SELECT IIf([KitSource] = 1, "BioTrack", "BioKit") AS KitDB, " & _
"tblKits.KitID, " & _
"tblKits.DateSent, " & _
"tblKits.DateReceived, " & _
"tblKits.Expired, " & _
"tblKits.SampleType, " & _
"tblKits.Resample, " & _
"tblKits.KitSource " & _
"FROM(tblKits " & _
"LEFT JOIN tblEventBN " & _
"ON tblKits.KitID = tblEventBN.KitID) " & _
"LEFT JOIN tblEventBN_R " & _
"ON tblKits.KitID = tblEventBN_R.KitID " & _
"WHERE(((tblKits.SubjectID) = 100010027) AND((tblEventBN.KitID) IS NULL) AND((tblEventBN_R.KitID) IS NULL)) ;"

Formatted with Linebreaks before AND/OR:
strSQL= "" & _
"SELECT IIf([KitSource] = 1, "BioTrack", "BioKit") AS KitDB, " & _
"tblKits.KitID, " & _
"tblKits.DateSent, " & _
"tblKits.DateReceived, " & _
"tblKits.Expired, " & _
"tblKits.SampleType, " & _
"tblKits.Resample, " & _
"tblKits.KitSource " & _
"FROM(tblKits " & _
"LEFT JOIN tblEventBN " & _
"ON tblKits.KitID = tblEventBN.KitID) " & _
"LEFT JOIN tblEventBN_R " & _
"ON tblKits.KitID = tblEventBN_R.KitID " & _
"WHERE(((tblKits.SubjectID) = 100010027) " & _
"AND((tblEventBN.KitID) IS NULL) " & _
"AND((tblEventBN_R.KitID) IS NULL)) ;"

[solved] keyword FLOOR (4 replies)

Jwk — Thu, 29 Mar 2012 18:07:28 +0200

The keyword FLOOR is not being upper cased in Desktop version 4.8.46

[open] Line Break after INSERT Key Word (2 replies)

RDWilson2 — Wed, 18 Jan 2012 20:50:22 +0100

I cannot find a way to prevent the Line Break after the Key Word INSERT. Am I over looking or misunderstanding some setting or is this just not possible (at this time?)?

The coding standard that I am used to and that is in place where I am working is:

INSERT INTO TableName

rather than:
I

INSERT
INTO TableName

[solved] Online version is borked (5 replies)

user — Fri, 24 Feb 2012 15:45:00 +0100

Online version appears to be broke again. Wont format anything. Tried multiple browsers, flush cached, emptied my cookies, changed the oil, topped off the headlight fluid....no go.

[solved] Can stored procedure and params be formatted like this? (2 replies)

Absinthe — Mon, 05 Mar 2012 21:16:02 +0100

CREATE PROCEDURE [dbo].[usp_CardMult_Create]
(
    @BallCallServerId int
  , @GameId           bigint
  , @MachineSerial    int
)

[closed] Convert Query from SQL 2008 to SQL92 (1 reply)

Steve Anderson — Fri, 02 Mar 2012 17:39:50 +0100

Can this be done in SQL92?

SELECT A.* from SharePointProgress.dbo.tblMscShpDt A
INNER JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY intid) AS 'RN', *
FROM SharePointProgress.dbo.tblMscShpDt) B ON A.intid= B.intid
AND B.RN between 20 and 30

STORED PROCEDURE: DECLARE HANDLER (1 reply)

dlscott — Mon, 06 Feb 2012 00:33:21 +0100

Hello,

Thanks so much for SQLInForm!

I am using GUI Release 4.8.37 with Engine 4.8.43.

Question: Is there a way to format the following:

DECLARE CONTINUE HANDLER FOR NOT FOUND
   SET VAR1 = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
   SET VAR2 = 999;

Thanks Again,
David

Qlikview formatting (1 reply)

JR — Mon, 06 Feb 2012 00:34:04 +0100

Hello. Great tool! I've been using this for years. With the increasing usage of Qlikview I was wondering if this will soon be incorporated into the syntax formatting options drop-down..specifically for load script formatting. Thanks!

An extra case option? (1 reply)

Geoff — Mon, 06 Feb 2012 00:32:12 +0100

One of the only things that seems to be missing in this (for me) is the ability to set the case of keywords, functions and identifiers separately. Assuming you already identify them via parsing, is it a big deal to add an extra couple of drop-downs to allow setting these three separately? I'm thinking 3 drop downs, each with "Leave as-is", "Upper case", "Lower case" (and possibly "Sentence case"?).
Is this something you've already considered, or would be prepared to add?
Cheers,
Geoff

[closed] Wanted B)- Beta Testers for NEW Release (3 replies)

guidomarcel — Thu, 09 Feb 2012 17:04:45 +0100

Hi,
the next SQLinForm release for Windows users will have a feature to define a Windows Hotkey. The Windows Hotkey will format the SQL and(!) paste the formatted SQL in your SQL editor or Development Environment.

This new feature will give you a feeling that SQLinForm is a plug-in in your daily DB development environement or your SQL editor.

If you already have a SQLinForm License Key and would like to test the Beta Version (targeted for February 2012) please just send me a mail.

Best Regards
Guido

[open] config file pathnames (1 reply)

winkey — Sun, 15 Jan 2012 20:28:07 +0100

On linux config files are not hidden files or are not in a hidden dir. they are stored in the current working dir. this will usually end up being the users home dir.

Normally applications create a hidden dir and store there config files in it or create hidden config files.

leaving them as normal files tend to make them more likely to be deleted.

Brian

[closed] OUTER APPLY (3 replies)

Martin — Sun, 15 Jan 2012 23:01:26 +0100

How do I get APPLY to be formatted in the same way as JOIN
(SQL Server)

SELECT x
FROM y
    INNER JOIN z
    ON z.a = x.a
    OUTER APPLY (
        SELECT TOP 1 u FROM v WHERE u.x = z.x
    )

[waiting for Info] Handling App-specific tokens (1 reply)

Jarvis Metheun — Sat, 08 Oct 2011 22:53:22 +0200

The application that I use has it's own 'intelligent' tokens used to generate more efficient SQL at execution time.

Example:

<%AND("START_DATE >= <%?(StartDate)%>")%>

I was wondering if there was an easy way of making this construct format like the AND clause it represents?

Thanks for an excellent tool. Happy customer for 3+ years.

[waiting for info] Keyword indentation/linebreaks (1 reply)

BetterChoices — Sat, 08 Oct 2011 22:49:13 +0200

Please can we have the option for linebreaks & indentation for BEGIN, BEGIN TRANSACTION, COMMIT, COMMIT TRANSACTION, BEGIN WORK, COMMIT WORK, IF, ELSE, ENDIF & MESSAGE?

Let me know if you need a copy of the SQL that looks *odd*

[closed] replace Host Variables Example (1 reply)

Orbitus — Fri, 23 Sep 2011 22:45:50 +0200

Could you give me an example of what (Replace Host Variables by "?") does because I cant seem to get it to work the way I think it should?

[started] Typo on Website (1 reply)

Josh — Mon, 16 Jan 2012 12:36:46 +0100

Hi,

When I attempt to save your webpage as a favorite, there is a mispelling of the word "Version"

SQLinForm - Free Online Vesion

Just thought you should know!

[closed] Desktop testing? (1 reply)

TD — Sat, 17 Sep 2011 08:36:37 +0200

I would gladly pay for a desktop license if I knew that it would work, but I've downloaded the desktop version and can't get it to do anything other than change the background color from white to gray. Is there not a way to test the desktop version with 100 lines or less or something?

[open] Linebreaks: Output SQL (1 reply)

jcrane — Sat, 08 Oct 2011 23:04:20 +0200

I would like to create SQL scripts that have 1 line per statement with no carriage returns between statements. Basically, it should resemble the option "Output SQL on 1 Line" option, but it should do that for each statement. Is this possible to do currently? I'm using paid version 4.8.34.

Input

INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", 
"RETAIN", "GROUPING") VALUES ('196420G', 'PAID TAXES, TX', '196420G', '196420G', 'Actuals', null, 190000, 1, 0, null) ; 
INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", 
"RETAIN", "GROUPING") VALUES ('196420N', 'PAID TAXES, ME', '196420N', '196420N', 'Actuals', null, 190000, 1, 0, null) ; 
INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", 
"RETAIN", "GROUPING") VALUES ('196420V', 'PAID TAXES, MS`', '196420V', '196420V', 'Actuals', null, 190000, 1, 0, null) ;

Output

INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420G', 'PAID TAXES, TX', '196420G', '196420G', 'Actuals', NULL, 190000, 1, 0, NULL) ;

INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420N', 'PAID TAXES, ME', '196420N', '196420N', 'Actuals', NULL, 190000, 1, 0, NULL) ;

INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420V', 'PAID TAXES, MS`', '196420V', '196420V', 'Actuals', NULL, 190000, 1, 0, NULL) ;

Expected Output

INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420G', 'PAID TAXES, TX', '196420G', '196420G', 'Actuals', NULL, 190000, 1, 0, NULL) ;
INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420N', 'PAID TAXES, ME', '196420N', '196420N', 'Actuals', NULL, 190000, 1, 0, NULL) ;
INSERT INTO "TABLE_X" ("ACCOUNT", "DESCRIPTION", "VALUE_A", "VALUE_B", "ELEMENT_TYPE", "FLAG", "EFF_DATE", "VALIDATION_STATUS_ID", "RETAIN", "GROUPING") VALUES ('196420V', 'PAID TAXES, MS`', '196420V', '196420V', 'Actuals', NULL, 190000, 1, 0, NULL) ;

[solved] Indent and linebreak for DECLARE (4 replies)

its1more — Mon, 16 Jan 2012 12:20:22 +0100

Hello all!

I have a very strange formatting in SQLinForm.

Here is what I "send in":

-- SYS_UNSPSC_Group
declare @CompanyID int, @GroupName varchar(250), @Description varchar(250)

-- SYS_UNSPSCGroup_GroupPropertiesData
declare @GroupPropertyID int, @GroupPropertyValue varchar(250);

-- In procedure only
declare @GroupID int

--For test only
set @CompanyID = 10297
set @GroupName = 'Fastighetsskötare - Göteborg'

Here is the result:

-- SYS_UNSPSC_Group
DECLARE
        @CompanyID   INT,
        @GroupName   VARCHAR(250),
        @Description VARCHAR(250)
        -- SYS_UNSPSCGroup_GroupPropertiesData
        DECLARE
                @GroupPropertyID    INT,
                @GroupPropertyValue VARCHAR(250)
                -- In procedure only
                DECLARE
                        @GroupID INT
                        --For test only
                        SET @CompanyID = 10297
                        SET @GroupName = 'Fastighetsskötare - Göteborg'

Here is about what I expect:
Please note: the declared variables data type will be aligned and that's working fine. However, I can't get it to show up correctly in this forum editor.

-- SYS_UNSPSC_Group
DECLARE @CompanyID INT,
	@GroupName VARCHAR(250),
	@Description VARCHAR(250)
        
-- SYS_UNSPSCGroup_GroupPropertiesData
DECLARE @GroupPropertyID INT,
	@GroupPropertyValue VARCHAR(250)

-- In procedure only
DECLARE @GroupID INT

--For test only
SET @CompanyID          = 10297
SET @GroupName          = 'Fastighetsskötare - Göteborg'

So, does anybody have a clue why the formatting is "wrong", or how to set SQLinForm to format as I want? Basically the DECLARE is indented to much, or?

Thanks in advance,
Fredrik

[solved] June 2011 edition: font issues on Mac OS X (11 replies)

Jan Beilicke — Fri, 27 Apr 2012 23:21:51 +0200

I got some font issues on 10.6.8 with JRE 5.0 or 6.0: The font is replaced by Comic Sans (or similar) and the three buttons "Format", "Edit" and "Clear" don't have a label. Please see attached screenshot (Jan. ed. compared to June ed.)

Any idea how to solve this issue?

Engine: 4.8.36
GUI: 4.8.34
JRE: 5.0 + 6.0

[closed] Not understanding the system tray option (2 replies)

bbrown127 — Fri, 30 Mar 2012 07:56:36 +0200

Hi,

First of all, I am loving SQLinForm, one try and I had to buy it. One thing I'm not sure I understand is the Java 6 version with "system tray." Is this intended to put some kind of service/system tray icon in my Window's system tray for ease of access? If so, how do I activate it. I currently have SQLinForm running (June 2011 Java 6 version) and can open it fine from a start menu shortcut I created, but if there is an option to activate it to the system tray, then I have not figured out how that works.

Please help me understand how I can activate the "system tray" feature or correct me in my understanding of what I should be expecting by "system tray". I do have JRE 1.6.0_24 installed and the active java runtime. I have also gone into the Java Control Panel and made sure the "Place Java Icon in system tray" checked in the advanced tab (if that matters).

Again, perhaps I am not fully understanding what the "system tray" feature indicates, but any information would be helpful...system tray or no, I love this app!

Thanks!

[closed] Keyword Alignment (1 reply)

SamBaugh — Thu, 11 Aug 2011 20:03:17 +0200

Seems like the formatter used to have an option to align keywords, is this still possible? I like having the FROM, WHERE, AND right aligned to the related SELECT. Thanks! This has been such a help when we have to capture and tune unformatted SQL, some of which get into the 1000's of lines.

[solved] Output string (4 replies)

Fri, 02 Mar 2012 17:32:19 +0100

When outputing as a SQLString the string is not formatted correctly.
e.g.
SELECT [RowNum] ,
[SomeID] ,
[SomeCode]
FROM [TestData]

becomes :
SELECT @SQLString = N'SELECT [RowNum] ,' +
N' [SomeID] ,' +
N' [SomeCode]' +
N'FROM [TestData]'

which is equivalent to :
SELECT [RowNum] , [SomeID] , [SomeCode]FROM [TestData]
which does not execute as there is not space between the last selected column and FROM.

One solution would be to add a space before each ending quote or after each beginning quote by default.

Hope this example makes sense.If there is an alternative please let me know.

thanks.

[closed] Online Version not formatting (13 replies)

Kellie — Thu, 28 Jul 2011 20:21:16 +0200

The online version is not formatting.

[waiting for info] Search-n-Replace (1 reply)

Raro42 — Thu, 28 Jul 2011 20:27:13 +0200

Hi, grabbing SQL_Statements from IBM-Cognos-Server-Logfile always includes " instead of " and ' instead of ' and > instead > ... and so on. There are about 10 characters, that I have to run through my favorite texteditor-macro before pasting it to SQLInform. Would be glad to store a replacement map in SQLinform to do the search-n-replace here instead of using editor macro.

what do you think?

regards,
RaRo.

[solved] Custom Output SQL (3 replies)

Eric — Fri, 02 Mar 2012 17:36:21 +0100

I did a quick search for "Output SQL" and didn't find anything.

Is it possible to design your own output sql? Specifically, I'm pasting into the syntax editor of SPSS. The current "Concatenated SQL" almost does the trick, but I have to replace the "||" with "+".

Thanks for your help,
-Eric