Luis Diaz
asked on
SQL: get ride of blank rows
Hello experts,
I use the following query which give me the attached result.
I don't know why I have empty rows in the result even if I tried with the is not null <> '' properties but I still having empty rows.
Any idea of what should be add in the where clause to get ride of the blank rows?
Thank you again for your help.
blank_rows_sql.xlsx
I use the following query which give me the attached result.
SET NOCOUNT ON
SELECT ProjectID1,
ProjectName,
WorkPackageID,
WorkPackageExtID,
Name
FROM (
SELECT CAST(P.ID AS NVARCHAR) as ProjectID1,
ProjectName,
T.WorkPackageID,
WorkPackageExtID,
T.Name,
ROW_NUMBER() OVER(PARTITION BY P.ID, T.WorkPackageID ORDER BY WorkPackageExtID DESC) AS row_num
FROM mstt_schema.Task T
LEFT JOIN mstt_schema.Project P ON P.InternalID = T.ProjectID
WHERE ProjectVersion = 1 and ProjectActive = 1 and MacroScheduleWP = 1 and P.[Version] = 1 and WorkPackageID <> 0 and (WorkPackageID IS NOT NULL)
) AS derived
WHERE row_num = 1
I don't know why I have empty rows in the result even if I tried with the is not null <> '' properties but I still having empty rows.
Any idea of what should be add in the where clause to get ride of the blank rows?
Thank you again for your help.
blank_rows_sql.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your excel files has thousands of rows. Can you tell us an example of an empty row?
First of all: A screenshot from SSMS would be better in such a case, cause using your Excel file, I don't find "blank rows". Are your sure that there are such rows? Not that this is just a simple Excel problem?
You can try to find "blank rows" by using e.g.
But I don't expect any rows. Cause WorkPackageID, WorkPackageExtID are part of your data model and used in the OVER clause. Thus I guess they are NOT NULL and numeric.
You can try to find "blank rows" by using e.g.
SET NOCOUNT ON;
SELECT ProjectID1 ,
ProjectName ,
WorkPackageID ,
WorkPackageExtID ,
Name
FROM ( SELECT CAST(P.ID AS NVARCHAR) AS ProjectID1 ,
ProjectName ,
T.WorkPackageID ,
WorkPackageExtID ,
T.Name ,
ROW_NUMBER() OVER ( PARTITION BY P.ID, T.WorkPackageID ORDER BY WorkPackageExtID DESC ) AS row_num
FROM mstt_schema.Task T
LEFT JOIN mstt_schema.Project P ON P.InternalID = T.ProjectID
WHERE ProjectVersion = 1
AND ProjectActive = 1
AND MacroScheduleWP = 1
AND P.[Version] = 1
AND WorkPackageID <> 0
AND ( WorkPackageID IS NOT NULL )
) AS derived
WHERE row_num = 1
AND LEN(LTRIM(ISNULL(CAST(ProjectID1 AS NVARCHAR(255)), ''))) = 0
AND LEN(LTRIM(ISNULL(CAST(ProjectName AS NVARCHAR(255)), ''))) = 0
AND LEN(LTRIM(ISNULL(CAST(WorkPackageID AS NVARCHAR(255)), ''))) = 0
AND LEN(LTRIM(ISNULL(CAST(NAME AS NVARCHAR(255)), ''))) = 0
AND LEN(LTRIM(ISNULL(CAST(WorkPackageExtID AS NVARCHAR(255)), ''))) = 0;
But I don't expect any rows. Cause WorkPackageID, WorkPackageExtID are part of your data model and used in the OVER clause. Thus I guess they are NOT NULL and numeric.
ASKER
Thank you all for your feedback.
In fact, when I make a copy paste of my initial request into a excel file I have the blank row however the initial query doesn't select blank rows as shown:
Excel:
SQL:
I tried the query by Ste5an however I don't have any reported info.
Any ideas?
Thank you again for your help.
In fact, when I make a copy paste of my initial request into a excel file I have the blank row however the initial query doesn't select blank rows as shown:
Excel:
SQL:
I tried the query by Ste5an however I don't have any reported info.
Any ideas?
Thank you again for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LD16, a feedback will be appreciated.
Cheers
Cheers
ASKER
Sorry again for the delay. I removed the blank as proposed and it works.