Thank you very much.... this was absolute solution!....
Sorry about diagram that got "wrapped" and i forgot to put variables
names.
Thanks!
Anil G
Charles Hooper wrote:
> Anil G wrote:
> > Hi,
> >
> > I have following data structure into table
> >
> > Name Type Qty
> > -------- ------- --------
> > N1 D 3 --------|
> > N1 F 2 | ----> Difference (D - I) DIDFF
> > ------------|
> > N1 I 4 --------|
> > |----> (S - DIDIFF) as SDIFF
> > N1 S 10
> > ----------------------------------------------------------------|
> > N2 D 2
> > ...
> > ...
> > Any clues about performing above type operations using SQL query rather
> > than writing PL/SQL???
> >
> >
> > Thanks in advance,
> >
> > Anil G
>
> I tried to understand what you are attempting to do here. Best guess:
> for the N1 Name entries, take the value of the row with "D" as the
> type, subtract from that the value of the row with "I" as the type, and
> assign this to a column named DIDFF. Take the value of the row with
> "S" as the type, subtract from it the result of the previous
> calculation, and assign this to a column named SDIFF.
>
> If my guess is correct, a SQL statement similar to the following should
> work:
> SELECT
> NAME,
> NVL(MAX(DECODE(TYPE,'D',QTY,0)),0)-NVL(MAX(DECODE(TYPE,'I',QTY,0)),0)
> DIDFF,
>
> NVL(MAX(DECODE(TYPE,'S',QTY,0)),0)-(NVL(MAX(DECODE(TYPE,'D',QTY,0)),0)-NVL(MAX(DECODE(TYPE,'I',QTY,0)),0))
> SDIFF
> FROM
> MY_TABLE
> GROUP BY
> NAME;
>
> Basic idea of what is happening in the above: for each change in the
> NAME column, find the largest QTY where the TYPE column is 'D' (assume
> that there is only one matching row), then repeat this syntax for the
> remaining TYPE values of interest and add/subtract the values as
> needed. This will collapse the multiple rows for each change in the
> NAME column to a single row, which is required for the calculations.
>
> SQL code to generate the test environment:
> CREATE TABLE MY_TABLE (
> NAME VARCHAR2(10),
> TYPE CHAR(1),
> QTY NUMBER(10));
>
> INSERT INTO MY_TABLE VALUES (
> 'N1',
> 'D',
> 3);
>
> INSERT INTO MY_TABLE VALUES (
> 'N1',
> 'F',
> 2);
>
> INSERT INTO MY_TABLE VALUES (
> 'N1',
> 'I',
> 4);
>
> INSERT INTO MY_TABLE VALUES (
> 'N1',
> 'S',
> 10);
>
> INSERT INTO MY_TABLE VALUES (
> 'N2',
> 'D',
> 2);
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Tue Sep 05 2006 - 22:16:49 CDT
