PL-SQL Query

[jcasas23]

I need help with a query thet I need to make a report.
I have this info.
Table Name 'header'
Fields Id, PartNumber, CodeNumber, Quantity
Info
1, 12345, C12345, 10
2, 67890, C67890, 25
3, C12345, , 23
4, C67890, , 15
5, 54321, C12345, 5
I want to show in a single query row like next:
Id, PartNumber, CodeNumbe, Quantity, Qty for Code Number
1, 12345, C12345, 10, 23
2, 67890, C67890, 25, 25
5, 54321, C12345, 5, 23

I have this query
select id, PartNumber, CodeNumber, quantity
from header
where PartNumber <> 'C%';

but I do not know how could I add the field for the Qty for CodeNumber.

I will appreciate any help.

 

[SantaMufasa]

J,

You must describe to us your algorithm for deriving the "Qty for CodeNumber"...it is not intuitive. Also on your existing query, I believe you want your WHERE clause to read: "...where PartNumber not LIKE 'C%';"; if you say "...where PartNumber <> 'C%';", will display all rows since none of them have PartNumber that equals "C%".

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:17 (27Aug04) UTC (aka "GMT" and "Zulu"), 16:17 (27Aug04) Mountain Time)

 

[jcasas23]

Yes, sorry I miss that.

The correct query that I have is the next.

select id, PartNumber, CodeNumber, quantity
from header
where PartNumber not like 'C%';

but I want to show the quantity for the CodeNumber, I do not know if I need another query or any function for that.

Thanks for any help

 

[SantaMufasa]

J,

Try this code:

col a heading "Id" format 999
col b heading "Part|Number" format a6
col c heading "Code|Number" format a6
col d heading "Quantity" format 9,999
col e heading "Quantity|for|Code Number" format 9,999
select	 x.fields_id a
	,x.PartNumber b
	,y.PartNumber c
	,x.quantity d
	,y.quantity e
from header x, header y
where x.CodeNumber = y.Partnumber
order by x.fields_id
/

                               Quantity
     Part   Code                    for
  Id Number Number Quantity Code Number
---- ------ ------ -------- -----------
   1 12345  C12345       10          23
   2 67890  C67890       25          15
   5 54321  C12345        5          23

3 rows selected.

The above code works fine provided that you always have a "Code number" to match "Part Number".

Let us know if this resolves your need,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:29 (30Aug04) UTC (aka "GMT" and "Zulu"), 10:29 (30Aug04) Mountain Time)

 

[jcasas23]

Hi Mufasa

Thanks it is working the query that you send me but I hava a new issue.

I have 2 tables one as catalog and one to save detail of inventory.

Table 1: Catalog
Fields: Id, PartNumber, CodeNumber, Descrption
Info: 1, 12345, C12345, AAAA
2, 67890, C67890, BBBB
3, C12345, , CCCC
4, C67890, , DDDD
5, 54321, C12345, EEEE

Table 2: Inventory
Fields: Id, Quantity
1, 10
2, 25
3, 23
4, 15
5, 5

and I want something similar to my first question.
I want to see in a query the next:

Qty for Qty for
Id, PartNumber, CodeNumber, PNumber, CNumber
1, 12345, C12345, 10, 23
2, 67890, C67890, 25, 15
5, 54321, C12345, 5, 23

Thanks in advance.