January 23, 2017 at 1:12 pm
I'm trying to find index name based on table_name, column_names, column_position. Tables MN_TST_INDEXES and MN_TST_IND_COLUMNS are used to store existing index details.
When trying with below query, I'm unable to get the analytic function FIRST_VALUE work as expected when partitioned by index_name. Please try below code and you'll notice values in FIRST_VAL field is different from the expected output.
Can someone please point me what I'm missing here?
CREATE TABLE MN_TST_INDEXES(
INDEX_NAME varchar(30) NOT NULL,
TABLE_NAME varchar(30) NOT NULL,
UNIQUENESS varchar(6) NULL,
ADD_DROP varchar(4) NOT NULL
)
CREATE TABLE MN_TST_IND_COLUMNS(
INDEX_NAME varchar(30) NOT NULL,
TABLE_NAME varchar(30) NOT NULL,
COLUMN_NAME varchar(30) NOT NULL,
COLUMN_POSITION int NOT NULL,
ADD_DROP varchar(4) NOT NULL
)
CREATE TABLE drp_idx_test (col1 INT, col2 INT, col3 INT, col4 INT);
CREATE INDEX col1_idx ON drp_idx_test(col1);
CREATE INDEX col2_idx ON drp_idx_test(col2);
CREATE INDEX col1_col2_idx ON drp_idx_test(col1, col2);
CREATE INDEX col2_col1_idx ON drp_idx_test(col2, col1);
CREATE INDEX col1_col2_col3_idx ON drp_idx_test(col1, col2, col3);
CREATE INDEX col1_col3_col2_idx ON drp_idx_test(col1, col3, col2);
CREATE INDEX col1_col4_col3_idx ON drp_idx_test(col1, col4, col3);
INSERT INTO MN_TST_INDEXES (index_name, table_name, uniqueness, add_drop) VALUES ('IDX5', 'DRP_IDX_TEST', NULL, 'DROP');
INSERT INTO MN_TST_IND_COLUMNS (index_name, table_name, column_name, column_position, add_drop) VALUES ('IDX5', 'DRP_IDX_TEST', 'COL1', 1, 'DROP');
INSERT INTO MN_TST_IND_COLUMNS (index_name, table_name, column_name, column_position, add_drop) VALUES ('IDX5', 'DRP_IDX_TEST', 'COL3', 2, 'DROP');
INSERT INTO MN_TST_IND_COLUMNS (index_name, table_name, column_name, column_position, add_drop) VALUES ('IDX5', 'DRP_IDX_TEST', 'COL2', 3, 'DROP');
;WITH BASE_IDX ( COLUMN_NAME, COLUMN_POSITION, MAX_POSITION) AS
(SELECT AIC.COLUMN_NAME, AIC.COLUMN_POSITION, MAX(AIC.COLUMN_POSITION) OVER (PARTITION BY AIC.INDEX_NAME) MAX_POSITION
FROM MN_TST_IND_COLUMNS AIC
WHERE AIC.INDEX_NAME = 'IDX5'
AND AIC.ADD_DROP = 'DROP'
), USER_IDX ( INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, MAX_POSITION) AS
(SELECT IND.NAME INDEX_NAME, COL.NAME COLUMN_NAME, INDCOL.INDEX_COLUMN_ID COLUMN_POSITION, MAX(INDCOL.INDEX_COLUMN_ID) OVER (PARTITION BY IND.NAME ) MAX_POSITION
FROM SYS.INDEXES IND
JOIN SYS.INDEX_COLUMNS INDCOL
ON INDCOL.OBJECT_ID = IND.OBJECT_ID
AND INDCOL.INDEX_ID = IND.INDEX_ID
JOIN SYS.COLUMNS COL
ON COL.OBJECT_ID = INDCOL.OBJECT_ID
AND COL.COLUMN_ID = INDCOL.COLUMN_ID
JOIN SYS.TABLES T
ON T.OBJECT_ID = IND.OBJECT_ID
WHERE T.NAME = 'DRP_IDX_TEST'
), GET_INDEX_LIST (INDEX_NAME, COLUMN_NAME, COLUMN_ORDER) AS
(SELECT UI.INDEX_NAME, BI.COLUMN_NAME, (CASE WHEN BI.COLUMN_NAME IS NULL THEN 0 ELSE 1 END) AS COLUMN_ORDER
FROM BASE_IDX BI
RIGHT JOIN USER_IDX UI
ON UI.COLUMN_NAME = BI.COLUMN_NAME
AND UI.COLUMN_POSITION = BI.COLUMN_POSITION
AND UI.MAX_POSITION = BI.MAX_POSITION)
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_ORDER, FIRST_VALUE(COLUMN_NAME) OVER (PARTITION BY INDEX_NAME ORDER BY COLUMN_ORDER) FIRST_VAL
FROM GET_INDEX_LIST
Trying to get output as below:
| INDEX_NAME | COLUMN_NAME | COLUMN_ORDER | FIRST_VAL |
| col1_col2_col3_idx | NULL | 0 | NULL |
| col1_col2_col3_idx | NULL | 0 | NULL |
| col1_col2_col3_idx | COL1 | 1 | NULL |
| col1_col2_idx | NULL | 0 | NULL |
| col1_col2_idx | NULL | 0 | NULL |
| col1_col3_col2_idx | COL1 | 1 | COL1 |
| col1_col3_col2_idx | COL3 | 1 | COL1 |
| col1_col3_col2_idx | COL2 | 1 | COL1 |
| col1_col4_col3_idx | NULL | 0 | NULL |
| col1_col4_col3_idx | NULL | 0 | NULL |
| col1_col4_col3_idx | COL1 | 1 | NULL |
| col1_idx | NULL | 0 | NULL |
| col2_col1_idx | NULL | 0 | NULL |
| col2_col1_idx | NULL | 0 | NULL |
| col2_idx | NULL | 0 | NULL |
January 23, 2017 at 1:34 pm
Favor, please.
Please explain what it is you are trying to accomplish, and then explain why the results of the query are wrong, and how you expect the results that you want.
January 23, 2017 at 1:36 pm
FIRST_VALUE requires a ROWS/RANGE clause. Since you haven't supplied one, it is using the default ROWS UNBOUNDED PRECEDING.
FIRST_VALUE returns the first value. Null is not a value, it's the lack of a value. Instead of using FIRST_VALUE, you might consider using a CROSS APPLY in conjunction with a TOP (1).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2017 at 1:50 pm
drew.allen - Monday, January 23, 2017 1:36 PMFIRST_VALUE requires a ROWS/RANGE clause. Since you haven't supplied one, it is using the default ROWS UNBOUNDED PRECEDING.FIRST_VALUE returns the first value. Null is not a value, it's the lack of a value. Instead of using FIRST_VALUE, you might consider using a CROSS APPLY in conjunction with a TOP (1).
Drew
Thanks Drew, but could you please help me understand why below code works? Below query takes NULL from COL2 when partitioned by COL1 and ordered by COL2
CREATE TABLE TESTT (COL1 INT, COL2 INT)
INSERT INTO TESTT VALUES (1, 0)
INSERT INTO TESTT VALUES (1, 1)
INSERT INTO TESTT VALUES (1, NULL)
INSERT INTO TESTT VALUES (1, 3)
INSERT INTO TESTT VALUES (2, 0)
INSERT INTO TESTT VALUES (2, NULL)
INSERT INTO TESTT VALUES (3, 3)
INSERT INTO TESTT VALUES (3, 1)
SELECT COL1, COL2, FIRST_VALUE (COL2) OVER (PARTITION BY COL1 ORDER BY COL2) FROM TESTT
January 23, 2017 at 2:37 pm
vpolasa - Monday, January 23, 2017 1:50 PMdrew.allen - Monday, January 23, 2017 1:36 PMFIRST_VALUE requires a ROWS/RANGE clause. Since you haven't supplied one, it is using the default ROWS UNBOUNDED PRECEDING.FIRST_VALUE returns the first value. Null is not a value, it's the lack of a value. Instead of using FIRST_VALUE, you might consider using a CROSS APPLY in conjunction with a TOP (1).
Drew
Thanks Drew, but could you please help me understand why below code works? Below query takes NULL from COL2 when partitioned by COL1 and ordered by COL2
CREATE TABLE TESTT (COL1 INT, COL2 INT)
INSERT INTO TESTT VALUES (1, 0)
INSERT INTO TESTT VALUES (1, 1)
INSERT INTO TESTT VALUES (1, NULL)
INSERT INTO TESTT VALUES (1, 3)
INSERT INTO TESTT VALUES (2, 0)
INSERT INTO TESTT VALUES (2, NULL)
INSERT INTO TESTT VALUES (3, 3)
INSERT INTO TESTT VALUES (3, 1)SELECT COL1, COL2, FIRST_VALUE (COL2) OVER (PARTITION BY COL1 ORDER BY COL2) FROM TESTT
That does seem odd. I'm also getting the correct results when I don't use your CTE.
CREATE TABLE #GET_INDEX_LIST ( [INDEX_NAME] nvarchar(128), [COLUMN_NAME] varchar(30), [COLUMN_ORDER] int )
INSERT INTO #GET_INDEX_LIST
VALUES
( N'col1_col2_col3_idx', NULL, 0 ),
( N'col1_col2_col3_idx', NULL, 0 ),
( N'col1_col2_col3_idx', 'COL1', 1 ),
( N'col1_col2_idx', NULL, 0 ),
( N'col1_col2_idx', NULL, 0 ),
( N'col1_col3_col2_idx', 'COL1', 1 ),
( N'col1_col3_col2_idx', 'COL3', 1 ),
( N'col1_col3_col2_idx', 'COL2', 1 ),
( N'col1_col4_col3_idx', NULL, 0 ),
( N'col1_col4_col3_idx', NULL, 0 ),
( N'col1_col4_col3_idx', 'COL1', 1 ),
( N'col1_idx', NULL, 0 ),
( N'col2_col1_idx', NULL, 0 ),
( N'col2_col1_idx', NULL, 0 ),
( N'col2_idx', NULL, 0 )
SELECT *, FIRST_VALUE(COLUMN_NAME) OVER(PARTITION BY INDEX_NAME ORDER BY COLUMN_ORDER)
FROM #GET_INDEX_LIST
DROP TABLE #GET_INDEX_LIST
You can also use the NULLIF/ISNULL technique to get the results that you are looking for.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2017 at 2:44 pm
vpolasa - Monday, January 23, 2017 1:50 PMThanks Drew, but could you please help me understand why below code works? Below query takes NULL from COL2 when partitioned by COL1 and ordered by COL2
CREATE TABLE TESTT (COL1 INT, COL2 INT)
INSERT INTO TESTT VALUES (1, 0)
INSERT INTO TESTT VALUES (1, 1)
INSERT INTO TESTT VALUES (1, NULL)
INSERT INTO TESTT VALUES (1, 3)
INSERT INTO TESTT VALUES (2, 0)
INSERT INTO TESTT VALUES (2, NULL)
INSERT INTO TESTT VALUES (3, 3)
INSERT INTO TESTT VALUES (3, 1)SELECT COL1, COL2, FIRST_VALUE (COL2) OVER (PARTITION BY COL1 ORDER BY COL2) FROM TESTT
Define "not working". Your SQL returns what I expect it to.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 23, 2017 at 3:27 pm
drew.allen - Monday, January 23, 2017 2:37 PMvpolasa - Monday, January 23, 2017 1:50 PMdrew.allen - Monday, January 23, 2017 1:36 PMFIRST_VALUE requires a ROWS/RANGE clause. Since you haven't supplied one, it is using the default ROWS UNBOUNDED PRECEDING.FIRST_VALUE returns the first value. Null is not a value, it's the lack of a value. Instead of using FIRST_VALUE, you might consider using a CROSS APPLY in conjunction with a TOP (1).
Drew
Thanks Drew, but could you please help me understand why below code works? Below query takes NULL from COL2 when partitioned by COL1 and ordered by COL2
CREATE TABLE TESTT (COL1 INT, COL2 INT)
INSERT INTO TESTT VALUES (1, 0)
INSERT INTO TESTT VALUES (1, 1)
INSERT INTO TESTT VALUES (1, NULL)
INSERT INTO TESTT VALUES (1, 3)
INSERT INTO TESTT VALUES (2, 0)
INSERT INTO TESTT VALUES (2, NULL)
INSERT INTO TESTT VALUES (3, 3)
INSERT INTO TESTT VALUES (3, 1)SELECT COL1, COL2, FIRST_VALUE (COL2) OVER (PARTITION BY COL1 ORDER BY COL2) FROM TESTT
That does seem odd. I'm also getting the correct results when I don't use your CTE.
CREATE TABLE #GET_INDEX_LIST ( [INDEX_NAME] nvarchar(128), [COLUMN_NAME] varchar(30), [COLUMN_ORDER] int )
INSERT INTO #GET_INDEX_LIST
VALUES
( N'col1_col2_col3_idx', NULL, 0 ),
( N'col1_col2_col3_idx', NULL, 0 ),
( N'col1_col2_col3_idx', 'COL1', 1 ),
( N'col1_col2_idx', NULL, 0 ),
( N'col1_col2_idx', NULL, 0 ),
( N'col1_col3_col2_idx', 'COL1', 1 ),
( N'col1_col3_col2_idx', 'COL3', 1 ),
( N'col1_col3_col2_idx', 'COL2', 1 ),
( N'col1_col4_col3_idx', NULL, 0 ),
( N'col1_col4_col3_idx', NULL, 0 ),
( N'col1_col4_col3_idx', 'COL1', 1 ),
( N'col1_idx', NULL, 0 ),
( N'col2_col1_idx', NULL, 0 ),
( N'col2_col1_idx', NULL, 0 ),
( N'col2_idx', NULL, 0 )SELECT *, FIRST_VALUE(COLUMN_NAME) OVER(PARTITION BY INDEX_NAME ORDER BY COLUMN_ORDER)
FROM #GET_INDEX_LISTDROP TABLE #GET_INDEX_LIST
You can also use the NULLIF/ISNULL technique to get the results that you are looking for.
Drew
Thanks again Drew. I used the case statement (CASE WHEN COLUMN_NAME IS NULL THEN 0 ELSE 1 END) and looks like it's working. But still puzzled why my CTE isn't working for this scenario.
January 23, 2017 at 3:30 pm
Thom A - Monday, January 23, 2017 2:44 PMvpolasa - Monday, January 23, 2017 1:50 PMThanks Drew, but could you please help me understand why below code works? Below query takes NULL from COL2 when partitioned by COL1 and ordered by COL2
CREATE TABLE TESTT (COL1 INT, COL2 INT)
INSERT INTO TESTT VALUES (1, 0)
INSERT INTO TESTT VALUES (1, 1)
INSERT INTO TESTT VALUES (1, NULL)
INSERT INTO TESTT VALUES (1, 3)
INSERT INTO TESTT VALUES (2, 0)
INSERT INTO TESTT VALUES (2, NULL)
INSERT INTO TESTT VALUES (3, 3)
INSERT INTO TESTT VALUES (3, 1)SELECT COL1, COL2, FIRST_VALUE (COL2) OVER (PARTITION BY COL1 ORDER BY COL2) FROM TESTT
Define "not working". Your SQL returns what I expect it to.
Could you please try the test code I posted in my first post and compare the output with the expected output in the same post? The output values for FIRST_VAL will be different. Please help me understand why we are seeing the output?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply