SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Big problem with empty (NULL?) Fields...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olivierb
Starting Member

6 Posts

Posted - 02/19/2014 :  10:14:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4353 Posts

Posted - 02/19/2014 :  15:28:49  Show Profile  Reply with Quote
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?
Go to Top of Page

olivierb
Starting Member

6 Posts

Posted - 02/20/2014 :  06:12:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000