Logo SQLinForm

 

Few Questions

Few Questions
January 28, 2016 04:55PM
Hello,
I am thinking about to buy your tool, it looks very useful. So I am testing your desktop version of your free SQL-Formater tool. On the first view there it looks pretty nice and easy to use and to configure. But it seems there are some restrictions/missing features which I need for my daily business. Maybe you can clarify how I can fix these problems and show me how to configure your tool to get my desired output.

Thanks for your Help!, Mike


===== 1: Recursions are not recognized

Input:
------
REPLACE Recursive VIEW T0202_Temp
    (Starting_Product_Group_Id
     ,Product_Group_Id
     ,Parent_Prod_Group_Id
     ,NSeq
    )



Output:
-------
REPLACE RECURSIVE VIEW t0202_temp( starting_product_group_id, product_group_id, parent_prod_group_id, nseq ) AS


Desired Result:
---------------
REPLACE Recursive VIEW T0202_Temp
    (
       Starting_Product_Group_Id
     , Product_Group_Id
     , Parent_Prod_Group_Id
     , NSeq
    )




===== 2: Comments shouldn’t be used for allignement

Input:
------
SELECT A as AColumn, B as BColumn FROM TableABCDEFGH
WHERE B > 10 AND CColumn like '%hello%'
/* this is a very long long long long long comment and 5 > 10 */
;


Output:
-------
SELECT 
  A AS AColumn 
, B AS BColumn 
FROM 
  TableABCDEFGH
WHERE 
  B                                                        > 10 
  AND CColumn                                           LIKE '%hello%'
  /* this is a very long long long long long comment and 5 > 10 */
  ;



Desired Result:
---------------
SELECT 
  A AS AColumn 
, B AS BColumn 
FROM 
  TableABCDEFGH
WHERE 
  B                     > 10 
  AND CColumn           LIKE '%hello%'
  /* this is my comment and 5 > 10 */
;



===== 3. Option to put the last ";" on a new line independant for queries and COMMENT ON
Input:
------
SELECT * FROM A;


Output:
-------
SELECT * FROM A;


Desired Result:
---------------
SELECT * FROM A
;




===== 4. Windowing PARTITION BY • ORDER BY • RESET WHEN • ROWS

Output:
-------
everything is on one line

Desired Result:
---------------
split it into more than one line


===== 5. QUALIFY COUNT(*) OVER (PARTITION BY ... ORDER BY ...) = 1

Output:
-------
everything is on one line

Desired Result:
---------------
split it into more than one line


===== 6. More than one line for expressions

Input:
------
select 
(a.x +b.x )*1+ (a.y +b.y )*2 +(a.z +b.z )/3 as Result
from a,b
where a.id =b.id
;


Output:
-------
SELECT
  (a.x + b.x ) * 1 + (a.y + b.y ) * 2 + (a.z + b.z ) / 3 AS RESULT
FROM 
  a 
, b
WHERE 
  a.id = b.id ;


Desired Result:
---------------
SELECT
    (a.x + b.x ) * 1 
  + (a.y + b.y ) * 2 
  + (a.z + b.z ) / 3 AS RESULT
FROM 
  a 
, b
WHERE 
  a.id = b.id
;



===== 7. Comments only on a new line under certrain conditions

Only put a comment on a new line, if the maximum line lenght is exceded



===== 8. Recursive Queries - the first SELECT has a bad position
Input:
------
WITH RECURSIVE cte(DND_NO, REC1, LATEST_DND) AS
 (
   SELECT DND_NO, REC1, DND_NO AS LATEST_DND
   FROM tab
   WHERE REC1 = 0
    
   UNION ALL
    
   SELECT t2.DND_NO, t2.REC1, cte.LATEST_DND
   FROM cte JOIN tab AS t2
   ON t2.REC1 = cte.DND_NO 
 )
SELECT * FROM cte;




Output:
-------
WITH RECURSIVE cte(DND_NO, REC1, LATEST_DND) AS
( SELECT DND_NO, REC1, DND_NO AS LATEST_DND FROM tab WHERE REC1 = 0
UNION ALL
SELECT 
  t2.DND_NO 
, t2.REC1 
, cte.LATEST_DND
FROM 
  cte 
JOIN 
  tab AS t2
ON t2.REC1 = cte.DND_NO
)
SELECT * FROM cte;





