Home » SQL & PL/SQL » SQL & PL/SQL » Query to become a new table (Oracle 10g - PL/SQL Version 7.1.5.1396 )
Query to become a new table [message #393254] Fri, 20 March 2009 19:08 Go to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Hi

Can someone spot check me and let me know what I'm doing wrong?

 SELECT  item
      , date
      , amt
      , LAG(date,1) OVER (PARTITION BY item ORDER BY date)AS PREV_DATE
FROM itemtable
WHERE date between '01-FEB-2009' and '28-FEB-2009'


However the date I want to restrict is not within the the column Prev_date but that's what's pulling back. I want the date range for date column to pull back between feb 1 2009 and feb 28 2009 and the prev_date should not be restricted to that date range.Did I miss something? I guess my other question is how do I make this query be created into a new table?

Thanks.
Re: Query to become a new table [message #393255 is a reply to message #393254] Fri, 20 March 2009 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Why are you cross/multi-posting in direct violation of Posting Guidelines?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

http://www.orafaq.com/forum/m/391924/136107/#msg_391924

http://www.orafaq.com/forum/m/391845/136107/#msg_391845

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

By the way, neither '01-FEB-2009' nor '28-FEB-2009' are dates.

[Updated on: Fri, 20 March 2009 19:25]

Report message to a moderator

Re: Query to become a new table DDL [message #393258 is a reply to message #393255] Fri, 20 March 2009 20:25 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
If '1-FEB-2009' and '28-FEB-2009' are not dates, what is the standard?

My desired result looks similar to this:
CarNum	Date	        Cost	PrevDate
16	5/1/2008	337374	5/2/2007
16	5/2/2008	337538	5/1/2008
16	5/3/2008	337898	5/2/2008


Let's say my where clause is

 WHERE Date between 5/1/2009 and 5/31/2009 


But when I write the above where clause in, it would pull this data:
CarNum	Date	        Cost	PrevDate
16	5/2/2008	337538	5/1/2008
16	5/3/2008	337898	5/2/2008


and omits line:

CarNum	Date	        Cost	PrevDate
16	5/1/2008	337374	5/2/2007
Re: Query to become a new table [message #393259 is a reply to message #393254] Fri, 20 March 2009 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>If '1-FEB-2009' and '28-FEB-2009' are not dates, what is the standard?

There is no standard.
Keep in mind that Oracle considers characters between single quote marks to be strings.
'This is a string 2009-MAR-20 not a date'

When you need to convert strings to DATE datatype use TO_DATE() function.
Re: Query to become a new table DDL [message #393285 is a reply to message #393258] Sat, 21 March 2009 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Sat, 21 March 2009 01:39]

Report message to a moderator

Re: Query to become a new table [message #393352 is a reply to message #393254] Sat, 21 March 2009 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Just curious why when Posting Guidelines direct to post OS name & version and Oracle version to 4 places, you respond with:
>(Oracle 10g - PL/SQL Version 7.1.5.1396 )
which is really none of the above.
How/from where did you obtain PL/SQL Version 7.1.5.1396?
Re: Query to become a new table [message #393356 is a reply to message #393352] Sat, 21 March 2009 22:56 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Is my post incorrect again? Sad I'm so sorry.

That's what I'm seeing in the other posts. They list the version of the PL/SQL. By now you probably figure out that I'm not an IT specialist. The lingo in this forum is new to me. I just got introduced to this tool and the whole sql process. Does OS mean operating system? I'm using Windows XP and I'm not sure the version and oracle 10g. I don't know the details.

Thanks for replying. I appreciate all the help.
Re: Query to become a new table [message #393357 is a reply to message #393254] Sat, 21 March 2009 23:10 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>I'm not sure the version and oracle 10g.
SELECT * FROM V$VERSION
The results from SQL will display the Oracle version.

If you ask question about what you don't know or is unclear,
answers will be forthcoming directly.
Re: Query to become a new table [message #393591 is a reply to message #393254] Mon, 23 March 2009 08:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
jen804 wrote on Fri, 20 March 2009 20:08

 SELECT  item
      , date
      , amt
      , LAG(date,1) OVER (PARTITION BY item ORDER BY date)AS PREV_DATE
FROM itemtable
WHERE date between '01-FEB-2009' and '28-FEB-2009'


I guess my other question is how do I make this query be created into a new table?



All you would need to do is
create table mookie as {your select statement}
, but please do look up the TO_DATE function to properly use DATEs in Oracle.
Re: Query to become a new table [message #393626 is a reply to message #393357] Mon, 23 March 2009 12:06 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Thanks, BlackSwan. My version is:
1	Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
2	PL/SQL Release 10.2.0.4.0 - Production



SELECT item_no
      ,effective_date
      , cost1
      , LAG(effective_date2,1) OVER (PARTITION BY item_no ORDER BY effective_date)AS PREV_DATE
FROM cost1
WHERE effective_date between to_date('01-FEB-2009','DD-MON-YYYY') and to_date('28-FEB-2009','DD-MON-YYYY');


I've added to_date function and it's still not pull back the restricted field I want. I've also read the Test Case post but I really don't know where to start or what to post. Thanks.

[Updated on: Mon, 23 March 2009 12:32]

Report message to a moderator

Re: Query to become a new table [message #393745 is a reply to message #393626] Tue, 24 March 2009 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem you describe does not match the query you post.

The query you post will return all the rows that match effective date range - rows will not be excluded because the prev_date column is outside that range. See this example:
create table test_154 (col_1 number, col_2 date);

insert into test_154 select level, sysdate + level from dual connect by level <= 10;

commit;

select col_1
      ,col_2
      ,lag(col_2) over (order by col_2) prev_col_2
from   test_154
where col_1 between 3 and 7;


What does happen is that the first LAG value is null, as there is no previous value within the set of results.
You can work round that problem like this:
select col_1,col_2,prev_col_2
from (select col_1
            ,col_2
            ,lag(col_2) over (order by col_2) prev_col_2
      from   test_154)
where col_1 between 3 and 7;
, although this will perform worse, as it has to do a window sort on the full table, rather than on the range of rows you specify.

Is this the problem you are having?

If not, you need to write a description of your problem that you believe describes it accurately to people who know nothing about what you're doing other than what you tell them.
If you think that you've already done this, then there's a bigger problem, as your current explanation is muddled and confusing, and you'll need to work out why before you can correct that.
Re: Query to become a new table [message #396249 is a reply to message #393745] Sun, 05 April 2009 20:10 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Thank you for the advice! Smile I think I found a solution.

I use

Create table test
As SELECT item
      , date
      , amt
      , LAG(date,1) OVER (PARTITION BY item ORDER BY date)AS PREV_DATE
      , LAG(Amt,1) OVER (PARTITION BY item ORDER BY date)AS PREV_amt
FROM itemtable


then I wrote another query (below) to write the restriction and it works.

SELECT  item
      , date
      , amt
      , PREV_DATE
      , PREV_amt
FROM test
WHERE date between '1-jan-2009' and '31-jan-2009'

Re: Query to become a new table [message #396252 is a reply to message #393254] Sun, 05 April 2009 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
With Oracle characters between single quote marks are STRINGS!
'This is a string 2009-0404; not a date'
Both '1-jan-2009' and '31-jan-2009' are STRINGS.
When a DATE datatype is desired use the TO_DATE() function along with appropriate format mask.
Re: Query to become a new table [message #396408 is a reply to message #396249] Mon, 06 April 2009 08:24 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
jen804 wrote on Sun, 05 April 2009 21:10

SELECT  item
      , date
      , amt
      , PREV_DATE
      , PREV_amt
FROM test
WHERE date between '1-jan-2009' and '31-jan-2009'




Since you are just starting out in Oracle, why not do it the correct way, or are you another one of those stubborn people who has the attitude that if it works in one case for you, then it will work for everyone?
FOO SCOTT>select * from dual where sysdate between '1-jan-2009' and '31-jan-2009';
select * from dual where sysdate between '1-jan-2009' and '31-jan-2009'
                                                          *
ERROR at line 1:
ORA-01843: not a valid month
Re: Query to become a new table [message #396411 is a reply to message #396408] Mon, 06 April 2009 08:52 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Joy_division,

I really don't know another way. If I understand your advice correctly, I should use [sysdate between '1-jan-2009' and '31-jan-2009']? But this table has lots of fields/columns with dates. How would it know which field/column to restrict the date from?

Blackswan,

From your advice, where I have [WHERE date between '1-jan-2009' and '31-jan-2009'], I should use the below right?

from test
where eff_date between to_date('01-JAN-2009', 'DD-MON-YYYY') and to_date('31-JAN-2009', 'DD-MON-YYYY')
Re: Query to become a new table [message #396415 is a reply to message #396411] Mon, 06 April 2009 09:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, Jen. You have it correct now Smile
Re: Query to become a new table [message #396416 is a reply to message #396415] Mon, 06 April 2009 09:08 Go to previous message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Thank you so much! I'll will get this new language one day.
Previous Topic: Cursor
Next Topic: Split a string
Goto Forum:
  


Current Time: Mon Dec 05 08:38:47 CST 2016

Total time taken to generate the page: 0.05214 seconds