Help with query variable number in where clause

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

Transact-SQL (2000)

Help with query variable number in where clause

Author

Topic

jughead1111
Starting Member

14 Posts

Posted - 2007-03-29 : 16:19:43

I have a query that I need help with. I have a query that has a dynamic number of variables in the where clause. SQL 2000 SP4. I've read just about every article I could find trying to fix this problem and cannot seem to get it to work.

The problem is that the where clause is an unknown number of variables.

This is unknown when the procedure runs.

(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K090-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K095-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K100-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K101-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K102-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K103-ST_FLUTE') OR
(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = 'K104-ST_FLUTE')

I attacked it this way -

CREATE PROCEDURE spJCTSelectIncomingDataByWorkCenter
-- Add the parameters for the stored procedure here
@OpGroup varchar(50) = '',
@StartDate DateTime = GetDate,
@EndDate DateTime = GetDate

AS
BEGIN
SET NOCOUNT ON;

DECLARE @i int --Holds the max number of Operations
DECLARE @e int --Holds the count we are on in the loop
DECLARE @WhereClause varchar(4000)--Holds the string we are using as the Where Clause
DECLARE @sql varchar(8000)

SET @e = 1
SET @WhereClause = ''

--Create Temp Table to hold our values
CREATE TABLE #Operations
(
RowNumber int IDENTITY(1,1),
OperationID varchar(15)
)

INSERT #Operations (OperationID)
SELECT OperationID
FROM dbo.JCTOperationGroup
WHERE (OpGroup = @OpGroup)
SET @i = MAX(RowNumber)

WHILE @e <= @i
BEGIN
@WhereClause = @WhereClause + '(dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE = ' + SELECT OperationID FROM #Operations WHERE RowNumber = @e + ') OR '
SET @e = @e + 1
END

@sql = 'SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO, dbo.CUSTOMER_ORDER.CUSTOMER_ID,
dbo.CUST_ORDER_LINE.PART_ID, dbo.PART.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY, dbo.OPERATION.OPERATION_TYPE,
dbo.OPERATION.SETUP_HRS, dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN
dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID ON dbo.WORK_ORDER.BASE_ID = dbo.PART.ID AND
dbo.WORK_ORDER.LOT_ID = dbo.PART.ENGINEERING_MSTR
WHERE 1 = 1 AND '

@sql = @sql + @WhereClause

But this did not work. Any help would be much appreciated.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-29 : 16:35:20

Here's one way:

SELECT ...
FROM...
WHERE (CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (OPERATION.OPERATION_TYPE IN @values)

Send in a concatenated list of values from your app. Parse the values in the proc and use it in the @values.

************************
Life is short. Enjoy it.
************************

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-29 : 16:46:19

Parse the values in the proc and use it in the @values.
dinakar - how do you do this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-29 : 17:59:45

quote:
Originally posted by snSQL

Parse the values in the proc and use it in the @values.
dinakar - how do you do this?

Something like this:
[url]http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx[/url]

************************
Life is short. Enjoy it.
************************

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 00:23:33

That seems pretty different to what you suggested here though, you said to parse the input and use it in @values. I don't see what you mean by that.

If you're passing in a comma delimitted list of the values you'd be better off using the list in a dynamic SQL statement? In the example you pointed to, you are parsing the list and then running the query once for each value, not using the list with the IN operator at all?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-30 : 12:44:21

Sorry I havent been descriptive enough in my earlier replies..
Here's some code sample:


DECLARE @values VARCHAR(500)
SET @values = '''K090-ST_FLUTE'', ''K095-ST_FLUTE'', ''K100-ST_FLUTE'', ''K101-ST_FLUTE'', ''K102-ST_FLUTE'', ''K103-ST_FLUTE'', ''K104-ST_FLUTE'''

DECLARE @t TABLE (col1 VARCHAR(100))

DECLARE @separator_position INT 
	,@array_value VARCHAR(1000)
	,@Array VARCHAR(1000)
	,@separator CHAR(1)

SELECT @Array = @values
	,@separator =','

SET @array = @array + @separator

WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
	BEGIN
		SELECT  @separator_position = PATINDEX('%' + @separator + '%',@array)
        SELECT  @array_value = LEFT(@array, @separator_position - 1)
		INSERT INTO @t SELECT @array_value
        
        --SELECT  Array_Value = @array_value
        SELECT  @array = STUFF(@array, 1, @separator_position, '')
	END

SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO, dbo.CUSTOMER_ORDER.CUSTOMER_ID, 
	dbo.CUST_ORDER_LINE.PART_ID, dbo.PART.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY, dbo.OPERATION.OPERATION_TYPE, 
	dbo.OPERATION.SETUP_HRS, dbo.OPERATION.RUN
FROM dbo.OPERATION 
INNER JOIN dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE 
	AND dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID 
	AND dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID 
INNER JOIN dbo.CUSTOMER_ORDER -- <----------- MISSING the join condition here 
INNER JOIN dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID 
INNER JOIN dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID ON dbo.WORK_ORDER.BASE_ID = dbo.PART.ID 
	AND dbo.WORK_ORDER.LOT_ID = dbo.PART.ENGINEERING_MSTR
WHERE CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate 
	AND OPERATION.OPERATION_TYPE IN ( SELECT col1 FROM @t)

************************
Life is short. Enjoy it.
************************

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 12:59:31

Cool, thanks dinakar, that makes much more sense than OPERATION.OPERATION_TYPE IN @values

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-30 : 13:10:32

Sure. Parsing the delimited list here is the key. So I was hoping the OP might be able to figure out the rest.
I generally try to avoid dynamic SQL if I can.

************************
Life is short. Enjoy it.
************************

jughead1111
Starting Member

14 Posts

Posted - 2007-03-30 : 13:19:45

Thanks Dinakar...I did not know that you could use the IN Clause like that or it would have been solved long ago.

I guess I was not clear enough, my list was already in a table so I did not have to parse the list. I was wondering why I needed to take my table data to a csv list and then back to a table. But I see the benefit if your list is coming from the client.

Also, during the process I learned a great deal. The final solution is below if anyone is interested.

ALTER PROCEDURE [dbo].[spJCTSelectIncomingDataByWorkCenter]
-- Add the parameters for the stored procedure here
@OpGroup varchar(50) = '',
@StartDate DateTime = GetDate,
@EndDate DateTime = GetDate

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/*DECLARE @values varchar(2000)
--SELECT @values = COALESCE(@values, '') + OperationID + ','
SELECT @values = COALESCE(@values, '') + CHAR(39) + OperationID + CHAR(39) + ','
FROM dbo.JCTOperationGroup
WHERE (OpGroup = @OpGroup)

SET @values = LEFT(@values, LEN(@values) - 1)*/

--Create Temp Table to hold our values
DECLARE @t TABLE (OperationID varchar(15))
INSERT @t (OperationID)
SELECT OperationID
FROM dbo.JCTOperationGroup
WHERE (OpGroup = @OpGroup)

--Qry Level 1
SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO,
dbo.CUSTOMER_ORDER.CUSTOMER_ID,dbo.CUST_ORDER_LINE.PART_ID, dbo.PART.DESCRIPTION,
dbo.CUST_ORDER_LINE.ORDER_QTY,dbo.OPERATION.OPERATION_TYPE,dbo.OPERATION.SETUP_HRS,
dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID
INNER JOIN dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID ON dbo.WORK_ORDER.BASE_ID =
dbo.PART.ID AND dbo.WORK_ORDER.LOT_ID = dbo.PART.ENGINEERING_MSTR
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE IN (SELECT OperationID FROM @t))
UNION ALL
--Qry Level 2
SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO,
dbo.CUSTOMER_ORDER.CUSTOMER_ID,
dbo.CUST_ORDER_LINE.PART_ID, PART_1.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY,
dbo.OPERATION.OPERATION_TYPE,
dbo.OPERATION.SETUP_HRS, dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.PART INNER JOIN
dbo.REQUIREMENT INNER JOIN
dbo.PART AS PART_1 INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID ON
PART_1.ID = dbo.CUST_ORDER_LINE.PART_ID ON dbo.REQUIREMENT.WORKORDER_BASE_ID =
PART_1.ID AND
dbo.REQUIREMENT.WORKORDER_LOT_ID = PART_1.ENGINEERING_MSTR ON dbo.PART.ID =
dbo.REQUIREMENT.PART_ID ON
dbo.WORK_ORDER.BASE_ID = dbo.PART.ID AND dbo.WORK_ORDER.LOT_ID =
dbo.PART.ENGINEERING_MSTR
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE IN (SELECT OperationID FROM @t))
UNION ALL
--Qry Level 3
SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO, dbo.CUSTOMER_ORDER.CUSTOMER_ID,
dbo.CUST_ORDER_LINE.PART_ID,
PART_1.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY, dbo.OPERATION.OPERATION_TYPE,
dbo.OPERATION.SETUP_HRS,
dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.PART ON dbo.WORK_ORDER.BASE_ID = dbo.PART.ID AND dbo.WORK_ORDER.LOT_ID =
dbo.PART.ENGINEERING_MSTR INNER JOIN
dbo.PART AS PART_2 INNER JOIN
dbo.REQUIREMENT AS REQUIREMENT_1 ON PART_2.ID = REQUIREMENT_1.WORKORDER_BASE_ID AND
PART_2.ENGINEERING_MSTR = REQUIREMENT_1.WORKORDER_LOT_ID INNER JOIN
dbo.REQUIREMENT INNER JOIN
dbo.PART AS PART_1 INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID ON
PART_1.ID = dbo.CUST_ORDER_LINE.PART_ID ON dbo.REQUIREMENT.WORKORDER_BASE_ID =
PART_1.ID AND
dbo.REQUIREMENT.WORKORDER_LOT_ID = PART_1.ENGINEERING_MSTR ON PART_2.ID =
dbo.REQUIREMENT.PART_ID ON
dbo.PART.ID = REQUIREMENT_1.PART_ID
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE IN (SELECT OperationID FROM @t))
UNION ALL
--Qry Level 4
SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO, dbo.CUSTOMER_ORDER.CUSTOMER_ID,
dbo.CUST_ORDER_LINE.PART_ID,
PART_1.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY, dbo.OPERATION.OPERATION_TYPE,
dbo.OPERATION.SETUP_HRS,
dbo.OPERATION.RUN
FROM dbo.PART AS PART_2 INNER JOIN
dbo.REQUIREMENT AS REQUIREMENT_1 ON PART_2.ID = REQUIREMENT_1.WORKORDER_BASE_ID AND
PART_2.ENGINEERING_MSTR = REQUIREMENT_1.WORKORDER_LOT_ID INNER JOIN
dbo.REQUIREMENT INNER JOIN
dbo.PART AS PART_1 INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID ON
PART_1.ID = dbo.CUST_ORDER_LINE.PART_ID ON dbo.REQUIREMENT.WORKORDER_BASE_ID =
PART_1.ID AND
dbo.REQUIREMENT.WORKORDER_LOT_ID = PART_1.ENGINEERING_MSTR ON PART_2.ID =
dbo.REQUIREMENT.PART_ID INNER JOIN
dbo.PART AS PART_3 ON REQUIREMENT_1.PART_ID = PART_3.ID INNER JOIN
dbo.REQUIREMENT AS REQUIREMENT_2 ON PART_3.ID = REQUIREMENT_2.WORKORDER_BASE_ID AND
PART_3.ENGINEERING_MSTR = REQUIREMENT_2.WORKORDER_LOT_ID INNER JOIN
dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.PART ON dbo.WORK_ORDER.BASE_ID = dbo.PART.ID AND dbo.WORK_ORDER.LOT_ID =
dbo.PART.ENGINEERING_MSTR ON
REQUIREMENT_2.PART_ID = dbo.PART.ID
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE IN (SELECT OperationID FROM @t))

END
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-30 : 13:32:07

Kool. Now I see that you are using UNION ALL's. Realize that it will use tempdb. Depending on how heavy this query is used you may notice tempdb contention which may or may not be an issue. You could get rid of it by doing multiple inserts into a local table variable and doing a final SELECT from it. Ofcourse a table variable might still use tempdb but this overhead would be less than the UNION ALL. I dont have any statistics/numbers to prove this but I have made these changes in some of our procs and I do see good improvement in reducing tempdb contention.

Currently your format is:


SELECT col1, col2..
FROM..
WHERE..

UNION ALL

SELECT col1, col2..
FROM..
WHERE..

UNION ALL

SELECT col1, col2..
FROM..
WHERE..

You could change it to:


DECLARE @SomeT Table (col1 ..., col2,..)

INSERT INTO @SomeT (...)
SELECT col1, col2..
FROM..
WHERE..

INSERT INTO @SomeT (...)
SELECT col1, col2..
FROM..
WHERE..


INSERT INTO @SomeT (...)
SELECT col1, col2..
FROM..
WHERE..

SELECT * FROM @SomeT

************************
Life is short. Enjoy it.
************************

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 13:40:36

Also, note that a join is usually preferable to using IN, you should usually do this

SELECT T1.*
FROM T1
INNER JOIN T2 ON T2.Key = T1.KEY
WHERE something

instead of this

SELECT T1.*
FROM T1
WHERE T1.Key IN (SELECT T2.Key FROM T2 WHERE something)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-30 : 14:28:38

quote:
Originally posted by snSQL

Also, note that a join is usually preferable to using IN, you should usually do this

SELECT T1.*
FROM T1
INNER JOIN T2 ON T2.Key = T1.KEY
WHERE something

instead of this

SELECT T1.*
FROM T1
WHERE T1.Key IN (SELECT T2.Key FROM T2 WHERE something)

This is kind of tricky. If the table in the subquery is a huge one (millions of rows) and the results being queries is small (few tens) I think an IN would be better as opposed to joining the table. What do you think? I definetely agree IN is a little slower.

************************
Life is short. Enjoy it.
************************

jughead1111
Starting Member

14 Posts

Posted - 2007-03-30 : 15:09:44

I took your advice on the Temp table and converted it.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 16:14:37

quote:
I think an IN would be better as opposed to joining the table.

That mostly depends on the query and the indexes, if the indexes are right and the query can be optimized then it won't make any difference how big the tables are, the join will only be based on the rows that are found by the index.

jughead1111
Starting Member

14 Posts

Posted - 2007-03-30 : 16:19:38

Still having an issue with this query. It Looks like the query is only pulling records for the last value in the list.

DECLARE @OpGroup varchar(50)
SET @OpGroup = 'Straight Flute'
DECLARE @StartDate DateTime
SET @StartDate = CONVERT(DATETIME, '2007-01-01 00:00:00', 102)
DECLARE @EndDate DateTime
SET @EndDate = CONVERT(DATETIME, '2007-01-03 00:00:00', 102)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @t TABLE (OperationID varchar(15))
INSERT @t (OperationID)
SELECT OperationID
FROM dbo.JCTOperationGroup
WHERE (OpGroup = @OpGroup)

Select * from @t

ResultSet --
K090-ST-FLUTE
K095-ST-FLUTE
K100-ST FLUTE
K101-ST FLUTE
K102-ST FLUTE
K103-ST FLUTE
K104-ST FLUTE
K105-ST-FLUTE

DECLARE @Temp TABLE (
ORDER_DATE datetime,
ID varchar(15),
LINE_NO smallint,
CUSTOMER_ID varchar(15),
PART_ID varchar(30),
DESCRIPTION varchar(40),
ORDER_QTY decimal(14,4),
OPERATION_TYPE varchar(15),
SETUP_HRS decimal(8,3),
RUN decimal(15,8))

INSERT @Temp (
ORDER_DATE,
ID,
LINE_NO,
CUSTOMER_ID,
PART_ID,
DESCRIPTION,
ORDER_QTY,
OPERATION_TYPE,
SETUP_HRS,
RUN)
--Qry Level 1
SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO,
dbo.CUSTOMER_ORDER.CUSTOMER_ID,dbo.CUST_ORDER_LINE.PART_ID, dbo.PART.DESCRIPTION,
dbo.CUST_ORDER_LINE.ORDER_QTY,dbo.OPERATION.OPERATION_TYPE,dbo.OPERATION.SETUP_HRS,
dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID
INNER JOIN dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID ON dbo.WORK_ORDER.BASE_ID =
dbo.PART.ID AND dbo.WORK_ORDER.LOT_ID = dbo.PART.ENGINEERING_MSTR
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN @StartDate AND @EndDate) AND (dbo.OPERATION.OPERATION_TYPE IN (SELECT OperationID FROM @t))

SELECT * from @Temp

ResultSet -
2007-01-02 00:00:00.000 73018 1 CAT G836629Q-Z M12 X 1.75 D6 4FL P W/SLOT 12.0000 K105-ST-FLUTE 0.300 100.00000000

Its is only returning rows from one of the values in @t. I don't understand why? When I put the values in the query like this it works correctly. Any suggestions?

SELECT dbo.CUSTOMER_ORDER.ORDER_DATE, dbo.CUSTOMER_ORDER.ID, dbo.CUST_ORDER_LINE.LINE_NO, dbo.CUSTOMER_ORDER.CUSTOMER_ID,
dbo.CUST_ORDER_LINE.PART_ID, dbo.PART.DESCRIPTION, dbo.CUST_ORDER_LINE.ORDER_QTY, dbo.OPERATION.OPERATION_TYPE,
dbo.OPERATION.SETUP_HRS, dbo.OPERATION.RUN
FROM dbo.OPERATION INNER JOIN
dbo.WORK_ORDER ON dbo.OPERATION.WORKORDER_TYPE = dbo.WORK_ORDER.TYPE AND
dbo.OPERATION.WORKORDER_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.OPERATION.WORKORDER_LOT_ID = dbo.WORK_ORDER.LOT_ID INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN
dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID ON dbo.WORK_ORDER.BASE_ID = dbo.PART.ID AND
dbo.WORK_ORDER.LOT_ID = dbo.PART.ENGINEERING_MSTR
WHERE (dbo.CUSTOMER_ORDER.ORDER_DATE BETWEEN CONVERT(DATETIME, '2007-01-01 00:00:00', 102) AND CONVERT(DATETIME,
'2007-01-03 00:00:00', 102)) AND (dbo.OPERATION.OPERATION_TYPE IN ('K090-ST_FLUTE', 'K095-ST_FLUTE', 'K100-ST_FLUTE', 'K101-ST_FLUTE',
'K102-ST_FLUTE', 'K103-ST_FLUTE', 'K104-ST_FLUTE'))

ResultSet-
1/2/2007 12:00:00 AM 72998 1 CAMERONMOR R834784T M16 X 1.5 H6 6FL SPPT P 33.0000 K095-ST_FLUTE 0.800 30.00000000
1/2/2007 12:00:00 AM S49366 10 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 11 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 12 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 7 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 8 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 9 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 4 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 5 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 6 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 1 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 2 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM S49366 3 TRINITYTX R835760Q 1 1/8-7 H8 4FL P 30.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM 72997 1 LEWIS R838684T 5/8-11 H3 4FL S/B O/H 1.0000 K095-ST_FLUTE 0.800 25.00000000
1/2/2007 12:00:00 AM 73012 1 CAT R840975T-Z 3/8-16 H3 3FL S/B O/H 12.0000 K095-ST_FLUTE 0.800 60.00000000
1/2/2007 12:00:00 AM 73000 1 LEWIS R842045Q 5/16-18 H3 4FL B 6OAL PULLEY 10.0000 K101-ST_FLUTE 0.800 34.00000000
1/3/2007 12:00:00 AM 73046 1 CAT R844379T-Z M10 X 1.5 D3 3FL SPPT P W/NECK 6 OAL 20.0000 K101-ST_FLUTE 0.800 75.00000000
1/2/2007 12:00:00 AM 73010 1 CAT R855124Q 1-14 H1 4FL PLUG 7 3/8 OAL 1.0000 K104-ST_FLUTE 0.800 15.00000000
1/2/2007 12:00:00 AM 73013 1 CAT R855624Q 3/8-16 H3 3FL 4 OAL PLUG 6.0000 K095-ST_FLUTE 0.800 35.00000000
1/3/2007 12:00:00 AM 73057 1 SPS R858379T 3/8-24 H3 3FL SPPT BENT SHK NIB 11.0000 K095-ST_FLUTE 1.000 20.00000000
1/3/2007 12:00:00 AM 73057 1 SPS R858379T 3/8-24 H3 3FL SPPT BENT SHK NIB 11.0000 K095-ST_FLUTE 1.000 20.00000000
1/3/2007 12:00:00 AM 73042 1 CAT R860544A 3/8-16 H3 4FL S/B VHP-CO O-H 48.0000 K095-ST_FLUTE 0.800 35.00000000
1/2/2007 12:00:00 AM 72996 1 CARMACH R862311T 1 1/4-7 H10 4FL SPPT VHP PLUG 6.0000 K104-ST_FLUTE 0.800 18.00000000
1/3/2007 12:00:00 AM 73051 1 RIVNUT R862313Y M10 X 1.5 D11B 4LG JARFLO 3.94 DIN 6.0000 K102-ST_FLUTE 1.000 60.00000000
1/3/2007 12:00:00 AM 73051 2 RIVNUT R862313Y M10 X 1.5 D11B 4LG JARFLO 3.94 DIN 6.0000 K102-ST_FLUTE 1.000 60.00000000
1/3/2007 12:00:00 AM 73053 1 RIVNUT R862314T M6 X 1.0 D6B 4LG JARFLO 3 OAL 6.0000 K102-ST_FLUTE 1.000 80.00000000
1/2/2007 12:00:00 AM 73021 1 AMERTLNE G855282J 7/16-14 H7 3FL SPPT P JARTUFF 144.0000 K095-ST_FLUTE 1.550 45.00000000

END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources