Query<\/strong><\/p>\n\n\n\nWITH -- starting data\n W1 (TRANS_ID, DATE_MOV) AS (\n VALUES ('T0011', '2024-01-10'), ('T0012', '2024-01-10'), ('T0013', '2024-01-10')\n , ('T0014', '2024-01-10'), ('T0015', '2024-01-10'), ('T0016', '2024-01-10')\n , ('T0017', '2024-01-10'), ('T0018', '2024-01-10'), ('T0019', '2024-01-10')\n , ('T0020', '2024-01-10'), ('T0023', '2024-01-10'), ('T0024', '2024-01-10')\n , ('T0025', '2024-01-10'), ('T0026', '2024-01-10'), ('T0127', '2024-01-10')\n , ('T0128', '2024-01-10'), ('T0129', '2024-01-10'), ('T0149', '2024-01-10')\n , ('T0150', '2024-01-11'), ('T0151', '2024-01-11'), ('T0152', '2024-01-11')\n , ('T0165', '2024-01-11'), ('T0166', '2024-01-11'), ('T0167', '2024-01-11')\n , ('T0168', '2024-01-11'), ('T0201', '2024-01-11'))\n -- assigns progressive numbering to the same DATA_MOV, ordering by TRANS_ID\n , W2 (TRANS_ID, ROW_NUM, DATE_MOV)\n as (select trim(W1.TRANS_ID)\n , row_number() over ( partition by DATE_MOV -- 'COMMON_VALUE'\n order by W1.TRANS_ID) ROW_NUM\n , W1.DATE_MOV\n from W1 order by W1.TRANS_ID)\nselect * from W2;\n<\/pre>\n\n\n\nResult<\/strong><\/p>\n\n\n\n\n
\n
Original table (W1)<\/p>\n<\/div>\n\n\n\n
\n
Result table (W2)<\/p>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
TRANS_ID DATE_MOV\nT0011 2024-01-10\nT0012 2024-01-10\nT0013 2024-01-10\nT0014 2024-01-10\nT0015 2024-01-10\nT0016 2024-01-10\nT0017 2024-01-10\nT0018 2024-01-10\nT0019 2024-01-10\nT0020 2024-01-10\nT0023 2024-01-10\nT0024 2024-01-10\nT0025 2024-01-10\nT0026 2024-01-10\nT0127 2024-01-10\nT0128 2024-01-10\nT0129 2024-01-10\nT0149 2024-01-10\nT0150 2024-01-11\nT0151 2024-01-11\nT0152 2024-01-11\nT0165 2024-01-11\nT0166 2024-01-11\nT0167 2024-01-11\nT0168 2024-01-11\nT0201 2024-01-11<\/pre>\n<\/div>\n\n\n\n\n
TRANS_ID ROW_NUM DATE_MOV\nT0011 1 2024-01-10\nT0012 2 2024-01-10\nT0013 3 2024-01-10\nT0014 4 2024-01-10\nT0015 5 2024-01-10\nT0016 6 2024-01-10\nT0017 7 2024-01-10\nT0018 8 2024-01-10\nT0019 9 2024-01-10\nT0020 10 2024-01-10\nT0023 11 2024-01-10\nT0024 12 2024-01-10\nT0025 13 2024-01-10\nT0026 14 2024-01-10\nT0127 15 2024-01-10\nT0128 16 2024-01-10\nT0129 17 2024-01-10\nT0149 18 2024-01-10\nT0150 1 2024-01-11\nT0151 2 2024-01-11\nT0152 3 2024-01-11\nT0165 4 2024-01-11\nT0166 5 2024-01-11\nT0167 6 2024-01-11\nT0168 7 2024-01-11\nT0201 8 2024-01-11\n<\/pre>\n<\/div>\n<\/div>\n\n\n\nDetailed description of the query<\/strong><\/p>\n\n\n\nThe SQL query uses Common Table Expressions to create a sample dataset and then assign a sequential number to each row within groups based on the date of movement (DATE_MOV<\/strong>), sorting by the transaction ID (TRANS_ID<\/strong>).<\/p>\n\n\n\nThe query is divided into two main parts:<\/p>\n\n\n\n
W1: Definition of Starting Data<\/strong><\/p>\n\n\n\nThe first CTE, W1<\/strong>, defines a starting dataset with two columns: TRANS_ID<\/strong> and DATE_MOV<\/strong>. Values \u200b\u200bare manually entered to simulate real data.<\/p>\n\n\n\nW2: Assignment of the Progressive Number<\/strong><\/p>\n\n\n\nThe second CTE, W2<\/strong>, is based on the data defined in W1. Use the ROW_NUMBER<\/strong>() function to assign a sequential number to each transaction (TRANS_ID<\/strong>) within each date (DATE_MOV<\/strong>). The ROW_NUMBER<\/strong>() function is partitioned by DATE_MOV<\/strong> to ensure that the numbering starts again at 1 for each new date. Additionally, the order is established by the TRANS_ID<\/strong> to determine the order of number assignment within each date group<\/p>\n\n\n\nLet’s analyze this part in more detail:<\/p>\n\n\n\n
ROW_NUMBER() OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID) AS ROW_NUM,<\/pre>\n\n\n\nThis expression is an example of a window function in SQL, specifically ROW_NUMBER<\/strong>(), that is used to assign a unique sequential number to each row within a query result set. Let’s examine each component of this expression:<\/p>\n\n\n\n\n- ROW_ ROW_NUMBER()<\/strong> is a window function that assigns a sequential number to each row in a result set. The number starts at 1 for the first row and increases by 1 for each subsequent row<\/li>\n<\/ul>\n\n\n\n
\n- OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID)<\/strong> The OVER<\/strong> clause specifies how the ROW_NUMBER<\/strong>() window function should be applied to the result set.<\/li>\n<\/ul>\n\n\n\n
\n- PARTITION BY DATE_MOV<\/strong>: This divides the result set into partitions (or groups) based on the value of the DATE_MOV<\/strong> column. The ROW_NUMBER<\/strong>() function is then applied within each of these partitions separately. This means that the counting starts again from 1 for each new date (DATE_MOV<\/strong>). In other words, each date will have its own set of sequential numbers.<\/li>\n<\/ul>\n\n\n\n
\n- ORDER BY W1.TRANS_ID<\/strong>: Within each partition (created based on DATE_MOV<\/strong>), the rows are sorted based on the value of TRANS_ID<\/strong>. The ROW_NUMBER<\/strong>() function assigns sequential numbers to these rows based on this order. So, for each date, the first transaction in the order will have the number 1, the second will have the number 2, and so on.<\/li>\n<\/ul>\n\n\n\n
\n- AS ROW_NUM<\/strong>: Finally, AS ROW_NUM<\/strong> assigns a name to the column that contains the progressive number generated by the ROW_NUMBER()<\/strong> function. In this case, the column will be named ROW_NUM<\/strong>.<\/li>\n<\/ul>\n\n\n\n
Please note that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database. It is always good practice to refer to the official IBM documentation for more precise and detailed information<\/p>\n\n\n\n
\n\n\n\n<\/p>\n\n\n\n
CTE Common Table Expression to detect breaks in the number sequence<\/strong><\/p>\n\n\n\nDescription Context and Objective<\/strong><\/p>\n\n\n\nThe query in question is designed to operate on a table that records transactions, identified by a Movement Code (MovCode<\/strong>) and associated with a specific identifier (NumberID<\/strong>).<\/p>\n\n\n\nWhile the original table may contain other relevant information, for the purpose of this query, the focus is solely on MovCode<\/strong> and NumberID<\/strong>.<\/p>\n\n\n\nThe main objective is to generate a list in which to identify and mark any breaks in the numerical sequence of identification numbers (NumberID<\/strong>) for each Movement Code (MovCode<\/strong>).<\/p>\n\n\n\nQuery<\/strong><\/p>\n\n\n\nWITH xx (MovCode, NumberID)\n AS (VALUES ('A1002','101')\n ,('A1002','102')\n ,('A1002','104')\n ,('A1002','105')\n ,('A1002','108')\n ,('A1002','109')\n ,('A1002','110')\n ,('B2002','201')\n ,('B2002','202')\n ,('B2002','203')\n ,('B2002','204')\n ,('B2002','210')\n ,('B2002','211')\n ,('B2002','214'))\n , CTE_CheckRegister (MovCode, NumberID, NextNumberID)\n AS (\n SELECT MovCode,\n NumberID,\n Lead(NumberID, 1) OVER (PARTITION BY MovCode ORDER BY NumberID) NextNumberID\n FROM xx\n )\nSELECT MovCode,\n CASE\n WHEN NextNumberID is NULL THEN NumberID\n WHEN (NumberID = NextNumberID - 1) THEN NumberID\n ELSE NumberID concat ' ***'\n END as NumberID\nFROM CTE_CheckRegister\nORDER BY MovCode, NumberID;<\/pre>\n\n\n\nResult<\/strong><\/p>\n\n\n\n\n
\n
Original table ( XX )<\/p>\n<\/div>\n\n\n\n
\n
Result table (CTE_CheckRegister)<\/p>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
MOVCODE\tNUMBERID\nA1002\t101\nA1002\t102\nA1002\t104\nA1002\t105\nA1002\t108\nA1002\t109\nA1002\t110\nB2002\t201\nB2002\t202\nB2002\t203\nB2002\t204\nB2002\t210\nB2002\t211\nB2002\t214<\/pre>\n<\/div>\n\n\n\n\n
MOVCODE\tNUMBERID\nA1002\t101\nA1002\t102 ***\nA1002\t104\nA1002\t105 ***\nA1002\t108\nA1002\t109\nA1002\t110\nB2002\t201\nB2002\t202\nB2002\t203\nB2002\t204 ***\nB2002\t210\nB2002\t211 ***\nB2002\t214<\/pre>\n<\/div>\n<\/div>\n\n\n\nDetailed description of the query<\/p>\n\n\n\n
This SQL query uses CTE (Common Table Expressions) and the LEAD<\/strong> function to identify and mark any breaks in the numerical sequence of identification numbers (NumberID) for each Movement Code (MovCode).<\/p>\n\n\n\nHere is a detailed explanation of each part of the query:<\/p>\n\n\n\n
\n- CTE Initial xx<\/strong>: This CTE defines a set of example values \u200b\u200brepresenting pairs of MovCode<\/strong> and NumberID<\/strong>. It is a temporary table used to demonstrate query logic.<\/li>\n<\/ul>\n\n\n\n
\n- CTE CTE_CheckRegister<\/strong>:\n
\n- Use data from the CTE<\/strong> xx<\/strong>.<\/li>\n\n\n\n
- Apply the LEAD<\/strong> function to get the next NumberID<\/strong> (NextNumberID<\/strong>) for each MovCode<\/strong>, sorting by NumberID<\/strong>.<\/li>\n\n\n\n
- The LEAD<\/strong> function is a window function that provides access to a line a certain physical distance from the current line, in this case, the next line.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
\n- Final Selection and CASE Logic<\/strong>:\n
\n- The final query selects MovCode<\/strong> and a modified version of NumberID<\/strong>.<\/li>\n\n\n\n
- CASE logic is used to determine what to show for NumberID<\/strong>:\n
\n- If NextNumberID<\/strong> is NULL<\/strong>, this indicates that it is the latest identification number for that MovCode, so it simply shows NumberID<\/strong>.<\/li>\n\n\n\n
- If NumberID<\/strong> is consecutive to NextNumberID<\/strong> (NumberID = NextNumberID – 1<\/strong>), show NumberID<\/strong>.<\/li>\n\n\n\n
- Otherwise, if there is a break in the sequence (i.e., the NumberID is not followed by the consecutive number), appends ‘***’ to NumberID<\/strong> to signal the break.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
\n- Sorting of Results<\/strong>:\n
\n- Results are sorted by MovCode<\/strong> and NumberID<\/strong>, ensuring that output is presented neatly and sequentially for each MovCode.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
Please note that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database.<\/p>\n\n\n\n
It is always good practice to refer to the official IBM documentation for more precise and detailed information<\/p>\n","protected":false},"excerpt":{"rendered":"
Common Table Expressions (CTE) in SQL on IBM iSeries (AS\/400), as in other database management systems, are a powerful and flexible feature that allows you to create temporary, reusable queries within a larger SQL statement wide. They are particularly useful…<\/p>\n","protected":false},"author":3,"featured_media":4181,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[141,143],"tags":[46,160,248,249,250],"class_list":["post-4274","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-manage-as400-with-sql","category-more-in-depth","tag-sql","tag-cte-common-table-expression-en","tag-lead-over-en","tag-row_number-over-en","tag-window-function-en"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4274","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/comments?post=4274"}],"version-history":[{"count":11,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4274\/revisions"}],"predecessor-version":[{"id":4397,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4274\/revisions\/4397"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media\/4181"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media?parent=4274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/categories?post=4274"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/tags?post=4274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}