SQL Binary Logic tangle, what am I not seeing? (1 Viewer)

[mdlueck]

Greetings,

This should be so simple. I was asked to knock out Obsolete Parts from several queries. The complicated part is that there are two things that can indicate an Obsolete Part... this application can declare the Part Obsolete, or JDE (Production ERP) may.

So for Fandango, a special value [p].[stocktypeid] = 1 is what indicates an Obsolete Part. For JDE, [jim].[stktyp] = 'O' indicates Obsolete Part.

Merely adding the Fandango restriction eliminates the query's entire result set!!! :banghead:

The test parts I am dealing with have a NULL [p].[stocktypeid] as one has not been defined yet in my test data. Which NULL <> 1, so that should not have knocked the records out of the result set.

Requesting another set of eyes. Perhaps you can see something that I am overlooking.

-- Needed for debugging
DECLARE @projid smallint;
SET @projid = 1;

SELECT DISTINCT [p].[id],
[p].[stocktypeid],
                [p].[buyercodetypeid],
                [pbc].[title] AS [buyercodetypetitle],
                [p].[cmdtycodetypeid],
                [pcc].[title] AS [cmdtycodetypetitle],
                [p].[partnumber],
                [p].[title] AS [parttitle],
                [p].[rev],
                dbo.frevconv_ConvN2A([p].[rev]) AS [revdisp]
FROM [dbo].[projects] AS [proj]
INNER JOIN [dbo].[products] AS [prod] ON [proj].[id] = [prod].[projectid]
INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid] 
INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id] 
LEFT JOIN [dbo].[partsbuyercodetype] AS [pbc] ON [p].[buyercodetypeid] = [pbc].[id]
LEFT JOIN [dbo].[partscmdtycodetype] AS [pcc] ON [p].[cmdtycodetypeid] = [pcc].[id]
LEFT JOIN [dbo].[jdeitemmaster] AS [jim] ON [p].[partnumber] = [jim].[partnumber]
LEFT JOIN [dbo].[quotes] AS [q] ON [p].[quoteid] = [q].[id]
WHERE [proj].[id] = @projid             -- All Parts associated with this Project
AND (([p].[quoteid] IS NULL)            -- No selected quote
     AND ([jim].[partnumber] IS NULL))  -- And not a production Part number
[COLOR=Red][B]AND ([p].[stocktypeid] <> 1)      -- And not Fandango Obsolete[/B]
--     OR ([jim].[stktyp] <> 'O'))   -- Or JDE Obsolete[/COLOR]
ORDER BY [p].[id];

And the result set with the "AND ([p].[stocktypeid] <> 1) " commented, in addition to the JDE LOC being commented.

id    stocktypeid    buyercodetypeid    buyercodetypetitle    cmdtycodetypeid    cmdtycodetypetitle    partnumber    parttitle    rev    revdisp
16    NULL    NULL    NULL    NULL    NULL    12435687    Testing Router Complete Flag    103    D
18    NULL    NULL    NULL    NULL    NULL    6500001020    Testing BOM Loaded Flg    100    A

 

[mdlueck]

It appears that as soon as I specify a "not value" for a not required field, suddenly it means that it becomes a required field.

So, off to do a similar counter action to restore that field's optional status which I have had to do before.

 

[WayneRyan]

Michael,

How did it run ???


Unbalanced parentheses:

WHERE [proj].[id] = @projid             -- All Parts associated with this Project


AND (([p].[quoteid] IS NULL)            -- No selected quote
    12                     1

     AND ([jim].[partnumber] IS NULL))  -- And not a production Part number
         2                          10

AND ([p].[stocktypeid] <> 1)      -- And not Fandango Obsolete
    1                      2

--     OR ([jim].[stktyp] <> 'O'))   -- Or JDE Obsolete
          3                     21

ORDER BY [p].[id];

It will run with the line commented out.

Wayne

 

[WayneRyan]

Michael,

Also, my gut instinct is that:

[p].[stocktypeid] <> 1 AND [jim].[stktyp] <> 'O'

Wayne

 

[mdlueck]

How did it run ???

It ran as posted with the one red line commented out. Yes I realized that what I posted, if you uncommented the line dealing with the JDE table, then yes the entire SQL then would have unbalanced parentheses.

My intent was to have it binary OR within parentheses, but I commented the one LOC to figure out what was wrong with the one LOC. Thus the "open patient" look to the SQL as posted.

From my SQL Server book author friend:

because you're saying to filter the result so that stocktypeid has to be equal a value other than 1. Null is unknown. in SQLspeak you can't prove that null isn't 1.

you need

AND ( [p].[stocktypeid] <> 1 OR [p].[stocktypeid] IS NULL )
​

to capture all the values you're looking for.

To the bold line I replied with ROTFLOL!!

So that is where I shall start in the morning.

 

[WayneRyan]

Michael,

How about:

Coalesce([p].[stocktypeid], 1) <> 1

Coalesce([jim].[stktyp], '0') <> 'O'

Wayne

 

[mdlueck]

Wayne, good reminder of the useful Coalesce() function.

I will not need that for the [jim] table as it must be 'O', to be considered Obsolete. The [p] table is where the NULL values can occur. So would only need one Coalesce().

 

[mdlueck]

Yesterday I received feedback from my SQL Server book author friend:

two direct test agains the field can use an index seek on each test, (super fast)

a function in the where clause forces SQL to evaluate the function for EVERY row and then perform the if test. To get every row, SQL will do a table scan to read every row and fetch it to memory. This is reeeeaaaalllllly slooooooooooowwwwwww. (actually much slower than we have space for the extra o's in an email. we're talking potentially several magnitudes of difference)


whenever possible the where clause should simply compare a direct field against a value or parameter (but not a local variable).


so, while it may look like a coalesce in this case would be better because it's one test instead of two, it really means 1 table scan vs two index seeks.

And I thought of an ugly scenario of my own...

The No ECO Released report specifically excludes production parts.

Say a current production part is being revised as part of a new product. A Fandango quote is selected for pricing, thus the Production Part check box is NOT checked on the Part Edit page.

Say that same part is Obsolete in JDE.

(chuckle)

So should it go on the report or not?

I am inclined to lean the way that since this report is excluding production parts anyway, then safe to leave production obsolete flag unchecked for this report.

In my scenario, a Fandango quote is selected to source the part.

Ooohhh…. the way I have been updating these reports is I do not care which quote is selected to source the part – JDE / Fandango. If either system has the part as Obsolete, leave it off the report. Should we tie that “skip obsolete” to what system is sourcing the pricing for the part? (If Fandango Quote and Fandango Obsolete, THEN leave the part off the report? And the reverse also true.)

I suppose "all in a day's work".

Anyway, I thought to share the advise of perform two / multiple table column checks and AVOID function calls where possible.

 

[mdlueck]

Reporting back the solution without use of COALESCE and safe for the scenario I thought of yesterday afternoon:

AND (
      CASE
        WHEN ([p].[quoteid] IS NOT NULL)             -- Fandango Quote is selected for this Part
             AND ([p].[stocktypeid] = 1) THEN 0      -- Fandango Obsolete, Do not include 
        WHEN ([p].[quoteid] IS NOT NULL)             -- Fandango Quote is selected for this Part
             AND ([p].[stocktypeid] IS NULL) THEN 1  -- Fandango Stocktype not specified, OK to include 
        WHEN ([p].[quoteid] IS NULL)                 -- No Fandango Quote selected for this Part
             AND ([jim].[partnumber] IS NOT NULL)    -- Check JDE to make sure the Part is in the ItemMaster
             AND ([jim].[stktyp] = 'O') THEN 0       -- JDE shows the Part Obsolete, Do not include
        WHEN ([p].[quoteid] IS NULL)                 -- No Fandango Quote selected for this Part
             AND ([jim].[partnumber] IS NOT NULL)    -- Check JDE to make sure the Part is in the ItemMaster
             AND ([jim].[stktyp] IS NULL) THEN 1     -- JDE stktyp not specified, OK to include 
        ELSE 1  -- All clear with at least these checks, OK to include in the result set
      END
    ) = 1

I suspect the final ELSE will never be reached, but just to be safe I left it in.