July 5, 2012 at 12:30 pm
Hello all, I am new here. I am a SQL newbie, but I have a huge task assigned to me. I basically have to count rows in a bunch of tables where certain 'marked' columns don't have any NULLs. Consider the following example:
http://dl.dropbox.com/u/85597623/TableStats2.PNG
Please note that Table A contains many more columns, but only the 4 shown are to be considered. For the assignment, the total table count is 40, total column count is around 1400 and the average rows count across the 40 tables is ~5000.
To flesh out my dynamic SQL for the commented cell in the above example, I wrote the following code using a couple of cursors: http://dl.dropbox.com/u/85597623/code1.sql CONFIGURATIONITEMATTRIBUTES = top portion of the above example.
My output contains an additional 'OR' as shown here; I can't seem to get rid of it: http://dl.dropbox.com/u/85597623/ExtraOR.PNG I understand why it is there, but as I am new to writing SPs (this is my first attempt), I don't know how to get rid of it.
Once the SQL statements are properly formed, I will also need to replace all the 'print' commands with a proper INSERT statement to get the 'Statistics' table shown in the bottom portion of the example.
I apologize for the dropbox links; I am just trying to be careful with company information. Finally, I have crossposted this on another site. If this is against the rules, please let me know.
Thanks much in advance! Please do ask if anything is unclear.
EDITS: DDL for the tables and the resultset are provided below, thanks to Cadavre.
--===== If the test tables already exists, drop them
IF OBJECT_ID('TempDB..#T1', 'U') IS NOT NULL
BEGIN
DROP TABLE #T1;
END;
IF OBJECT_ID('TempDB..#T2', 'U') IS NOT NULL
BEGIN
DROP TABLE #T2;
END;
IF OBJECT_ID('TempDB..#Metadata', 'U') IS NOT NULL
BEGIN
DROP TABLE #Metadata;
END;
--===== Create the test table T1 with
CREATE TABLE #T1 (COL1 INT, COL2 VARCHAR(64), COL3 INT, COL4 VARCHAR(64), COL5 VARCHAR(32), COL6 INT);
INSERT INTO #T1 (COL1, COL2, COL3, COL4, COL5, COL6)
SELECT '20', 'A', '1012', '', 'XYZ', '1' UNION ALL
SELECT '21', 'A', '1013', '', 'ABC', '2' UNION ALL
SELECT '22', 'A', '', '', 'NAS', '2' UNION ALL
SELECT '23', 'A', '1012', '', 'ABC', '2' UNION ALL
SELECT '24', 'A', '1013', '', 'ABC', '2' UNION ALL
SELECT '25', 'A', '', '', 'ABC', '3' UNION ALL
SELECT '26', 'A', '2000', 'XLS', '', '3' UNION ALL
SELECT '27', 'A', '1012', 'XLS', '', '3' UNION ALL
SELECT '28', 'A', '1013', 'XLSX', 'NAS', '3' UNION ALL
SELECT '29', 'A', '', 'XLS', '', '3' UNION ALL
SELECT '30', 'A', '2000', 'XLSX', 'NAS', '3' UNION ALL
SELECT '31', 'A', '', 'XLS', 'XYZ', '3' UNION ALL
SELECT '32', 'A', '', '', 'XYZ', '3' UNION ALL
SELECT '33', 'A', '2000', '', 'NAS', '4' UNION ALL
SELECT '34', 'A', '2000', '', 'NAS', '4' UNION ALL
SELECT '35', 'A', '', '', 'ABC', '5' UNION ALL
SELECT '36', 'A', '1013', 'CSV', 'XYZ', '5' UNION ALL
SELECT '37', 'A', '1013', '', '', '5' UNION ALL
SELECT '38', 'A', '', '', 'NAS', '5' UNION ALL
SELECT '39', 'A', '', 'CSV', 'ABC', '6';
--===== Create the test table T2 with
CREATE TABLE #T2 (COL1 INT, COL2 VARCHAR(64), COL3 INT, COL4 VARCHAR(64), COL5 VARCHAR(32), COL6 INT, COL7 VARCHAR(64));
INSERT INTO #T2 (COL1, COL2, COL3, COL4, COL5, COL6, COL7)
SELECT '514', 'BBQ', '126', '', 'XYZ', '1', 'NA' UNION ALL
SELECT '515', 'BBQ', '', '', 'ABC', '2', 'NA' UNION ALL
SELECT '516', '', '', '', 'NAS', '9', 'LA' UNION ALL
SELECT '517', '', '733', 'XLSB', 'ABC', '9', 'BE' UNION ALL
SELECT '518', '', '561', '', 'ABC', '2', 'BE' UNION ALL
SELECT '519', 'BBQ', '571', 'XLSB', 'ABC', '3', 'IL' UNION ALL
SELECT '520', '', '295', 'XLS', '', '3', 'XO' UNION ALL
SELECT '521', 'OMG', '300', 'XLS', '', '3', '' UNION ALL
SELECT '522', 'OMG', '951', 'XLSX', 'NAS', '3', 'XO' UNION ALL
SELECT '523', 'OMG', '71', 'XLS', '', '14', 'XO' UNION ALL
SELECT '', '', '', 'XLSX', 'NAS', '2', 'XO' UNION ALL
SELECT '', '', '88', 'XLS', 'XYZ', '2', 'XO' UNION ALL
SELECT '526', 'LOL', '367', '', 'XYZ', '3', 'BE' UNION ALL
SELECT '527', 'LOL', '14', 'XLSM', 'NAS', '7', 'HO' UNION ALL
SELECT '528', 'WAT', '808', 'XLSM', 'NAS', '6', 'HO' UNION ALL
SELECT '529', '', '642', '', 'ABC', '5', 'HO';
--===== Create the test table Metadata with
CREATE TABLE #Metadata (
VARCHAR(128), [COLUMN] VARCHAR(128), MANDATORY BIT, FILTER NVARCHAR(MAX));
INSERT INTO #Metadata (
, [COLUMN], MANDATORY, FILTER)
SELECT 'T1', 'COL2', '0', '1=1' UNION ALL
SELECT 'T1', 'COL3', '1', '1=1' UNION ALL
SELECT 'T1', 'COL4', '1', '1=1' UNION ALL
SELECT 'T1', 'COL5', '0', 'T1.COL6 = 3' UNION ALL --<< IF T1.COL6 = 3 THEN CHECK COL5 FOR NULLs, OTHERWISE IRRELEVANT WHETHER COL5 IS NULL OR NOT (MARK ROW AS COMPLETE IF COL2 IS NOT NULL)
SELECT 'T2', 'COL1', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL --<< T2.COL7 = 'XO' IS A TABLE-LEVEL FILTER, I.E. ACTIVE ON THIS ENTIRE TABLE ITERATION
SELECT 'T2', 'COL2', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL3', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL5', '0', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL6', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL1', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL --<< T2.COL7 IN ('BE', 'LA') IS A ALSO A TABLE-LEVEL FILTER, I.E. ACTIVE ON THIS ENTIRE TABLE ITERATION
SELECT 'T2', 'COL2', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL3', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL5', '0', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL6', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')';
RESULTSET DDL
SELECT
, [Total Rows], [Mandatory Complete], [Optional Complete], [Mandatory Pctg], [Optional Pctg]
FROM (SELECT 'T1', 20, 5, 17, '25%', '85%' UNION ALL
SELECT 'T2 (XO)', 5, 2, 3, '40%', '60%' UNION ALL
SELECT 'T2 (BE, LA)', 4, 1, 4, '25%', '100%'
) a(
, [Total Rows], [Mandatory Complete], [Optional Complete], [Mandatory Pctg], [Optional Pctg]);
RESULTSET OUTPUT
TABLE Total Rows Mandatory Complete Optional Complete Mandatory Pctg Optional Pctg
----------- ----------- ------------------ ----------------- -------------- -------------
T1 20 5 17 25% 85%
T2 (XO) 5 2 3 40% 60%
T2 (BE, LA) 4 1 4 25% 100%
July 5, 2012 at 12:39 pm
Please post table definitions and details either in your post or as attachments. I'm not clicking on a link to some file that could be anything, I know a lot of others have the same feeling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2012 at 1:03 pm
I have deobfuscated the links, is this good enough? If not, I can change them.
July 5, 2012 at 1:42 pm
It isn't just the links but making them a bit more clear helps. From what you posted nobody can run anything. It is impossible to test sql when there are no tables to run your code against.
Take a look at the first link in my signature for best practices when posting questions. This same applies to the other site(s) that deal with sql help. They will all ask for the same thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 1:47 pm
And FWIW from the description and the cursor you posted I don't think there is any need at all for a cursor. Cursors are notoriously slow and should be avoided in most situations (there are a FEW administrative type things were they are neccesary). This does not appear to be a situation where a cursor is needed. If you can post ddl, sample data and desired output I am pretty confident this can be done in a single query with no looping or cursors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 3:40 pm
I have updated the top post to add some sample data. Thanks guys.
July 5, 2012 at 3:42 pm
zxxz (7/5/2012)
I have updated the top post to add some sample data. Thanks guys.
That is better but...nobody can run that. We are all volunteers around here and nobody wants to spend a bunch of time creating your tables and then building insert statements. I am guessing you didn't actually read the article that was suggested. You should be posting ddl (create table statements), sample data (insert statements) then desired output based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 4:47 pm
Sorry again, re-updating the original post. 🙂 I apologize in advance for any mistakes, but I don't think there are any.
July 6, 2012 at 3:17 am
zxxz (7/5/2012)
Sorry again, re-updating the original post. 🙂 I apologize in advance for any mistakes, but I don't think there are any.
Corrected your DDL: -
--===== If the test tables already exists, drop them
IF OBJECT_ID('TempDB..#T1', 'U') IS NOT NULL
BEGIN
DROP TABLE #T1;
END;
IF OBJECT_ID('TempDB..#T2', 'U') IS NOT NULL
BEGIN
DROP TABLE #T2;
END;
IF OBJECT_ID('TempDB..#Metadata', 'U') IS NOT NULL
BEGIN
DROP TABLE #Metadata;
END;
--===== Create the test table T1 with
CREATE TABLE #T1 (COL1 INT, COL2 VARCHAR(64), COL3 INT, COL4 VARCHAR(64), COL5 VARCHAR(32), COL6 INT);
INSERT INTO #T1 (COL1, COL2, COL3, COL4, COL5, COL6)
SELECT '20', 'A', '1012', '', 'XYZ', '1' UNION ALL
SELECT '21', 'A', '1013', '', 'ABC', '2' UNION ALL
SELECT '22', 'A', '', '', 'NAS', '2' UNION ALL
SELECT '23', 'A', '1012', '', 'ABC', '2' UNION ALL
SELECT '24', 'A', '1013', '', 'ABC', '2' UNION ALL
SELECT '25', 'A', '', '', 'ABC', '3' UNION ALL
SELECT '26', 'A', '2000', 'XLS', '', '3' UNION ALL
SELECT '27', 'A', '1012', 'XLS', '', '3' UNION ALL
SELECT '28', 'A', '1013', 'XLSX', 'NAS', '3' UNION ALL
SELECT '29', 'A', '', 'XLS', '', '3' UNION ALL
SELECT '30', 'A', '2000', 'XLSX', 'NAS', '3' UNION ALL
SELECT '31', 'A', '', 'XLS', 'XYZ', '3' UNION ALL
SELECT '32', 'A', '', '', 'XYZ', '3' UNION ALL
SELECT '33', 'A', '2000', '', 'NAS', '4' UNION ALL
SELECT '34', 'A', '2000', '', 'NAS', '4' UNION ALL
SELECT '35', 'A', '', '', 'ABC', '5' UNION ALL
SELECT '36', 'A', '1013', 'CSV', 'XYZ', '5' UNION ALL
SELECT '37', 'A', '1013', '', '', '5' UNION ALL
SELECT '38', 'A', '', '', 'NAS', '5' UNION ALL
SELECT '39', 'A', '', 'CSV', 'ABC', '6';
--===== Create the test table T2 with
CREATE TABLE #T2 (COL1 INT, COL2 VARCHAR(64), COL3 INT, COL4 VARCHAR(64), COL5 VARCHAR(32), COL6 INT, COL7 VARCHAR(64));
INSERT INTO #T2 (COL1, COL2, COL3, COL4, COL5, COL6, COL7)
SELECT '514', 'BBQ', '126', '', 'XYZ', '1', 'NA' UNION ALL
SELECT '515', 'BBQ', '', '', 'ABC', '2', 'NA' UNION ALL
SELECT '516', '', '', '', 'NAS', '9', 'LA' UNION ALL
SELECT '517', '', '733', 'XLSB', 'ABC', '9', 'BE' UNION ALL
SELECT '518', '', '561', '', 'ABC', '2', 'BE' UNION ALL
SELECT '519', 'BBQ', '571', 'XLSB', 'ABC', '3', 'IL' UNION ALL
SELECT '520', '', '295', 'XLS', '', '3', 'XO' UNION ALL
SELECT '521', 'OMG', '300', 'XLS', '', '3', '' UNION ALL
SELECT '522', 'OMG', '951', 'XLSX', 'NAS', '3', 'XO' UNION ALL
SELECT '523', 'OMG', '71', 'XLS', '', '14', 'XO' UNION ALL
SELECT '', '', '', 'XLSX', 'NAS', '2', 'XO' UNION ALL
SELECT '', '', '88', 'XLS', 'XYZ', '2', 'XO' UNION ALL
SELECT '526', 'LOL', '367', '', 'XYZ', '3', 'BE' UNION ALL
SELECT '527', 'LOL', '14', 'XLSM', 'NAS', '7', 'HO' UNION ALL
SELECT '528', 'WAT', '808', 'XLSM', 'NAS', '6', 'HO' UNION ALL
SELECT '529', '', '642', '', 'ABC', '5', 'HO';
--===== Create the test table T1 with
CREATE TABLE #Metadata (
VARCHAR(128), [COLUMN] VARCHAR(128), MANDATORY BIT, FILTER NVARCHAR(MAX));
INSERT INTO #Metadata (
, [COLUMN], MANDATORY, FILTER)
SELECT 'T1', 'COL2', '0', '' UNION ALL
SELECT 'T1', 'COL3', '1', '' UNION ALL
SELECT 'T1', 'COL4', '1', '' UNION ALL
SELECT 'T1', 'COL5', '0', 'T1.COL6 = 3' UNION ALL --<< IF T1.COL6 = 3 THEN CHECK COL5 FOR NULLs, OTHERWISE IRRELEVANT WHETHER COL5 IS NULL OR NOT (MARK ROW AS COMPLETE IF COL2 IS NOT NULL)
SELECT 'T2', 'COL1', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL --<< T2.COL7 = 'XO' IS A TABLE-LEVEL FILTER, I.E. ACTIVE ON THIS ENTIRE TABLE ITERATION
SELECT 'T2', 'COL2', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL3', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL5', '0', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL6', '1', 'T2.COL7 = ' + CHAR(39) + 'XO' + CHAR(39) UNION ALL
SELECT 'T2', 'COL1', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL --<< T2.COL7 IN ('BE', 'LA') IS A ALSO A TABLE-LEVEL FILTER, I.E. ACTIVE ON THIS ENTIRE TABLE ITERATION
SELECT 'T2', 'COL2', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL3', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL5', '0', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')' UNION ALL
SELECT 'T2', 'COL6', '1', 'T2.COL7 IN (' + CHAR(39) + 'BE' + CHAR(39) + ', ' + CHAR(39) + 'LA' + CHAR(39) + ')';
Expected result for those that don't want to download a random forum poster's XLS file :hehe:
SELECT
, [Total Rows], [Mandatory Complete], [Optional Complete], [Mandatory Pctg], [Optional Pctg]
FROM (SELECT 'T1', 20, 5, 17, '25%', '85%' UNION ALL
SELECT 'T2 (XO)', 5, 2, 3, '40%', '60%' UNION ALL
SELECT 'T2 (BE, LA)', 4, 1, 4, '25%', '100%'
) a(
, [Total Rows], [Mandatory Complete], [Optional Complete], [Mandatory Pctg], [Optional Pctg]);
Returns: -
TABLE Total Rows Mandatory Complete Optional Complete Mandatory Pctg Optional Pctg
----------- ----------- ------------------ ----------------- -------------- -------------
T1 20 5 17 25% 85%
T2 (XO) 5 2 3 40% 60%
T2 (BE, LA) 4 1 4 25% 100%
After spending all the time it took to make your DDL and sample data scripts work, then downloading the XLS on a virtual machine that isn't connected to my network and scripting the result-set you require I've used the 10 minutes I had free to try and help you answer your question. Hopefully, the work I did will benefit someone else that wishes to help, but you should take on board that not supplying enough information is just as bad as supplying non-working code that has to be edited. Volunteer forum users often have a spare few minutes in between tasks that they are doing themselves to help answer questions, if they have to spend those spare minutes writing the test data from your descriptions or fixing the test data you have supplied then often you'll be passed over for someone that has supplied all that is required.
Not having a go or anything, but something to keep in mind next time. 😉
July 6, 2012 at 8:50 am
I just can't seem to get it right, can I? Thank you for your work; my experience with SQL does not go much beyond 'SELECT... FROM'. I simply tried to follow the 'Best Practices' and should have executed the code before posting it.
I will take the liberty of replacing your DDL scripts with mine in the top post. Thank you again.
July 6, 2012 at 9:17 am
zxxz (7/6/2012)
I just can't seem to get it right, can I? Thank you for your work; my experience with SQL does not go much beyond 'SELECT... FROM'. I simply tried to follow the 'Best Practices' and should have executed the code before posting it.I will take the liberty of replacing your DDL scripts with mine in the top post. Thank you again.
The single most important lesson you will (hopefully) learn from this is always test.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2012 at 3:34 pm
Does anyone want to help me take a crack at this?
To calculate the 'Mandatory Complete' column in the resultset, I wrote the following to generate dynamic SQL using cursors, but it is not completely accurate:
DECLARE @COLUMN nvarchar(128);
DECLARE @TABLE nvarchar(128);
DECLARE @FILTER nvarchar(max);
DECLARE TBL CURSOR FOR
SELECT DISTINCT
, FILTER FROM #METADATA;
OPEN TBL
FETCH NEXT FROM TBL INTO @TABLE, @FILTER;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'SELECT COUNT(*) ''M_CmpltCnt'' FROM ' + @TABLE + ' WHERE ' + @FILTER
DECLARE COL CURSOR FOR
SELECT DISTINCT [COLUMN] FROM #METADATA WHERE
= @TABLE AND MANDATORY = 1;
OPEN COL
FETCH NEXT FROM COL INTO @COLUMN;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'AND ' + @COLUMN + ' IS NOT NULL';
FETCH NEXT FROM COL INTO @COLUMN;
END
CLOSE COL;
DEALLOCATE COL;
FETCH NEXT FROM TBL INTO @TABLE, @FILTER;
END
CLOSE TBL;
DEALLOCATE TBL;
The output of that SQL was the following:
SELECT COUNT(*) 'M_CmpltCnt' FROM T1 WHERE 1=1
AND COL3 IS NOT NULL
AND COL4 IS NOT NULL
SELECT COUNT(*) 'M_CmpltCnt' FROM T1 WHERE T1.COL6 = 3
AND COL3 IS NOT NULL
AND COL4 IS NOT NULL
SELECT COUNT(*) 'M_CmpltCnt' FROM T2 WHERE T2.COL7 = 'XO'
AND COL1 IS NOT NULL
AND COL2 IS NOT NULL
AND COL3 IS NOT NULL
AND COL6 IS NOT NULL
SELECT COUNT(*) 'M_CmpltCnt' FROM T2 WHERE T2.COL7 IN ('BE', 'LA')
AND COL1 IS NOT NULL
AND COL2 IS NOT NULL
AND COL3 IS NOT NULL
AND COL6 IS NOT NULL
Note that the second SELECT COUNT(*) statement is unwanted and incorrect. As explained in the #Metadata DDL, T1.COL6 = 3 is only to apply on COL5, which is an optional column and shouldn't show up here at all...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply