Home » SQL & PL/SQL » SQL & PL/SQL » A Query Which Looks Ahead....
A Query Which Looks Ahead.... [message #298504] Wed, 06 February 2008 04:33 Go to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Hi,

I have a table of work, for each row there is a frequency column, and this is the number of days this work is repeated

i.e. if it was 24 every 24 days the work woruld be carried out

I need to write a query which can return a row for each time the work is to be carried out in the space of the next year

Is this possible??

Thanks
Re: A Query Which Looks Ahead.... [message #298508 is a reply to message #298504] Wed, 06 February 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: A Query Which Looks Ahead.... [message #298520 is a reply to message #298504] Wed, 06 February 2008 04:53 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Ok, I have created a simple script to create and populate a simple table to explain what I mean Razz (Please se the attached Script)

So the table has 10 rows, each row has a different frequency, for eaxmple WorkId 4 has a frequency of 28

So every 28 days this work would need to be carried out, and so in my look ahead query of one year this Work ID and Description would need to be printed 15 times.

Create table LookAhead(WorkId int, Description Char(100), Frequency int)

Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (1, 'Description 1', 25);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (2, 'Description 2', 26);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (3, 'Description 3', 27);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (4, 'Description 4', 28);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (5, 'Description 5', 29);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (6, 'Description 6', 30);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (7, 'Description 7', 31);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (8, 'Description 8', 32);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (9, 'Description 9', 33);
Insert into LookAhead (WORKID, DESCRIPTION, FREQUENCY) Values (10, 'Description 10', 34);
COMMIT;


[Edit: Included the File content and added the CODE tag by Raj ]
[Edit: Remove 80 useless spaces in strings by MC]

regards
  • Attachment: Script.sql
    (Size: 1.90KB, Downloaded 124 times)

[Updated on: Wed, 06 February 2008 05:22] by Moderator

Report message to a moderator

Re: A Query Which Looks Ahead.... [message #298529 is a reply to message #298504] Wed, 06 February 2008 05:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
Work ID and Description would need to be printed 15 time

If I understood the problem, Workid 4 and its description should be "printed" 15 times.

[ But how come 365/28 becomes 15 ? ]

There could be many ways to do . One way would be to

1. Create a inline view for rows generated
2. Join this to LookAhead.Workid
3. Apply suitable where condition


Thumbs Up
Rajuvan

[Updated on: Wed, 06 February 2008 05:17]

Report message to a moderator

Re: A Query Which Looks Ahead.... [message #298531 is a reply to message #298520] Wed, 06 February 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your Oracle version number is mandatory for this.

Regards
Michel
Re: A Query Which Looks Ahead.... [message #298534 is a reply to message #298504] Wed, 06 February 2008 05:28 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I need to write a query which can return a row for each time the work is to be carried out in the space of the next year




may you define it by a test case?

regards,
Re: A Query Which Looks Ahead.... [message #298537 is a reply to message #298504] Wed, 06 February 2008 05:32 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Hey,

I'm using oracle 9.2.0.5
Re: A Query Which Looks Ahead.... [message #298538 is a reply to message #298534] Wed, 06 February 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what OP posted a test case?
Just output is missing but it should quite long and I think:
Quote:
for eaxmple WorkId 4 has a frequency of 28
So every 28 days this work would need to be carried out, and so in my look ahead query of one year this Work ID and Description would need to be printed 15 times.

is clear. Doesn't it? (even if 365/28 is 13 and not 15)

Regards
Michel
Re: A Query Which Looks Ahead.... [message #298540 is a reply to message #298504] Wed, 06 February 2008 05:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

So what ?

Row generator , inline view , Analytic function etc will work on Oracle 9.2.0.5

Thumbs Up
Rajuvan.

[Updated on: Wed, 06 February 2008 05:36]

Report message to a moderator

Re: A Query Which Looks Ahead.... [message #298541 is a reply to message #298504] Wed, 06 February 2008 05:37 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Could you please give me an example of how it could be done using an inline statement?

Thanks you
Re: A Query Which Looks Ahead.... [message #298543 is a reply to message #298504] Wed, 06 February 2008 05:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Show us what did you tried so far .

Thumbs Up
Rajuvan.

[Updated on: Wed, 06 February 2008 05:39]

Report message to a moderator

Re: A Query Which Looks Ahead.... [message #298544 is a reply to message #298540] Wed, 06 February 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So what ?

Nothing, I asked this.

Regards
Michel
Re: A Query Which Looks Ahead.... [message #298555 is a reply to message #298504] Wed, 06 February 2008 06:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
So what ?


Question was really to the OP when he replied
Quote:
Hey,

I'm using oracle 9.2.0.5


Though it was a answer to your reply

Quote:
Your Oracle version number is mandatory for this.


Thumbs Up
Rajuvan.




Re: A Query Which Looks Ahead.... [message #298707 is a reply to message #298555] Thu, 07 February 2008 04:17 Go to previous messageGo to next message
prawinrajkumar
Messages: 2
Registered: February 2008
Junior Member
will this help?

SELECT workid,description,frequency,Round(365/frequency) FROM lookahead a,(SELECT LEVEL lvl FROM dual CONNECT BY LEVEL<=365) b
WHERE b.LVL<=Round(365/a.frequency)
ORDER BY 1;

regards,
Prawin.
Re: A Query Which Looks Ahead.... [message #298770 is a reply to message #298707] Thu, 07 February 2008 11:09 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

2/ Don't give solution in newbie forum, just give hint/clue/way to find a solution

Regards
Michel
Previous Topic: ORA-00900: invalid SQL statement
Next Topic: Is it possible?
Goto Forum:
  


Current Time: Mon Dec 05 12:35:42 CST 2016

Total time taken to generate the page: 0.16640 seconds