Big problem with empty (NULL?) Fields...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Big problem with empty (NULL?) Fields...

Author

Topic

olivierb
Starting Member

6 Posts

Posted - 2014-02-19 : 10:14:36

Hello Everyone,

I am working on a query on wich I need to displays also the empty field, but I doesnt'work

It on tblPackageApplication.Comment

I want to not display where there is '%obsolète%' - And it works perfectly.

Anyway, it doesn't display EMPTY FIELD.

If someone have a suggestion, I will sleep tonight ;>)

Here is my query

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tblPackageApplication.Name ORDER BY tblPackageApplication.Name) AS 'Seq',
tblBusinessApplication.Name AS 'NOM',
tblApplicationLayer.ApplicationLayerCode AS 'Catégorie',
tblPackageApplication.Name AS 'PackageApplicationName',
tblPackageApplication.Editor AS 'Editeur',
tblPackageApplication.Version AS 'Version',
tblPackageApplication.Application AS 'Référence Editeur',
tblPackageApplication.ID AS 'ID Package',
tblBusinessApplication.ApplicationID AS 'ID Business Application',
CAST(tblPackageApplication.ID AS varchar(6)) + '-' + CAST(tblBusinessApplication.ApplicationID as varchar (6))as 'IDpkApp',
tblBusinessApplication.Comment AS 'Commentaires',
tblActor_1.Name AS 'Contact',
tblMode.StatusDate AS 'StatusDate',

tblPackageApplication.Comment

FROM
tblPackageApplication FULL OUTER JOIN
tblBusinessApplication FULL OUTER JOIN
tblApplicationLayer ON tblBusinessApplication.ApplicationLayerID = tblApplicationLayer.ApplicationLayerID FULL OUTER JOIN
tblBusinessPackage ON tblBusinessApplication.ApplicationID = tblBusinessPackage.ApplicationID ON
tblPackageApplication.ID = tblBusinessPackage.PackageApplication FULL OUTER JOIN
tblPackaging ON tblPackageApplication.ID = tblPackaging.PackageApplicationID FULL OUTER JOIN
tblActor ON tblBusinessApplication.ProjectManagerActorId = tblActor.ID FULL OUTER JOIN
tblActor AS tblActor_1 ON tblBusinessApplication.ContactActorID = tblActor_1.ID FULL OUTER JOIN
tblMode ON tblPackaging.ID = tblMode.PackagingID

WHERE
(tblPackageApplication.Comment NOT LIKE '%obsolète%') AND
(tblBusinessApplication.RichClient LIKE 'oui') AND
(tblBusinessApplication.Win7Scope LIKE 'oui') AND
(tblPackageApplication.Version NOT LIKE 'X.X') AND
(tblBusinessPackage.TypeBusinessApplication LIKE 'p')
)t
WHERE Seq=1
--ORDER BY 'ID Package'
ORDER BY 'PackageApplicationName'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-19 : 15:28:49

I guess I don't understand what you want. If you are filtering out the rows that contain '%obsolète%' then there is nothing to replace (basically you have turned your outer joins into INNER joins.) Maybe you really want to move the predicate (tblPackageApplication.Comment NOT LIKE '%obsolète%') out of the WHERE clause and put it onto the join condition instead?

olivierb
Starting Member

6 Posts

Posted - 2014-02-20 : 06:12:26

Dear Friend,

Sorry for my English...

In fact, the rows in "tblPackageApplication.Comment" are well filtered with the good condition, (NOT LIKE '%obsolète%...), but the rows that containes empty fields are not displayed, wich is a problem for me.

And I would like to write something like

WHERE (tblPackageApplication.Comment NOT LIKE '%obsolète%') OR (tblPackageApplication.Comment IS NULL)

To get the empty ones, but id doesnt work

I hope it is clear...

Regards,

Olivier

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources