Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query help 1

Status
Not open for further replies.
mdarsot

mdarsot

Instructor
Apr 13, 2006
86
CA
select
shipment_id as ship_id,
sum(in_parts) as parts_recieved,
sum(out_parts) as parts_sold,
to_char(sent_date, 'DD-Mon-YYYY') as setn_date
From
parts_table
where
set_id like '33%'
group by
shipment_id,
to_char(sent_date,'DD-Mon-YYYY')


This will produce something like this
33Y 500 234 02-Mar-2009
33Z 200 122 03-Mar-2009



What i want is something like this. with 2 extra field
1) in_parts - out_parts = value
2) sent_date + 8days = new value with day specified

results will look like

33Y 500 234 02-Mar-2009 266 Monday-09-Mar-2009
33Z 200 122 03-Mar-2009 78 Tuesday-10-Mar-2009
 
Sort by date Sort by votes
MDarsot,

Here is some code that does what you requested:
Code: 
select
    shipment_id                             as ship_id,
    sum(in_parts)                           as parts_recieved,
    sum(out_parts)                          as parts_sold,
    to_char(sent_date, 'DD-Mon-YYYY')       as setn_date,
    sum(in_parts)-sum(out_parts)            as value,
    to_char(sent_date+8,'fmDay-dd-Mon-yyyy')as new_value
From
    parts_table
where
    set_id like '33%'
group by
    shipment_id,
    sent_date
/

SHI PARTS_RECIEVED PARTS_SOLD SETN_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Y            500        234 02-Mar-2009        266 Tuesday-10-Mar-2009
33Z            200        122 03-Mar-2009         78 Wednesday-11-Mar-2009
As you see, coding "sent_date + 8 days" gives you a day beyond your request for each row, but perhaps you wanted, instead, "sent_date + 7 days". If so, just change the "+8" in the code to "+7".

Let us know your thoughts.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
thanks only 1 issue with above query. It does the math and returns a number when sum(out_parts) is present. However is sum(out_parts)=0 then it gives me no result.

Is there way i can get a result every time.
 
Upvote 0 Downvote
MDarsot said:
However if sum(out_parts)=0 then it gives me no result.
Click to expand...
Unless I am mistaken, that scenario is not occurring. If you are receiving "no result", then it is because there is no record present to represent the group you are missing.


Your GROUP BY clause looks like this:
Code: 
group by
    shipment_id,
    sent_date
Are you saying that you want a row to represent every combination of "shipment_id" and "sent_date"? We can make that happen, but before I propose such code, I want to ensure that such is what you really want.

If I am mistaken about all of this, then can you please post some sample input rows and output results that illustrate the scenario you are talking about?

Thanks,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
here is sample output

ship_id out in diff date
33Z 25563 25563 0 Fri-06-Mar-2009 09
33Y 5615 5615 0 Fri-06-Mar-2009 09
33K 61 Fri-06-Mar-2009 09

The above field diff calculate when column "in" is present. however when column "in" is not present in line #3 diff returns empty instead of giving me diff of "61"


 
Upvote 0 Downvote
In the case where you are doing an arithmetic operation on an expression that is NULL, the entire result becomes NULL. (It's rather like putting a teaspoon of sewage into a 400-litre vat of fine wine...you now have 400-litres of sewage.)

Therefore, whenever you have an operand in an arithmetic expression that can possibly be NULL, you use Oracle's NVL() function. So, notice the difference in output between this code that doesn't use NVL()...
Code: 
select
    shipment_id                             as ship_id,
    sum(in_parts)                           as parts_recieved,
    sum(out_parts)                          as parts_sold,
    to_char(sent_date, 'DD-Mon-YYYY')       as setn_date,
    sum(in_parts)-sum(out_parts)            as value,
    to_char(sent_date+8,'fmDay-dd-Mon-yyyy')as new_value
From
    parts_table
where
    set_id like '33%'
group by
    shipment_id,
    sent_date
order by 4,1 desc
/

SHI PARTS_RECIEVED PARTS_SOLD SETN_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Y            500        234 02-Mar-2009        266 Tuesday-10-Mar-2009
33Z            200        122 03-Mar-2009         78 Wednesday-11-Mar-2009
33Z           5615       5615 06-Mar-2009          0 Saturday-14-Mar-2009
33Y          25563      25563 06-Mar-2009          0 Saturday-14-Mar-2009
33K             61            06-Mar-2009            Saturday-14-Mar-2009
...and this code that does use NVL():
Code: 
SHI PARTS_RECIEVED PARTS_SOLD SETN_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Y            500        234 02-Mar-2009        266 Tuesday-10-Mar-2009
33Z            200        122 03-Mar-2009         78 Wednesday-11-Mar-2009
33Z           5615       5615 06-Mar-2009          0 Saturday-14-Mar-2009
33Y          25563      25563 06-Mar-2009          0 Saturday-14-Mar-2009
33K             61            06-Mar-2009         61 Saturday-14-Mar-2009
But if your business rules state that you should not have non-numeric values for in_parts and for out_parts, then a better solution (from a data integrity perspective) is to assign NOT NULL constraints to those two columns.

Let us know your thoughts.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
thanks

whats the syntax to use nvl with sum
 
Upvote 0 Downvote
oh and i forgot to ask what do i have to do to get column title displayed on each set of records which is group by send date. So if send date changes break 2 and display column headings again.

 
Upvote 0 Downvote
ok i figured out the nvl part.

Only thing required now is the column headings.
 
Upvote 0 Downvote
MDarsot said:
whats the syntax to use nvl with sum
Click to expand...
Silly, silly me...I can't believe I didn't post the revised syntax to use NVL. (I thought that I never forgot the revised code. [banghead])


So, here is the syntax, along with the SQL*Plus syntax to cause a break when sent_date chnges, along with page column headings with the break:
Code: 
break on sent_date skip page
select
    shipment_id                                as ship_id,
    sum(in_parts)                              as parts_recieved,
    sum(out_parts)                             as parts_sold,
    to_char(sent_date, 'DD-Mon-YYYY')          as sent_date,
    sum(nvl(in_parts,0))-sum(nvl(out_parts,0)) as value,
    to_char(sent_date+8,'fmDay-dd-Mon-yyyy')   as new_value
From
    parts_table
where
    set_id like '33%'
group by
    shipment_id,
    sent_date
order by 4,1 desc
/

SHI PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Y            500        234 02-Mar-2009        266 Tuesday-10-Mar-2009

SHI PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Z            200        122 03-Mar-2009         78 Wednesday-11-Mar-2009

SHI PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
--- -------------- ---------- ----------- ---------- ---------------------
33Z           5615       5615 06-Mar-2009          0 Saturday-14-Mar-2009
33Y          25563      25563                      0 Saturday-14-Mar-2009
33K             61                                61 Saturday-14-Mar-2009

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
i did this for some reason it just wont show title

when i do


break on sent_date skip 3

it does skip 3 lines after each sent_date however when i do page it does not do anythign. No titel is displayed anywhere.
 
Upvote 0 Downvote
ok i figure it out it works. i had pagesize set to 0 i removed it.

The column headings only displays as per columns. Is there a way to display full column headings regardless of field length.
 
Upvote 0 Downvote
MDarsot said:
Is there a way to display full column headings regardless of field length.
Click to expand...
Yes, you can use SQL*Plus's COLUMN definitions to exert full control over column behaviors.


Interestingly, if SQL*Plus recognizes a column's output as NUMBER, then the width of the column heading expands to the width of the column title or the column format, whichever is greater.

For Alphanumeric columns, the default width of the column display is the declared width of the table column. If you wish to modify that width, then use SQL*Plus's COLUMN definition, as I have done, below:
Code: 
set heading on
col ship_id format a7 heading "Ship ID"
break on sent_date skip page
select
    shipment_id                                as ship_id,
    sum(in_parts)                              as parts_recieved,
    sum(out_parts)                             as parts_sold,
    to_char(sent_date, 'DD-Mon-YYYY')          as sent_date,
    sum(nvl(in_parts,0))-sum(nvl(out_parts,0)) as value,
    to_char(sent_date+8,'fmDay-dd-Mon-yyyy')   as new_value
From
    parts_table
where
    set_id like '33%'
group by
    shipment_id,
    sent_date
order by 4,1 desc
/

Ship ID PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
------- -------------- ---------- ----------- ---------- ---------------------
33Y                500        234 02-Mar-2009        266 Tuesday-10-Mar-2009

Ship ID PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
------- -------------- ---------- ----------- ---------- ---------------------
33Z                200        122 03-Mar-2009         78 Wednesday-11-Mar-2009

Ship ID PARTS_RECIEVED PARTS_SOLD SENT_DATE        VALUE NEW_VALUE
------- -------------- ---------- ----------- ---------- ---------------------
33Z               5615       5615 06-Mar-2009          0 Saturday-14-Mar-2009
33Y              25563      25563                      0 Saturday-14-Mar-2009
33K                 61                                61 Saturday-14-Mar-2009
Let us know if this resoloves your question(s).

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sqlPower
  • Locked
  • Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
384
SkipVought
S
Tsmtbo2011
  • Locked
  • Question
Help Needed!! Micros WS6 610
Replies
1
Views
127
carp
carp
ssmith999
  • Locked
  • Question
Query help w/ fiscal calendar query
Replies
1
Views
96
johnherman
johnherman
rk68
  • Locked
  • Question
auto execute procedure/query on record insert
Replies
13
Views
119
rk68
rk68
DABETTIE
  • Locked
  • Question
Please Help! Lowered my manager privileges
Replies
0
Views
75
DABETTIE
DABETTIE

Part and Inventory Search

Sponsor

Back
Top