Home » SQL & PL/SQL » SQL & PL/SQL » Your kindly help in this query (pivot) (Oracle 8.1.7.0.0, Wuindos 2007)
Your kindly help in this query (pivot) [message #574591] Sun, 13 January 2013 22:48 Go to next message
kumarvk
Messages: 185
Registered: July 2004
Senior Member
Hi All,

It must be crazy for you people for me asking help in an out dated version what to do my company is not in a position to upgrade. I need your expert help in this query. If you see the rows it will be repeated for column NT_INV I want to display in one row. I tried using MAX(decode.. but cannot get the result. Please kindly help. I created a view from two different tables.

DROP TABLE MSS.MFDMS CASCADE CONSTRAINTS;
CREATE TABLE MSS.MFDMS
(
  DSC       VARCHAR2(30),
  CODE      VARCHAR2(8),
  NAME      VARCHAR2(40),
  BAL       NUMBER,
  UOMS      VARCHAR2(4),
  TX_DTIME  DATE,
  NT_INV    NUMBER
)
TABLESPACE MSSU
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;



SET DEFINE OFF;
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '0468', 'BULB, 24V70W - HALOGEN (H4 LUGT BASE)', 6, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '4052', 'BULB, 24V5W - SINGLE CONTACT', 25, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '5249', '24V / 25/ 5W DOUBLE CONTACT BULB', 25, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('DEFAULT VALUE (CENTRE PROFILE)', 'T0358', 'STAINLESS STEEL RAILINGS', 36, 'SET', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('FUEL', 'DIESEL', 'DIESEL FUEL', 2090, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'BESCO', 'BESCO CLEAN ENGINE OIL (ISUZU)', 328.8, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'E4. PUMP', 'E4 SUPPLY PUMP ASSY 094000-0480 CYZ/CYH', 2, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'KQ9901', 'CYZ/CXH TRANSMISSION HOSE SET', 27, 'SET', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50014986', 'SHELL RIMULA R3 x 15W-40(550033151)', 3806.66, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50024937', 'SHELL SPIRAX S3 ATF MD3', 26, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50025669', 'SHELL GADUS S2 V220 2_1 GREASE(EP2)', 186.01, 'KG', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50027043', 'SHELL GRADUS S3 (BEARING GREASE)', 38, 'KG', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50030273', 'SHELL RIMULA R4L 15W40 C14_1*209L_A227', 1829.5, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '55002550', 'SHELL RIMULA R3x15W-40 1*209L', 5, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '84053370', 'BESCO COOLANT LLC', 249, 'L', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('OPEL PARTS', '09111019', 'VIVARO OIL FILTER', 8, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('OPEL PARTS', '93161121', 'VIVARO 64V FUEL FILTER', 6, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'CYH/CYZ', 'L-SHAPE BRACKET FOR TRANS OIL FILTER', 32, 'SET', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'LC 1005', 'M8x20 SS BOLT&NUTS (NHR RAILING)', 780, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'RMAT', 'RUBBER FLOOR MAT (SMALL)', 278, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'TB1207C', 'LIQUID GASKETS', 3.05, 'PCS', 
    0);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '0468', 'BULB, 24V70W - HALOGEN (H4 LUGT BASE)', 0, 'PCS', 
    2);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '4052', 'BULB, 24V5W - SINGLE CONTACT', 0, 'PCS', 
    1);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('BATTERY & ELECTRICS', '5249', '24V / 25/ 5W DOUBLE CONTACT BULB', 0, 'PCS', 
    1);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('DEFAULT VALUE (CENTRE PROFILE)', 'T0358', 'STAINLESS STEEL RAILINGS', 0, 'SET', 
    2);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('FUEL', 'DIESEL', 'DIESEL FUEL', 0, 'L', 
    3605);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'BESCO', 'BESCO CLEAN ENGINE OIL (ISUZU)', 0, 'L', 
    16);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'E4. PUMP', 'E4 SUPPLY PUMP ASSY 094000-0480 CYZ/CYH', 0, 'PCS', 
    1);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('ISUZU PARTS', 'KQ9901', 'CYZ/CXH TRANSMISSION HOSE SET', 0, 'SET', 
    13);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50024937', 'SHELL SPIRAX S3 ATF MD3', 0, 'L', 
    45);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50025669', 'SHELL GADUS S2 V220 2_1 GREASE(EP2)', 0, 'KG', 
    6);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50027043', 'SHELL GRADUS S3 (BEARING GREASE)', 0, 'KG', 
    1.5);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '50030273', 'SHELL RIMULA R4L 15W40 C14_1*209L_A227', 0, 'L', 
    115.5);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '55002550', 'SHELL RIMULA R3x15W-40 1*209L', 0, 'L', 
    5);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('LUBRICANT', '84053370', 'BESCO COOLANT LLC', 0, 'L', 
    48);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('OPEL PARTS', '09111019', 'VIVARO OIL FILTER', 0, 'PCS', 
    1);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('OPEL PARTS', '93161121', 'VIVARO 64V FUEL FILTER', 0, 'PCS', 
    1);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'CYH/CYZ', 'L-SHAPE BRACKET FOR TRANS OIL FILTER', 0, 'SET', 
    13);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'LC 1005', 'M8x20 SS BOLT&NUTS (NHR RAILING)', 0, 'PCS', 
    20);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'RMAT', 'RUBBER FLOOR MAT (SMALL)', 0, 'PCS', 
    6);
Insert into MFDMS
   (DSC, CODE, NAME, BAL, UOMS, 
    NT_INV)
 Values
   ('SUNDRIES & MISC', 'TB1207C', 'LIQUID GASKETS', 0, 'PCS', 
    4);
COMMIT;

Report message to a moderator

Re: Your kindly help in this query (pivot) [message #574592 is a reply to message #574591] Sun, 13 January 2013 23:12 Go to previous messageGo to next message
spacebar
Messages: 18
Registered: February 2007
Junior Member
Um, Ok, Post the query you can't get to work and example of what you want the results to be.....

Report message to a moderator

Re: Your kindly help in this query (pivot) [message #574599 is a reply to message #574591] Mon, 14 January 2013 01:09 Go to previous message
Michel Cadot
Messages: 54165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result you want for the data you gave.

And I already told do NOT post the schema and tablespace names (and storage clause), we have not the same ones as you.

Regards
Michel

Report message to a moderator

Previous Topic: Problem with comparing collections
Next Topic: how to handle null in date time
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 21 12:18:34 CDT 2013

Total time taken to generate the page: 0.16706 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.