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

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

Having twice looked at what you posted I have no idea what you are
asking and I sure don't know in what version you are working.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

> Having twice looked at what you posted I have no idea what you are
> asking

Glad to know I wasn't the only one!

Cheers,
Brian




--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

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.

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.