===== 9. "Edit SQL"-Tab
What is its purpose?
- Only For Administrators
- ...

These functions are not self-explaining for me.


===== 10. Comment on COLUMN/TABLE



COMMENT ON TABLE TT.TableA AS 'Table A is a really useful table'
;

COMMENT ON TABLE TT.TableA.ColumnA AS 'Columne A is a really useful table'
;



Recognize the COMMENT-Keyword and align all comments.

COMMENT ON TABLE TT.TableA 		AS 'Table A is a really useful table';
COMMENT ON TABLE TT.TableA.ColumnA 	AS 'Columne A is a really useful table';



===== 11. Create Table Statement

Input:
------
CREATE TABLE tablename
(columnABCDEFG INTEGER,
 column2AS FLOAT,
 column3ASD VARCHAR(3),
 column3ASDASASas VARCHAR(50),
 columnN BIGINT
)


Output:
-------
CREATE TABLE tablename
  ( 
    columnABCDEFG INTEGER 
  , column2AS FLOAT 
  , column3ASD       VARCHAR(3) 
  , column3ASDASASas VARCHAR(50) 
  , columnN          BIGINT
  )


Desired Output:
---------------
CREATE TABLE tablename
  ( 
    columnABCDEFG    INTEGER 
  , column2AS 	     FLOAT 
  , column3ASD       VARCHAR(3) 
  , column3ASDASASas VARCHAR(50) 
  , columnN          BIGINT
  )





10:========== MERGE INTO is returned on 2 lines


Input:
------
MERGE INTO ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS
USING  (
SELECT  VEND_PACK_OPR_ID,  LOCN_OPR_NBR,   DAY_NBR,  MDS


Output:
------
MERGE 
INTO ARP_TBLS_PRD.WRK_FACT_SHC_DLY_OPR_INS
USING ( SELECT VEND_PACK_OPR_ID, LOCN_OPR_NBR, DAY_NBR, MDS
Re: Few Questions
January 28, 2016 05:09PM
Hi Mike,
thanks for your valuable suggestions. I will have a look to them today. Are you working with Teradata?
Regards
Guido
Re: Few Questions
January 28, 2016 05:12PM
Hi Guido. Yes I work with Teradata.
Re: Few Questions
January 28, 2016 10:26PM
Hi Mike,
just published release 5.1.05

Point1: Resolved
Point 2: Resolved
Point 3: this new option is on my todo-list
Point 4+5: Could you post 1 or 2 examples please?
Point 6: This one is not easy. I have to think about a general parsing rule
Point 7: Could you please post 1 example please
Point 8: Please reduce the amount of characters for "Small SQLs" which do not get linebreaks. This option is found in the "Output SQL pane" and is called "No breaks for SQL up to xxx chars"
Point 9: This option is only for me for debugging purpose
Point 10: Will add the COMMENT Keyword. But if the ";" will get a linebreak then there will be no alignment of the AS because alignment only happens when the keywords to be aligned are present on each line
Point 11: Resolved. Added "FLOAT" to the list of keywords to be aligned
Point 12: Will check this one later.

Regards
Guido
Mike Carter
Re: Few Questions
January 29, 2016 10:12AM
Thank you for your very fast reply and your fixes! Here some ideas how to format queries of the kind 4 and 5.

===== 4. Windowing PARTITION BY • ORDER BY • RESET WHEN • ROWS
===== 5. QUALIFY COUNT(*) OVER (PARTITION BY ... ORDER BY ...) = 1

A: some people like it on one line, but the "OVER(PARTITION" might become larger and larger, maybe doing a linebreak if the overall lenght is >n?
--
SELECT 
  emp_name 
, dept_name 
, salary_amt 
, FIRST_VALUE(salary_amt) OVER(PARTITION BY dept_name ORDER BY salary_amt DESC) AS top_salary 
, LAST_VALUE(salary_amt) OVER(PARTITION BY dept_name ORDER BY salary_amt DESC)  AS bottom_salary
FROM 
  Personnel_Assignments
;


B: format it as a list, if there is more than one column
--
SELECT 
  emp_name 
, dept_name 
, salary_amt 
, FIRST_VALUE(salary_amt) OVER (
    PARTITION BY dept_name 	       /*<-- only 1 line because only one column is partitioned*/
    ORDER BY salary_amt DESC           /*<-- only 1 line because it is ordered only over one column*/
    ) AS top_salary 
, LAST_VALUE(salary_amt) OVER (
    PARTITION BY 
          dept_name 
	, aColumn
	, bColumn
    ORDER BY 
         salary_amt DESC
       , cColumn    ASC
       , dColumn    DESC
       , fColumn
    ) AS bottom_salary
FROM Personnel_Assignments
;


C: Example with "RESET WHEN" and "ROWS UNBOUNDED PRECEDING" and "ROWS BETWEEN N PRECEDING AND 1 "
--
SUM(sales) OVER (
    PARTITION BY region
    ORDER BY day_of_calendar
    RESET WHEN sales < SUM(sales) OVER (
         PARTITION BY region
         ORDER BY day_of_calendar
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
    ROWS UNBOUNDED PRECEDING
)

==> Other Keywords are CURRENT ROW, UNBOUNDED FOLLOWING/PRECEDING, [N] FOLLOWING, [N] PRECDEDING


D: Some people put the keyword "over" on an extra line
--
Select 
  MIN(mytransactionID ) 
    OVER (
      PARTITION BY String1, String2  
      ORDER BY Timestamp
      RESET WHEN Flag1 = 1 ROWS UNBOUNDED PRECEDING
      ) As derived_column
 , TransactionID 
 , String1
 , String2
 , Flag1
 , Timestamp
FROM DataTable;


F: Qualify Clause (is similar to HAVING and might have similar format options like A to D)
--
QUALIFY ROW_NUMBER() OVER(PARTITION BY TransID, TransLine ORDER BY ABS(Date1-Date2)) = 1

QUALIFY ROW_NUMBER() OVER(
    PARTITION BY 
      TransID
      , TransLine 
    ORDER BY ABS(Date1-Date2)) = 1

===== 7. Comments only on a new line under certrain conditions
Only put a comment on a new line, if the maximum line lenght is exceded
SELECT 
	  columnA                  AS myColA  /* this is a short comment */
	, columnB                  AS myColB  /* this is a short comment */
     /* this is a very very long long comment for columnC and exceeds the total line lenght of 100 characters, why I put the comment before/after the column*/
	, columnC * columnZ * 3    AS myColC  
	, columnD                  AS myColD
FROM myTable
;
Mike Carter
Re: Few Questions
January 29, 2016 02:09PM
sry it's me again... I found some few more issues

==== 13: WITH
WITH TEMP_TAB(a,b,c)
AS
(Select
a,b,c
from mytable)
,TEMP_TAB2(a,x,y)
AS
(Select
a,x,y
from mytable)
Select derived_columns from TEMP_TAB inner join TEMP_TAB2

WITH temp_tab 
  ( 
    a 
  , b 
  , c 
  )
  AS
  (
    SELECT a, b, c FROM mytable 
  )
, temp_tab2 
  ( 
    a 
  , x 
  , y 
  )
  AS
  (
    SELECT a, x, y FROM mytable 
  )
SELECT derived_columns 
FROM temp_tab 
INNER JOIN temp_tab2
- there is no linebreak before the SELECT


==== 14: WITH RECURSIVE
WITH RECURSIVE All_Trips
(Origin,
Destination,
Cost,
Depth) AS
( SELECT Origin, Destination, Cost, 0
FROM Flights
WHERE origin = 'LAX'
UNION ALL
SELECT All_Trips.Origin,
       Flights.Destination,
       All_Trips.Cost + Flights.Cost,
       All_Trips.Depth + 1
FROM All_Trips INNER JOIN Flights
ON All_Trips.Destination = Flights.Origin
AND All_Trips.Origin = 'LAX'
WHERE Depth < 2 )
SELECT * FROM All_Trips ORDER BY Depth;
- I can't change the position for the first SELECT (no linebreak before the select)
- no List for WITH RECURSIVE
- the first query is too short and on one line, I don't like it


==== 15:REPLACE/CREATE RECURSIVE VIEW
CREATE RECURSIVE VIEW JDAVENDNUM (REAL_NM, SUB_NM, CON_NM, ROW_NUM) AS
( SELECT
  a.REAL_NM
, a.SUB_NM
, CAST(TRIM(a.CON_NM) AS VARCHAR(2000))
, a.ROW_NUM
FROM
  New_veiw a
WHERE 
  a.ROW_NUM = 1
UNION ALL
SELECT
  a.REAL_NM
, a.SUB_NM
, b.CON_NM 
    || ',' 
    || TRIM(a.CON_NM) CON_NM
, a.ROW_NUM
FROM
  New_veiw   a
, JDAVENDNUM b
WHERE 
  a.REAL_NM     = b.REAL_NM
  AND a.SUB_NM  = b.SUB_NM
  AND a.ROW_NUM = b.ROW_NUM + 1
);
- I can't change the position for the first SELECT (no linebreak before the select)
- no List for REPLACE/CREATE RECURSIVE VIEW


==== 16: UPPERCASE/LOWERCASE doesn't work proper.
(I would prefer 2 options, one for Keywords and one for Fieldnames/Tablenames)

With the default settings nothing is changed.
SELect
/* UPPERCASE TEST */
 UPPERCASECOL1 as c1
, lowercasecol2 as c2
, mixedCOLUMN as c3
FRom mixedTABLE tmixed;


After setting the option Keyword Uppercase it changes to:
SELECT
        /* UPPERCASE TEST */
        UPPERCASECOL1 AS c1 , 
        lowercasecol2 AS c2 , 
        mixedCOLUMN   AS c3
FROM 
        mixedTABLE tmixed ;
it is ok.

After setting the option "Only Keywords Uppercase" it changes to:
SELECT
        /* UPPERCASE TEST */
        uppercasecol1 AS c1 , 
        lowercasecol2 AS c2 , 
        mixedcolumn   AS c3
FROM 
        mixedtable tmixed;
Fieldnames and Tablenames are changed to lowercase.



Going back to the setting the option "Keyword Uppercase" it changes to:
SELECT
        /* UPPERCASE TEST */
        uppercasecol1 AS c1 , 
        lowercasecol2 AS c2 , 
        mixedcolumn   AS c3
FROM 
        mixedtable tmixed;


Fieldnames and Tablenames are still in lowercase.

After setting the option "Only Keywords" unchanged:

SELECT
        /* UPPERCASE TEST */
        uppercasecol1 AS c1 , 
        lowercasecol2 AS c2 , 
        mixedcolumn   AS c3
FROM 
        mixedtable tmixed;


Fieldnames and Tablenames are still in lowercase.

Txs for your support.
Mike
Re: Few Questions
January 29, 2016 09:35PM
Hi Mike,
for 13+14+15 for the position of the SELECT: Please try the following
* Goto SELECT | UPDATE | DELETE | INSERT
* Goto Sub-Select
* Check Linebreak after "("
* Check Indent Sub-Select in Brackets

and make sure that you set the value for "No breaks for SQL up to xxx chars" to e.g. 1.
Re: Few Questions
January 29, 2016 09:56PM
Re: Few Questions
January 30, 2016 10:31PM
For 7:
For comments there is the option: "Linebreaks Before / After Block Comments". Please switch it off and it should work as expected.
Regards
Guido
Re: Few Questions
January 31, 2016 01:00PM
Point3: Option to put the last ";" on a new line for queries
--> Available in new release 5.1.07 in the "Keyword" option pane

Regards
Guido
Mike Carter
Re: Few Questions
February 02, 2016 01:44PM
Thank you for the new version of your Formater and your solution for my problems with 13+14+15. Is there a similar solution too keep "MERGE INTO" and "WHEN NOT MATCHED THEN" on one line?

Are you going to implement the "Partition/Qualify" options?

Can you add an option for white spaces for ||(string concatenation) it is not recognized as a keyword.

SELECT

	a||b || ' once upon a time '
	, c
, d
, f
from myTable;
SELECT
  a 
    ||b     -- here is no whitespace what I need is *|| b*
    || ' once upon a time '
, c
, d
, f
FROM 
  myTable 
;

Regards
Mike
Author:

Your Email (Optional):


Subject:


Spam prevention:
Please, solve the mathematical question and enter the answer in the input field below. This is for blocking bots that try to post this form automatically.
Question: how much is 14 plus 11?
Message: