Re: Missing rows in stored procedure results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 18:50:34 -0500

James,

  While I haven't tried to reproduce your problem, I'll make some
suggestions:

First, when specifying dates as strings, either use one of the two safe
SQL Server date formats (safe in all language and dateformat settings):

'20031124 18:00:00'
'2003-11-24T18:00:00' -- not safe without the T

or do the conversion from the supplied string format to smalldatetime
explicitly, using CONVERT with the appropriate code:

convert(smalldatetime, '02/03/2004', 101)

While date formats without zero padding ('2/3/2004') may work also, that
format is not in the documentation on CONVERT, so why risk it? Perhaps
somewhere in what the optimizer does a comparison that should be made on
dates is being made on the strings, or else somewhere there is a
different dateformat setting. If so, this may fix the problem. I'm
sure the prospect of changing the check constraint definition in every
table of your partitioned view is not welcome, so while I think you
should, you might try changing the format just for the parameters first.

Another suggestion: is this equivalent to what you want, and maybe simpler?

select strSAN, strUPC, SUM(intQty) as intQty from (

select trans.strSAN, upc1.strUPC AS strUPC, SUM(trans.intQty) AS intQty
from vwTransactions trans
LEFT JOIN tblUPCConversion upc1
-- left join and first AND condition below may not be needed if it is
-- never the case that upc1.strUPC is null when upc1.strCode is not null
ON trans.strSKU = upc1.strCode
JOIN Database2..StoreList stores
ON stores.lngStoreID = trans.lngStoreID
WHERE dtTran BETWEEN @dtWeekStart AND @dtWeekEnd
AND upc1.strUPC IS NOT NULL
AND dtImport < @dtImportEnd
GROUP BY trans.strSAN, upc1.strUPC

UNION ALL

select trans.strSAN, trans.strSKU AS strUPC, SUM(trans.intQty) AS intQty
from vwTransactions trans
JOIN tblUPCConversion upc2
ON trans.strSKU = upc2.strUPC
JOIN Database2..StoreList stores
ON stores.lngStoreID = trans.lngStoreID
WHERE dtTran BETWEEN @dtWeekStart AND @dtWeekEnd
AND NOT EXISTS (
  select * from tblUPCConversion upc1
  where upc1.strCode = trans.strSKU
)
AND dtImport < @dtImportEnd
GROUP BY trans.strSAN, trans.strSKU

) T
group by strSAN, strUPC

It seems like you don't need to join against the conversion table
twice. I think you are trying to select only transactions where
trans.strSKU matches with some upc.strCode value, or if not, with some
upc.strUPC value. In either case, you retrieve the upc.strUPC value,
which equals trans.strSKU if it came from your second-choice match.
That can be done in one non-union query with one join, but I pulled it
apart to avoid an OR from the WHERE clause (which can slow things
down). It can be simpler yet if what I wondered about in the comment
in the query is true.

I am making various assumptions about what can be NULL, what's unique,
etc., but burying DISTINCT in a subquery just somehow looks like it
could cause problems.

Also, I would be sure the database settings for dateformat, language,
ansi_nulls, collation, etc., are all identical for the databases
containing the tables of the partitioned view.

Finally, a remote possibility is a problem if vwTransactions adds a
constant computed column to any of the tables it selects from,
especially if it's a column referred to in the query - there at least
used to be a bug that could come up in this situation.

If none of this points you in the right direction, can you say a bit
more about the problem? I assume you mean you get different results
even if you change nothing at all, but are these active tables, where
there could be locks or changes taking place while you are running the
queries?

SK

James Loesch wrote:

>Ok, here's the basic information. I cleaned some frivolous stuff (didn't change the behavior) and changed some of the names so that they would make more sense.
>
>----------------------------------------------------------------------------------------------------------
>
>-- vwTransactions is a partitioned view on tables with the following structure:
>CREATE TABLE tblTran_200402 (
> lngStoreTranImportID INTEGER NOT NULL,
> dtTran SMALLDATETIME NOT NULL,
> lngSAProductID INTEGER NULL,
> lngPPCProdID INTEGER NULL,
> lngStoreCustomerID INTEGER NULL,
> strSAN CHAR (7) NOT NULL,
> lngStoreID INTEGER NOT NULL,
> intItemID INTEGER NULL,
> strRecType CHAR (3) NULL,
> strRecNum VARCHAR (15) NOT NULL,
> strSKU VARCHAR (13) NULL,
> intQty SMALLINT NOT NULL,
> ccyUnitPrice SMALLMONEY NULL,
> dtImport SMALLDATETIME NOT NULL,
> strCustCode2 VARCHAR (3) NULL,
> strPromoCode VARCHAR (5) NULL,
> strDiscType CHAR (1) NULL,
> strClerk VARCHAR (10) NULL,
> strTendType VARCHAR (5) NULL,
> strUniqueIdentifier VARCHAR (20) NULL,
> strDepartment VARCHAR (5) NULL,
> sngDiscAmt REAL NULL,
> lngFileID INTEGER NULL,
> strCustCode1 VARCHAR (15) NULL,
> ynDoNotUse BIT NULL,
> CONSTRAINT PK_Tran200402_ID_dtTran PRIMARY KEY NONCLUSTERED
> (
> lngStoreTranImportID,
> dtTran
> ) WITH FILLFACTOR = 95,
> CONSTRAINT CK_Date_200402 CHECK (dtTran >= '2/1/2004' AND dtTran < '3/1/2004')
>)
>GO
>
>/* Clustered index is dtTran, and other indexes exist for lngSAProductID, strSAN, strSKU,
> lngStoreID, and others. Partitioned view currently references 72 tables, each with
> 1 - 3 million rows. */
>
>
>CREATE TABLE tblUPCConversion (
> strCode VARCHAR (12) NOT NULL ,
> strUPC CHAR (12) NOT NULL
>)
>GO
>
>
>/* The StoreList is a view in another database that returns a list of store IDs and SAN
> codes for stores that are to be included in this report. That view, I discovered,
> references 2 tables, one of which is aliased 3 times in the view. */
>
>
>/*
>DECLARE @dtWeekStart SMALLDATETIME, @dtWeekEnd SMALLDATETIME, @dtImportEnd SMALLDATETIME
>SET @dtWeekStart = '2/16/04 12:00AM'
>SET @dtWeekEnd = '2/22/04 12:00AM'
>SET @dtImportEnd = '2/23/04 8:30 AM'
>--*/
>--/*
>CREATE PROCEDURE spTran_Generate @dtWeekStart SMALLDATETIME, @dtWeekEnd SMALLDATETIME,
> @dtImportEnd SMALLDATETIME AS
>--*/
>BEGIN
> SELECT trans.strSAN, COALESCE(upc1.strUPC, upc2.strUPC) AS strUPC, SUM(trans.intQty) AS intQty
> FROM vwTransactions trans
> LEFT JOIN tblUPCConversion upc1
> ON trans.strSKU = upc1.strCode
> LEFT JOIN (SELECT DISTINCT strUPC FROM tblUPCConversion) upc2
> ON trans.strSKU = upc2.strUPC
> JOIN Database2..StoreList stores
> ON stores.lngStoreID = trans.lngStoreID
> WHERE dtTran BETWEEN @dtWeekStart AND @dtWeekEnd
> AND (upc1.strUPC IS NOT NULL OR upc2.strUPC IS NOT NULL)
> AND dtImport < @dtImportEnd
> GROUP BY trans.strSAN, COALESCE(upc1.strUPC, upc2.strUPC)
>END
>GO
>
>-- What I finally got to work consistently is the following:
>/*
>DECLARE @dtWeekStart SMALLDATETIME, @dtWeekEnd SMALLDATETIME, @dtImportEnd SMALLDATETIME
>SET @dtWeekStart = '2/16/04 12:00AM'
>SET @dtWeekEnd = '2/22/04 12:00AM'
>SET @dtImportEnd = '2/23/04 8:30 AM'
>--*/
>--/*
>ALTER PROCEDURE spTran_Generate @dtWeekStart SMALLDATETIME, @dtWeekEnd SMALLDATETIME,
> @dtImportEnd SMALLDATETIME AS
>--*/
>BEGIN
> SELECT lngStoreID
> INTO #tblStoreList
> FROM Database2..StoreList
>
> SELECT strCode, strUPC
> INTO #tblSKUList
> FROM tblUPCConversion
> INSERT INTO #tblSKUList
> SELECT strUPC, strUPC
> FROM tblUPCConversion
> WHERE strUPC NOT IN (SELECT t2.strCode FROM #tblSKUList t2)
>
> SELECT trans.strSAN, upc.strUPC, SUM(trans.intQty) AS intQty
> FROM vwTransactions trans
> JOIN #tblSKUList upc
> ON trans.strSKU = skus.strCode
> JOIN #tblStoreList storeList
> ON storeList.lngStoreID = trans.lngStoreID
> WHERE trans.dtTran BETWEEN @dtWeekStart AND @dtWeekEnd
> AND trans.dtImport < @dtImportEnd
> GROUP BY trans.strSAN, upc.strUPC
>END
>
>
>

Relevant Pages

  • Re: Convert MDB to ADP
    ... What I would suggest, then, is to use a dynamic record source for your form. ... > strings rptCtry() ... Is it possibel to find any function in SQL server to replace Format, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Convert MDB to ADP
    ... > What I would suggest, then, is to use a dynamic record source for your ... >> generate excel reports. ... >> strings rptCtry() ... >>> There's nothing in SQL Server that directly replace the Format() ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Is there a "select langauge" module?
    ... > number format, date format, time corrections, gender, pluralization, ... > screen space allocated for strings, text directionality, keyboard ... The MessageFormat class is like a very souped-up sprintf ...
    (comp.lang.perl.misc)
  • new lame effort, self-compressed bin-xml...
    ... hell, I use xml, partly because it ... (likewise, my format supports namespaces, which is imo a plus). ... for the most part strings are merged via mru caching. ...
    (comp.compression)