sql - Group by item detect first or last record in FOR SELECT - Stack Overflow

admin管理员组

文章数量:1125916

Is there a function or workaround in Firebird 5 that tells you in a FOR SELECT whether the GROUP BY element is the first or last data item?

Example:

FOR SELECT SITE_ID, DELIVER, ITEM_NUMBER, SUM( QTY_ORDERED ) AS SUM_QTY_ORDER
    FROM ORDER_DETAIL
    WHERE ...
    GROUP BY SITE_ID, DELIVER, ITEM_NUMBER
    AS CURSOR OD DO
BEGIN
   --THIS 
   IF FIRST( SITE_ID ) THEN
   BEGIN
       -- do picklist number
   END

   -- Do something
   IF LAST( DELIVER ) THEN
   BEGIN
       -- do quantity on hand checks
   END
END

I would need this because a picking list number should be generated at the first occurrence of SITE_ID. At the last DELIVER data, it should be determined whether the item is already in stock.

I haven't come across something like this before, or does anyone have any other ideas?

Table and test data:

CREATE TABLE ORDER_DETAIL
( ORDER_NUMBER VARCHAR( 22 ) DEFAULT '' NOT NULL,
  ORDER_LINE INTEGER DEFAULT 0 NOT NULL,
  SITE_ID VARCHAR( 8 ) DEFAULT '',
  DELIVER VARCHAR( 8 ) DEFAULT '',
  ITEM_NUMBER VARCHAR( 18 ) DEFAULT '',
  QTY_ORDERED NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  UNIT_OF_MEASURE VARCHAR( 2 ) DEFAULT '',
  QTY_ALLOC  NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  QTY_TO_PICK  NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  QTY_SHIPPED  NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  DUE_DATE DATE,
  DESCRIPTION VARCHAR( 64 ) DEFAULT '',
  LIST_PRICE  NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  DISCOUNT  NUMERIC( 6, 3 ) DEFAULT 0 NOT NULL,
  FINAL_PRICE  NUMERIC( 14, 6 ) DEFAULT 0 NOT NULL,
  PRICING_DATE DATE,
  NEED_DATE DATE,
  LINE_TYPE VARCHAR( 8 ) DEFAULT 'NORMAL' NOT NULL,
  LOT_NUMBER VARCHAR( 22 ),
  REFERENCE VARCHAR( 8 ),
  CONSTRAINT ORDER_DETAIL PRIMARY KEY ( ORDER_NUMBER, ORDER_LINE ) );
  
CREATE INDEX ORDER_PART ON ORDER_DETAIL( ITEM_NUMBER, SITE_ID, DELIVER, DUE_DATE, ORDER_NUMBER );
CREATE INDEX ORDER_SITE ON ORDER_DETAIL( SITE_ID, DELIVER, ITEM_NUMBER, ORDER_NUMBER );  

INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES ('FO2412010000001', 1, '1', 'FA7', '9021', 500.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 375.000000, 0, 375.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'FO2412010000001', 2, '1', 'FA7', '9033', 127.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 370.000000, 0, 370.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'FO2412010000001', 3, '1', 'FA9', '9101', 470.000000, 'DB', 0.000000, 0.000000, 0.000000, NULL, '', 41.000000, 0, 41.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'FO2412010000001', 4, '1', 'FA9', '9121', 352.000000, 'DB', 0.000000, 0.000000, 0.000000, NULL, '', 41.000000, 0, 41.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'FO2412010000002', 1, '1', 'FA7', '9021', 200.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 375.000000, 0, 375.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'FO2412010000002', 2, '1', 'FA7', '9033', 40.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 370.000000, 0, 370.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055621', 1, '7', 'FA7', '9021', 100.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 375.000000, 0, 375.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055621', 2, '7', 'FA7', '9033', 25.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 370.000000, 0, 370.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055621', 3, '7', 'HGZ788', '9310007887', 32.000000, 'DB', 0.000000, 0.000000, 0.000000, NULL, '', 124.000000, 0, 124.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055621', 4, '7', 'FA7', '9512', 1.000000, 'DB', 0.000000, 0.000000, 0.000000, NULL, '', 200.000000, 100, 0.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055621', 5, '7', 'HGZ788', '', 1.000000, 'DB', 0.000000, 0.000000, 0.000000, NULL, 'Packaging Cost', 217.000000, 0, 217.000000, NULL, NULL, 'MEMO', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055622', 1, '7', 'FA7', '9021', 117.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 375.000000, 0, 375.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055623', 1, '7', 'FA7', '9021', 412.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 375.000000, 0, 375.000000, NULL, NULL, 'NORMAL', NULL, NULL);
INSERT INTO ORDER_DETAIL ( ORDER_NUMBER, ORDER_LINE, SITE_ID, DELIVER, ITEM_NUMBER, QTY_ORDERED, UNIT_OF_MEASURE, QTY_ALLOC, QTY_TO_PICK, QTY_SHIPPED, DUE_DATE, DESCRIPTION, LIST_PRICE, DISCOUNT, FINAL_PRICE, PRICING_DATE, NEED_DATE, LINE_TYPE, LOT_NUMBER, REFERENCE ) VALUES (  'BO2412030055623', 2, '7', 'FA7', '9033', 20.000000, 'KG', 0.000000, 0.000000, 0.000000, NULL, '', 370.000000, 0, 370.000000, NULL, NULL, 'NORMAL', NULL, NULL);

本文标签： sqlGroup by item detect first or last record in FOR SELECTStack Overflow