Select records if a MAX() condition is true in a s

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)

Select records if a MAX() condition is true in a s

Author

Topic

stewsterl
Starting Member

2 Posts

Posted - 2009-10-15 : 16:28:02

Please forgive my newbee SQL status..

I have the following database structure and data:

CREATE TABLE CUSTOMER_ORDER
(
ROWID INT ,
ID varchar(15) ,
CUSTOMER_ID varchar(15),
STATUS char(1),
USER_1 varchar(80),
USER_2 varchar(80)
)

CREATE TABLE DEMAND_SUPPLY_LINK
(
ROWID INT ,

SUPPLY_BASE_ID varchar(30),
DEMAND_BASE_ID varchar(30)
)

CREATE TABLE CUST_ORDER_LINE
(
ROWID INT ,
LINE_NO smallint ,
CUST_ORDER_ID varchar(15),
PART_ID varchar(30) ,
UNIT_PRICE decimal(15, 6),
TRADE_DISC_PERCENT decimal(6, 3)
)

CREATE TABLE PART
(
ROWID INT ,
ID varchar(30),
DESCRIPTION varchar(40),
PRODUCT_CODE varchar(15),
UNIT_PRICE decimal(15, 6)
)

CREATE TABLE OPERATION
(
ROWID INT ,
WORKORDER_BASE_ID varchar(30) ,
SEQUENCE_NO smallint,
STATUS CHAR(1)
)

INSERT INTO CUSTOMER_ORDER VALUES (18847, 'CO23753','NOVAE','C','14X','STAT')
INSERT INTO CUSTOMER_ORDER VALUES (31843, 'SS019712','NOVAE','C','14X','STAT')
INSERT INTO CUSTOMER_ORDER VALUES (27387, 'S025894','NOVAE','C','15X','STATUS')
INSERT INTO CUSTOMER_ORDER VALUES (27652, 'S026125','NOVAE','C','18X','STATS')

INSERT INTO DEMAND_SUPPLY_LINK VALUES (23107, '28289', 'CO23753')
INSERT INTO DEMAND_SUPPLY_LINK VALUES (34282, 'S019712', 'SS019712')
INSERT INTO DEMAND_SUPPLY_LINK VALUES (30162, 'S025894', 'S025894')
INSERT INTO DEMAND_SUPPLY_LINK VALUES (30539, 'S026125', 'S026125')

INSERT INTO CUST_ORDER_LINE VALUES (52187, 1, 'CO23753', 'ST8220TAT-B-070', 2004.000000, 25.000 )
INSERT INTO CUST_ORDER_LINE VALUES (82691, 1, 'SS019712', 'ST8218CHS-B-070', 904.000000, 25.000 )
INSERT INTO CUST_ORDER_LINE VALUES (70936, 1, 'S025894', 'ST6210HSA-B', 3104.000000, 25.000 )
INSERT INTO CUST_ORDER_LINE VALUES (71766, 1, 'S026125', 'ST7918TE-B-140', 1104.000000, 25.000 )

INSERT INTO PART VALUES (1974,'ST8220TAT-B-070', '7 x 20 Tube Top Angle Tandem- Black 7K', 'TRAILER', 2700.000000)
INSERT INTO PART VALUES (2903,'ST8218CHS-B-070', '7 x 18 Car Hauler Steel Deck 7K', 'TRAILER', 1700.000000)
INSERT INTO PART VALUES (1625,'ST6210HSA-B', '5 x 10 Utility Angle High Side ', 'TRAILER', 1200.000000)
INSERT INTO PART VALUES (3674,'ST7918TE-B-140', '7 x 18 14K Tilting Equipment Trailer', 'TRAILER', 4700.000000)

INSERT INTO OPERATION VALUES (192987, '28289',20, 'C')
INSERT INTO OPERATION VALUES (192988, '28289',30, 'C')
INSERT INTO OPERATION VALUES (192990, '28289',60, 'C')
INSERT INTO OPERATION VALUES (192991, '28289',70, 'C')
INSERT INTO OPERATION VALUES (192992, '28289',80, 'C')
INSERT INTO OPERATION VALUES (192994, '28289',100, 'C')

INSERT INTO OPERATION VALUES (204531, 'S019712',10, 'C')
INSERT INTO OPERATION VALUES (204532, 'S019712',20, 'f')
INSERT INTO OPERATION VALUES (204534, 'S019712',34, 'C')
INSERT INTO OPERATION VALUES (204538, 'S019712',70, 'f')
INSERT INTO OPERATION VALUES (204542, 'S019712',150, 'f')
INSERT INTO OPERATION VALUES (204539, 'S019712',80, 'C')
INSERT INTO OPERATION VALUES (204541, 'S019712',41, 'f')

INSERT INTO OPERATION VALUES (326611, 'S025894',10, 'f')
INSERT INTO OPERATION VALUES (326612, 'S025894',11, 'f')
INSERT INTO OPERATION VALUES (326613, 'S025894',20, 'C')
INSERT INTO OPERATION VALUES (326614, 'S025894',45, 'f')
INSERT INTO OPERATION VALUES (326615, 'S025894',36, 'f')
INSERT INTO OPERATION VALUES (326616, 'S025894',81, 'C')
INSERT INTO OPERATION VALUES (326617, 'S025894',40, 'f')
INSERT INTO OPERATION VALUES (326618, 'S025894',12, 'f')

INSERT INTO OPERATION VALUES (333850, 'S026125',12, 'f')
INSERT INTO OPERATION VALUES (333851, 'S026125',11, 'f')
INSERT INTO OPERATION VALUES (333852, 'S026125',20, 'f')
INSERT INTO OPERATION VALUES (333853, 'S026125',30, 'f')
INSERT INTO OPERATION VALUES (333854, 'S026125',25, 'f')
INSERT INTO OPERATION VALUES (333855, 'S026125',40, 'f')
INSERT INTO OPERATION VALUES (333856, 'S026125',60, 'C')

I'm trying to return only the records IF OPERATION MAX(SEQUENCE_NO)'s = 'C'

I tried the sugestion above but I'm getting an error that says everything could not be bound.

This is the query I'm using.

SELECT CUSTOMER_ORDER.ROWID, CUSTOMER_ORDER.ID, CUSTOMER_ORDER.CUSTOMER_ID, OPERATION.SEQUENCE_NO, OPERATION.STATUS,
CUST_ORDER_LINE.LINE_NO, CUST_ORDER_LINE.PART_ID, CUST_ORDER_LINE.UNIT_PRICE, CUST_ORDER_LINE.TRADE_DISC_PERCENT, PART.DESCRIPTION,
PART.PRODUCT_CODE, PART.UNIT_PRICE AS Expr1
FROM

(SELECT CUSTOMER_ORDER.ROWID, CUSTOMER_ORDER.ID, CUSTOMER_ORDER.CUSTOMER_ID, OPERATION.SEQUENCE_NO, OPERATION.STATUS,
CUST_ORDER_LINE.LINE_NO, CUST_ORDER_LINE.PART_ID, CUST_ORDER_LINE.UNIT_PRICE, CUST_ORDER_LINE.TRADE_DISC_PERCENT, PART.DESCRIPTION,
PART.PRODUCT_CODE, PART.UNIT_PRICE AS Expr1, ROW_NUMBER() OVER (ORDER BY OPERATION.SEQUENCE_NO desc) AS ROW_NO
FROM PART INNER JOIN
CUST_ORDER_LINE ON PART.ID = CUST_ORDER_LINE.PART_ID CROSS JOIN
DEMAND_SUPPLY_LINK INNER JOIN
CUSTOMER_ORDER ON DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUSTOMER_ORDER.ID INNER JOIN
OPERATION ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = OPERATION.WORKORDER_BASE_ID) T1
WHERE Row_No = 1 AND STATUS = 'C' AND (CUSTOMER_ORDER.CUSTOMER_ID = 'Novae') AND (CUST_ORDER_LINE.LINE_NO = '1')

Can someone help out this newbee.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 16:57:16

Here is your statement with corrected syntax. However, there are no results based on your sample data and criteria. What is the expected results for this data?


SELECT ROWID
       ,ID
       ,CUSTOMER_ID
       ,SEQUENCE_NO
       ,STATUS
       ,LINE_NO
       ,PART_ID
       ,UNIT_PRICE
       ,TRADE_DISC_PERCENT
       ,DESCRIPTION
       ,PRODUCT_CODE
       ,UNIT_PRICE AS Expr1
FROM
(
       SELECT co.ROWID
              ,co.ID
              ,co.CUSTOMER_ID
              ,o.SEQUENCE_NO
              ,o.STATUS
              ,col.LINE_NO
              ,col.PART_ID
              ,col.UNIT_PRICE
              ,col.TRADE_DISC_PERCENT
              ,PART.DESCRIPTION
              ,PART.PRODUCT_CODE
              ,PART.UNIT_PRICE AS Expr1
              ,ROW_NUMBER() OVER (ORDER BY o.SEQUENCE_NO desc) AS ROW_NO
       FROM PART 
       INNER JOIN CUST_ORDER_LINE col
              ON PART.ID = col.PART_ID 
       CROSS JOIN DEMAND_SUPPLY_LINK dsl
       INNER JOIN CUSTOMER_ORDER co
              ON dsl.DEMAND_BASE_ID = co.ID 
       INNER JOIN OPERATION o
              ON dsl.SUPPLY_BASE_ID = o.WORKORDER_BASE_ID
       ) T1
WHERE Row_No = 1 
AND STATUS = 'C' 
AND (CUSTOMER_ID = 'Novae') 
AND (LINE_NO = '1')

EDIT:
Just as a wild guess, try replacing the row_number line with this:

              ,ROW_NUMBER() OVER (partition by o.WORKORDER_BASE_ID order by o.SEQUENCE_NO desc) AS ROW_NO

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources