Lu, Huayi 27019c2991 DWS TOOL 830.201 version

Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>

2024-05-16 07:35:25 +00:00

31 KiB

Raw Permalink Blame History

TOP and SAMPLE

The TOP and SAMPLE clauses of Teradata are migrated to LIMIT in GaussDB(DWS).

TOP

The DSC also supports migration of TOP statements with dynamic parameters.

For TOP clauses containing WITH TIES, the ORDER BY clause is also required. Otherwise, the tool will not migrate the statement and copy it as it is.
When using TOP with dynamic parameters:
- The input dynamic parameters should be in the following form:
  1
  TOP :<parameter_name>
  The following characters are valid for dynamic parameters: a-z, A-Z, 0-9 and "_".

Input: SELECT .. TOP

1
2
3
4
SELECT TOP 1 c1, COUNT (*) cnt 
  FROM tab1 
 GROUP BY c1 
 ORDER BY cnt;	

Output

1
2
3
4
5
SELECT c1, COUNT( * ) cnt
  FROM tab1
 GROUP BY c1
 ORDER BY cnt 
 LIMIT 1;

Input: SELECT .. TOP PERCENT

1
2
3
4
SELECT TOP 10 PERCENT c1, c2 
  FROM employee
 WHERE ...
 ORDER BY c2 DESC;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
WITH top_percent AS (
      SELECT c1, c2
        FROM employee
       WHERE ...
       ORDER BY c2 DESC
                    ) 
SELECT *
  FROM top_percent 
 LIMIT (SELECT CEIL(COUNT( * ) * 10 / 100)
          FROM top_percent);

Input: SELECT .. TOP with dynamic parameters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT
           TOP :Limit WITH TIES c1
          ,SUM (c2) sc2
     FROM
          tab1
     WHERE
          c3 > 10
     GROUP BY
          c1
     ORDER BY
          c1
;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH top_ties AS (
     SELECT
                c1
               ,SUM (c2) sc2
               ,rank (
               ) OVER( ORDER BY c1 ) AS TOP_RNK
          FROM
               tab1
          WHERE
               c3 > 10
          GROUP BY
               c1
) SELECT
          c1
          ,sc2
     FROM
          top_ties
     WHERE
          TOP_RNK <= :Limit
     ORDER BY
          TOP_RNK
;

Input: SELECT .. TOP with dynamic parameters and with TIES

1
2
3
4
5
6
7
 SELECT
           TOP :Limit WITH TIES Customer_ID
   FROM
          Customer_t
   ORDER BY
          Customer_ID
;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
WITH top_ties AS (
     SELECT
               Customer_ID
               ,rank (
               ) OVER( order by Customer_id) AS TOP_RNK
          FROM
               Customer_t
) SELECT
          Customer_ID
     FROM
          top_ties
     WHERE
          TOP_RNK <= :Limit
     ORDER BY
          TOP_RNK
;

Input: SELECT .. TOP PERCENT with dynamic parameters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
          TOP :Input_Limit PERCENT WITH TIES c1
          ,SUM (c2) sc2
     FROM
          tab1
     GROUP BY
          c1
     ORDER BY
          c1
;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH top_percent_ties AS (
     SELECT
               c1
               ,SUM (c2) sc2
               ,rank (
               ) OVER( ORDER BY c1 ) AS TOP_RNK
          FROM
               tab1
          GROUP BY
               c1
) SELECT
          c1
          ,sc2
     FROM
          top_percent_ties
     WHERE
          TOP_RNK <= (
               SELECT
                         CEIL(COUNT( * ) * :Input_Limit / 100)
                    FROM
                         top_percent_ties
          )
     ORDER BY
          TOP_RNK
;

SAMPLE

The tool only supports single positive integers in the SAMPLE clause.

Input: SELECT .. SAMPLE

1
2
3
4
SELECT c1, c2, c3 
  FROM tab1 
 WHERE c1 > 1000
SAMPLE 1;

Output

1
2
3
4
SELECT c1, c2, c3
  FROM tab1
 WHERE c1 > 1000
 LIMIT 1;

Parent topic: SELECT

31 KiB Raw Permalink Blame History

TOP and SAMPLE

TOP

SAMPLE

31 KiB

Raw Permalink Blame History