October 14, 2009 at 1:30 pm
Looks like I messed up the requirment for this. Basicly i need an if statment in there some how.
select records where the max field in operations table's status field = C
CREATE TABLE CUSTOMER_ORDER
(
ROWID INT ,
ID INT ,
CUSTOMER_ID INT
)
CREATE TABLE DEMAND_SUPPLY_LINK
(
DEMAND_BASE_ID INT ,
SUPPLY_BASE_ID INT
)
CREATE TABLE OPERATION
(
WORKORDER_BASE_ID INT ,
SEQUENCE_NO INT,
STATUS CHAR(1)
)
INSERT INTO CUSTOMER_ORDER VALUES (31232, 2801,0001)
INSERT INTO CUSTOMER_ORDER VALUES (31231, 2802,0002)
INSERT INTO DEMAND_SUPPLY_LINK VALUES (2801, 15)
INSERT INTO DEMAND_SUPPLY_LINK VALUES (2802, 16)
INSERT INTO OPERATION VALUES (15, 25, 'C')
INSERT INTO OPERATION VALUES (15, 115, 'F')
INSERT INTO OPERATION VALUES (15, 13, 'C')
INSERT INTO OPERATION VALUES (16, 87, 'D')
INSERT INTO OPERATION VALUES (16, 5, 'C')
INSERT INTO OPERATION VALUES (16, 659, 'C')
EXAMPLE:
SELECT CO.ROWID, CO.ID, CO.CUSTOMER_ID, DSL.DEMAND_BASE_ID, DSL.SUPPLY_BASE_ID, OP.WORKORDER_BASE_ID, OP.SEQUENCE_NO, OP.STATUS
FROM CUSTOMER_ORDER AS CO INNER JOIN
DEMAND_SUPPLY_LINK AS DSL ON CO.ID = DSL.DEMAND_BASE_ID INNER JOIN
OPERATION AS OP ON DSL.SUPPLY_BASE_ID = OP.WORKORDER_BASE_ID
WHERE (CO.CUSTOMER_ID = 1)
--Since the MAX(SEQUENCE_NO (115)) STATUS for customer 1 = F Then nothing should be returned or selected
--
--IF CO.CUSTOMER_ID = 2 Then
--
--Need ONLY the records if the MAX(SEQUENCE_NO (659) ) STATUS = C
--Results should be everything NOT including data from the OPERATION TABLE its just for checking if --max field is c
--RowIDIDCUSTOMER_IDDEMAND_BASE_IDSUPPLY_BASE_IDWORKORDER_BASE_ID
--312312802228021616
October 14, 2009 at 1:56 pm
Sorry if I've not understood your requirement clearly, but is this what you are looking for?
SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY SEQUENCE_NO desc) AS ROW_NO
FROM CUSTOMER_ORDER AS CO
INNER JOIN DEMAND_SUPPLY_LINK AS DSL
ON CO.ID = DSL.DEMAND_BASE_ID
INNER JOIN OPERATION AS OP
ON DSL.SUPPLY_BASE_ID = OP.WORKORDER_BASE_ID) T1
WHERE (Row_NO =1 AND CUSTOMER_ID = 1 AND STATUS <> 'F' )
OR (Customer_ID = 2 AND Row_No =1 AND STATUS = 'C')
---------------------------------------------------------------------------------
October 14, 2009 at 2:53 pm
This PDF has an article called The Questions. If I understand what you're trying to do, I show three different ways to solve that problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2009 at 11:43 am
Please forgive my newbee SQL status.. :w00t:
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.
October 16, 2009 at 12:03 am
This may not be the best way of doing it, but it's how I would do it:
DECLARE @MaxSN INT
SELECT @MaxSN = MAX(SEQUENCE_NO) FROM Operation
/*JOIN On to this result set*/
SELECT *
FROM Operation
WHERE STATUS = 'C' AND SEQUENCE_NO = @MaxSN
If I understand your criteria correctly, you are only interested in returning records IF the STATUS field on the HIGHEST SEQUENCE_NO in the table = 'C'
The above code should return rows that meet this criteria (if any) and you can join to this.
October 16, 2009 at 4:54 am
Thank you all for your help.. You guys rock!!!!
The problem was that I was including the table name on my second select. I was thinking more about it trying to learn and realized that I was doing a select on the results of my first select and adding the table name was invalid. So I basically changed CUSTOMER_ORDER.ROWID to ROWID and so on.
I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes.
Thanks again... Maybe someday I'll be able to provide some support on this forum...
October 16, 2009 at 7:50 am
stewsterl 80804 (10/16/2009)
Thank you all for your help.. You guys rock!!!!The problem was that I was including the table name on my second select. I was thinking more about it trying to learn and realized that I was doing a select on the results of my first select and adding the table name was invalid. So I basically changed CUSTOMER_ORDER.ROWID to ROWID and so on.
I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes.
Thanks again... Maybe someday I'll be able to provide some support on this forum...
You already have. Well said. If you don't mind, I'm going to quote you in my sig.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply