February 4, 2014 at 6:19 am
Hi,
Can anyone help me with this problem? I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all. What am I doing wrong?
Regards,
Helmi
selectdistinct ProductionHeader.ProdHeaderOrdNr,
ProductionHeader.PartCode,
ProductionHeader.Description,
ProductionHeader.Qty,
(select max (ProdStatusLog.ProdStatusCode)
from ProdStatusLog
where ProdStatusLog.ProdHeaderDossierCode = ProductionHeader.ProdHeaderDossierCode) as N'Status',
(select max (ProdStatusLog.LastUpdatedOn)
from ProdStatusLog
where ProdStatusLog.ProdHeaderDossierCode = ProductionHeader.ProdHeaderDossierCode and
ProdStatusLog.LastUpdatedOn <= dateadd(dd, -14, getdate())) as N'Uitgifte',
ProductionHeader.LastUpdatedBy,
(select (CallRegistration.Info)
from CallRegistration, ProductionHeaderCall
where CallRegistration.CallNr = ProductionHeaderCall.CallNr and
ProductionHeaderCall.ProdHeaderDossierCode = ProductionHeader.ProdHeaderDossierCode and
CallRegistration.CallTypeCode = 'P01') as N'Opmerking'
from ProductionHeader,
ProdStatusLog
whereProductionHeader.ProdHeaderDossierCode = ProdStatusLog.ProdHeaderDossierCode and
ProductionHeader.ProdStatusCode <> '90' and
ProductionHeader.ProdStatusCode <> '15' and
ProductionHeader.ProdStatusCode <> '10' and
ProductionHeader.ProdHeaderType = 2
order by N'Uitgifte'
February 4, 2014 at 6:57 am
You could avoid the null values by not selecting them.
I wonder why do you use Cartesian product in the "from" part? Do you need to?
Can you try replaceing your "from" part with the following:
fromProductionHeader as t1
join ProdStatusLog as t2 on ((t1.ProdHeaderDossierCode = t2.ProdHeaderDossierCode) and (t2.LastUpdatedOn is not null))
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 4, 2014 at 7:17 am
Here's your query rewritten to replace old-style joins and with table aliases to reduce noise:
selectdistinct
ph.ProdHeaderOrdNr,
ph.PartCode,
ph.[Description],
ph.Qty,
[Status] = (
select max(psl.ProdStatusCode)
from ProdStatusLog psl
where psl.ProdHeaderDossierCode = ph.ProdHeaderDossierCode),
[Uitgifte] = (
select max(psl.LastUpdatedOn)
from ProdStatusLog psl
where psl.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
and psl.LastUpdatedOn <= dateadd(dd, -14, getdate())),
ProductionHeader.LastUpdatedBy,
Opmerking = (
select cr.Info
from CallRegistration cr
INNER JOIN ProductionHeaderCall phc ON cr.CallNr = phc.CallNr
WHERE phc.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
and cr.CallTypeCode = 'P01')
from ProductionHeader ph
INNER JOIN ProdStatusLog l ON l.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
where
ph.ProdStatusCode NOT IN ('90', '15', '10')
ph.ProdHeaderType = 2
order by Uitgifte
You will get null values for [Uitgifte] if table [ProdStatusLog] hasn't been updated in the last 14 days. You have two choices - to remove rows where [Uitgifte] is null, or to replace null values of [Uitgifte] with something else. It's not clear what you want to do.
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
February 4, 2014 at 7:21 am
Yes I tried not to select NULL-values, but I think I explained the problem wrong. The column ProdStatusLog.LastUpdatedOn doesn't contain NULL values, but when I execute the statement, all values in the column N'Uitgifte' that have a LastUpdatedOn value less than 14 days older as today are shown as: NULL. But they don't has to be shown at all. I tried to exclude them with is not null, but it didn't work.
February 4, 2014 at 7:28 am
helmi (2/4/2014)
Yes I tried not to select NULL-values, but I think I explained the problem wrong. The column ProdStatusLog.LastUpdatedOn doesn't contain NULL values, but when I execute the statement, all values in the column N'Uitgifte' that have a LastUpdatedOn value less than 14 days older as today are shown as: NULL. But they don't has to be shown at all. I tried to exclude them with is not null, but it didn't work.
If you don't have rows satisfying the condition
and psl.LastUpdatedOn <= dateadd(dd, -14, getdate())
you'll see nulls
Can you add this condition into the "from" part for more filtered joining.
Igor Micev,My blog: www.igormicev.com
February 4, 2014 at 7:31 am
Eliminating rows with a null value could play havoc with your [Status] values. Try this to see what I mean:
selectdistinct
ph.ProdHeaderOrdNr,
ph.PartCode,
ph.[Description],
ph.Qty,
[Status] = (
select max(psl.ProdStatusCode)
from ProdStatusLog psl
where psl.ProdHeaderDossierCode = ph.ProdHeaderDossierCode),
[Uitgifte] = (
select max(psl.LastUpdatedOn)
from ProdStatusLog psl
where psl.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
and psl.LastUpdatedOn <= dateadd(dd, -14, getdate())),
ProductionHeader.LastUpdatedBy,
Opmerking = (
select cr.Info
from CallRegistration cr
INNER JOIN ProductionHeaderCall phc ON cr.CallNr = phc.CallNr
WHERE phc.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
and cr.CallTypeCode = 'P01')
from ProductionHeader ph
INNER JOIN ProdStatusLog l ON l.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
AND l.LastUpdatedOn <= dateadd(dd, -14, getdate()))
where
ph.ProdStatusCode NOT IN ('90', '15', '10')
ph.ProdHeaderType = 2
order by Uitgifte
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
February 4, 2014 at 7:49 am
Thanks people. With the help of your comments I solved this. The code you showed me is much more readable than my own way, I will definitely use this for all my scripting. As for the solution: while studying your comments and trying it out, I realized the [Status] part was obsolete in the script. Here is the actually working version:
selectdistinct
ph.ProdHeaderOrdNr,
ph.PartCode,
ph.[Description],
ph.Qty,
ph.ProdStatusCode,
[Uitgifte] = (
select max(psl.LastUpdatedOn)
from ProdStatusLog psl
where psl.ProdHeaderDossierCode = ph.ProdHeaderDossierCode),
ph.LastUpdatedBy,
Opmerking = (
select cr.Info
from CallRegistration cr
INNER JOIN ProductionHeaderCall phc ON cr.CallNr = phc.CallNr
WHERE phc.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
and cr.CallTypeCode = 'P01')
from ProductionHeader ph
INNER JOIN ProdStatusLog l ON l.ProdHeaderDossierCode = ph.ProdHeaderDossierCode
AND l.LastUpdatedOn <= dateadd(dd, -14, getdate())
where
ph.ProdStatusCode NOT IN ('90', '15', '10') and
ph.ProdHeaderType = 2
order by Uitgifte
February 5, 2014 at 4:57 am
Nice work, Helmi. The best possible outcome here is when the OP - that's you - figures out the problem themselves given a few pointers.
You may wish to examine your WHERE clause:
l.LastUpdatedOn <= dateadd(dd, -14, getdate())
If LastUpdatedOn is a datetime data type with a populated time component, the results may not be what you expect - because dateadd(dd, -14, getdate()) will also return a datetime.
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
February 6, 2014 at 5:59 am
Thanks for your comments. LastUpdatedOn is not a datetime field, but I know what you mean. In other scripts with dateadd functions I noticed this gives problems with datetime fields. In that cases I solved it by using convert functions on the datetime field.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply