Table Variable Filtering - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Table Variable Filtering

Author

Topic

rosshughes
Starting Member

16 Posts

Posted - 2011-04-12 : 16:46:31

Hi All,

I am getting strange results filtering my table variable.

I have three records with a quotenum of 2440 that should get returned.

When I try this:

Declare @QuotingDataSet Table
( -- define columns -- )
Insert into @QuotingDataSet
( -- SELECT Statement including next line -- )
WHERE quotenum = 2440 -- (filtering on INSERT)
SELECT * FROM @QuotingDataSet -- (retrieving ALL)

I get 3 records as it should.

When I try this:

Declare @QuotingDataSet Table
( -- define columns -- )
Insert into @QuotingDataSet
( -- SELECT Statement -- ) -- (not filtering on INSERT)

SELECT * FROM @QuotingDataSet WHERE quotenum = 2440 -- (retrieve filtered only)

I only get 2 records.

Any idea why this may be? The quotenum field is defined as integer.

Thanks,
Ross

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-12 : 17:14:40

We're going to need to see the full code as what you've got above would work. I suspect it's just a data type issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rosshughes
Starting Member

16 Posts

Posted - 2011-04-12 : 18:10:49

Thanks, here it is:

Declare @QuotingDataSet Table
(
counter int identity(1,1) PRIMARY KEY,
Company varchar(10),
QuoteNum int,
datereceived smalldatetime,
EntryDate smalldatetime,
expirationdate smalldatetime,
datequoted smalldatetime,
quoted bit,
headordered bit,
salespersoncode varchar(10),
salespersonname varchar (30),
groupcode varchar(20),
groupdescription varchar(50),
Division varchar(50),
custid varchar(20),
CustomerName varchar(100),
quoteline int,
dtlordered bit,
partnum varchar(50),
linedesc varchar(200),
revisionnum varchar(25),
prodcode varchar(15),
productgroup varchar(50),
partsales bit,
toolingsales bit,
qcustomerpart varchar(50),
qcustomerrevnum varchar(25),
daystoprocess int,
opcode varchar(12),
opdesc varchar(50),
rownumber int
)
--insert the base quoting data in to the temporary memory table
Insert into @QuotingDataSet
SELECT * FROM
(SELECT qt.company, qt.quotenum, qt.date01 As datereceived, qt.entrydate, qt.expirationdate,
qt.datequoted, qt.quoted, qt.ordered AS headordered, qsalesrp.salesrepcode, qsalesrp.name As SalesName,
(CASE WHEN custgrup.groupcode Is Null THEN '[No Group Code]' ELSE custgrup.groupcode END) As groupcode,
(CASE WHEN custgrup.groupdesc Is Null THEN '[No Group Desc]' ELSE custgrup.groupdesc END) As groupdesc,
(CASE WHEN SUBSTRING(qt.character01,1,3) = '' Then '[No Division]' ELSE qt.character01 END) AS Division,
customer.custid, customer.name, quotedtl.quoteline, quotedtl.ordered, quotedtl.partnum, quotedtl.linedesc, quotedtl.revisionnum,
(CASE WHEN quotedtl.prodcode = '' THEN '[No Prod Code]' ELSE quotedtl.prodcode END) As ProdCode,
(CASE WHEN prodgrup.description Is Null THEN '[No Prod Group]' ELSE prodgrup.description END) As productgroup,
prodgrup.checkbox02 As PartSales, prodgrup.checkbox03 As ToolingSales, quotedtl.xpartnum, quotedtl.xrevisionnum,
(CASE WHEN qt.date01 Is Null THEN DateDiff(day,qt.entrydate,qt.datequoted) ELSE DateDiff(day,qt.date01,qt.datequoted) END) As DaysToProcess,
(SELECT TOP 1 qs1.opcode FROM quoteopr As qs1 WHERE qt.company = qs1.company AND qt.quotenum = qs1.quotenum AND qs1.assemblyseq = 0 AND quotedtl.quoteline = qs1.quoteline ORDER BY qs1.oprseq) As OpCode,
(SELECT TOP 1 qs1.opdesc FROM quoteopr As qs1 WHERE qt.company = qs1.company AND qt.quotenum = qs1.quotenum AND qs1.assemblyseq = 0 AND quotedtl.quoteline = qs1.quoteline ORDER BY qs1.oprseq) As OpDescription,
ROW_NUMBER() OVER(PARTITION BY quotedtl.prodcode,quotedtl.partnum ORDER BY quotedtl.prodcode,quotedtl.partnum,qt.entrydate DESC) AS 'RowNumber'
FROM quotehed AS qt
INNER JOIN customer ON qt.company = customer.company AND qt.custnum = customer.custnum
INNER JOIN quotedtl ON qt.company = quotedtl.company AND qt.quotenum = quotedtl.quotenum
LEFT OUTER JOIN part ON quotedtl.company = part.company AND quotedtl.partnum = part.partnum
LEFT OUTER JOIN prodgrup ON quotedtl.company = prodgrup.company AND quotedtl.prodcode = prodgrup.prodcode
LEFT OUTER JOIN custgrup ON customer.company = custgrup.company AND customer.groupcode = custgrup.groupcode
LEFT OUTER JOIN qsalesrp ON qt.company = qsalesrp.company AND qt.quotenum = qsalesrp.quotenum
WHERE qt.company = 'LSI68332'
AND qt.quoted = 1
AND qt.entrydate >= '1/1/2010' AND qt.entrydate <= '12/31/2010' AND qt.quotenum = 2440) As QSum
--AND qt.entrydate >= @QFROMDate AND qt.entrydate <= @QTODate AND qt.quotenum = 2440) As QSum
WHERE RowNumber = 1

SELECT * FROM @QuotingDataSet-- WHERE Quotenum = 2440

The QuoteNum field is the relevant field. The above example returns two records, rather than three.

Thanks,
Ross

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-12 : 18:21:58

Show us the other query too so we can see the difference.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rosshughes
Starting Member

16 Posts

Posted - 2011-04-12 : 22:23:44

I see there is a little mistake in my pasted code. It is not double-filtered in the SP.

The code is the same. While troubleshooting I noticed that when I change the end of this code to:

...AND qt.entrydate >= '1/1/2010' AND qt.entrydate <= '12/31/2010' AND qt.quotenum = 2440) As QSum
WHERE RowNumber = 1
SELECT * FROM @QuotingDataSet

...it will return 3 records. But, when I have the end like this:

AND qt.entrydate >= '1/1/2010' AND qt.entrydate <= '12/31/2010') As QSum
WHERE RowNumber = 1
SELECT * FROM @QuotingDataSet WHERE Quotenum = 2440

...it will only return 2 records and trims one.

???

Thanks for looking,
Ross

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-13 : 10:37:56

With out any sample data it's hard to tell, but I suspect that having (or not having) the filter on QuoteNum is affecting which rows are RowNumber 1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources