Home » SQL & PL/SQL » SQL & PL/SQL » Intra-Partitioning Query
Intra-Partitioning Query [message #224861] Thu, 15 March 2007 15:20 Go to next message
fes937
Messages: 7
Registered: March 2007
Junior Member
Hi,

I'm trying to create an Intra Partioning Query but need a bit of help.

What i have so far is:

Select call_id,
Sum(decode(start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)) Charge12,
Sum(decode(start_date_time (To_Date('19-MAR-2006', 'DD-MON-YYYY')), charge, NULL)) Charge19,
Sum(decode(start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)) -
Sum(decode(start_date_time (To_Date('19-MAR-2006', 'DD-MON-YYYY')), charge, NULL)) diff
From phone_call
Group By call_id;

The error i'm getting is that it doesnot recognise "start_date_time.

I have checked the spelling and it matches up.

Appreciate any help

Thanks
Re: Intra-Partitioning Query [message #224863 is a reply to message #224861] Thu, 15 March 2007 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I have checked the spelling and it matches up.
If the above is true, then you have no problem or you should file a bug.

Why are you describing what you think you are doing and how Oracle responds; rather than just doing CUT & PASTE so we (TINW) can see for ourselves is or is not happening?

Include SQL & DESC of all tables involved.

[Updated on: Thu, 15 March 2007 15:32] by Moderator

Report message to a moderator

Re: Intra-Partitioning Query [message #224864 is a reply to message #224863] Thu, 15 March 2007 15:37 Go to previous messageGo to next message
fes937
Messages: 7
Registered: March 2007
Junior Member
The tables are:

CREATE Table Customer
(Customer_ID NUMBER (6),
Forename VARCHAR2 (20),
Surname VARCHAR2 (20),
Age NUMBER (3),
Tel_Code NUMBER (4),
Tel_No VARCHAR2 (7),
constraint customer_PK primary key (Customer_ID));


CREATE Table Phone_Call
(Call_ID NUMBER(6),
Call_From NUMBER(6),
Call_To NUMBER(6),
Start_Date_Time DATE,
End_Date_Time DATE,
Charge DECIMAL(4,2),
constraint Phone_Call_PK primary key (Call_ID),
constraint Phone_Call_FROM_FK foreign key (Call_From) REFERENCES Customer (Customer_ID),
constraint Phone_Call_TOO_FK foreign key (Call_To) REFERENCES Customer (Customer_ID));
Re: Intra-Partitioning Query [message #224865 is a reply to message #224861] Thu, 15 March 2007 15:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY'))
What kind of syntax is above?
What do you expect SQL to do with this collection of charaters?
Re: Intra-Partitioning Query [message #224866 is a reply to message #224865] Thu, 15 March 2007 15:45 Go to previous messageGo to next message
fes937
Messages: 7
Registered: March 2007
Junior Member
It's getting that data in that format. DD-MON-YYYY

The start_date_time field also has time: HH-MM-SS
Re: Intra-Partitioning Query [message #224867 is a reply to message #224861] Thu, 15 March 2007 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>It's getting that data in that format. DD-MON-YYYY
No, it is NOT! It is generating error & rightfully so.
You might try TO_CHAR(start_date_time, 'DD-MON-YYYY'))
START_TIME_DATE is already a DATE data type & TO_DATE has NO purpose.
Re: Intra-Partitioning Query [message #224875 is a reply to message #224867] Thu, 15 March 2007 16:37 Go to previous messageGo to next message
fes937
Messages: 7
Registered: March 2007
Junior Member
TO_DATE has worked for me before

Re: Intra-Partitioning Query [message #224877 is a reply to message #224861] Thu, 15 March 2007 16:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>TO_DATE has worked for me before
Most likely due to dumb luck & implicit data type conversion which did not error out.
If you are sure you are correct, then continue as before & ignore me.
Re: Intra-Partitioning Query [message #224964 is a reply to message #224861] Fri, 16 March 2007 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If this is an actual cut and paste of your query, then I see your problem:
Select call_id,
Sum(decode(start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)) Charge12,
Sum(decode(start_date_time (To_Date('19-MAR-2006', 'DD-MON-YYYY')), charge, NULL)) Charge19,
Sum(decode(start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)) - 
Sum(decode(start_date_time (To_Date('19-MAR-2006', 'DD-MON-YYYY')), charge, NULL)) diff
From phone_call
Group By call_id;

In the lines like this
decode(start_date_time (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)
You are missing a comma after the first term. It should look like this:
decode(start_date_time, (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)
                      ^

The way you have written is, it is looking for a function called START_DATE_TIME that it can pass a date parameter to.
Re: Intra-Partitioning Query [message #225147 is a reply to message #224866] Sun, 18 March 2007 08:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If the below statement is correct.

> It's getting that data in that format. DD-MON-YYYY

> The start_date_time field also has time: HH-MM-SS

then your query will work only when start_date_time column is having 00.00.00 in the time component otherwise you will get null

Please find the example

create table test 
(sno number,
start_date_time date);

Table created.

insert into test values (1,SYSDATE);

1 row created.

insert into test values (2,SYSDATE+1);

1 row created.

insert into test values (3,sysdate+2);

1 row created.

insert into test values (4,trunc(sysdate));

1 row created.

commit;

Commit complete.

alter session set nls_Date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

select * from test;

       SNO START_DATE_TIME
---------- --------------------
         1 18-MAR-2007 13:14:03
         2 19-MAR-2007 13:14:14
         3 20-MAR-2007 13:14:20
         4 18-MAR-2007 00:00:00

select sno, decode(start_date_time, to_date('18-MAR-2007','DD-MON-YYYY'), '18th March', null),
decode(start_date_time,to_date('19-mar-2007','DD-MON-YYYY'), '19th March', null),
decode(start_Date_time,to_date('20-mar-2007','DD-MON-YYYY'),'20th March',null) from test;

       SNO DECODE(STA DECODE(STA DECODE(STA
---------- ---------- ---------- ----------
         1
         2
         3
         4 18th March




You have to make a decision as whether you want to sum all the records on the given day and if that is the case then your sum statement will look like
Sum(decode(trunc(start_date_time), (To_Date('12-JAN-2006', 'DD-MON-YYYY')), charge, NULL)) 


cheers
Re: Intra-Partitioning Query [message #228383 is a reply to message #224861] Mon, 02 April 2007 15:59 Go to previous message
dreamkitchen
Messages: 3
Registered: April 2007
Junior Member
Are you using SYSDATE as a place holder for a new date to be inserted via UPDATE?

What if you know the ecaxt time you want inserted? What does the insert for DATE type look like with time?

thanks
Jerry H
Previous Topic: find last wedday
Next Topic: max date
Goto Forum:
  


Current Time: Mon Dec 05 21:24:42 CST 2016

Total time taken to generate the page: 0.27080 seconds