Home » SQL & PL/SQL » SQL & PL/SQL » is it possible to write "ORDER BY QUARTERLY"
is it possible to write "ORDER BY QUARTERLY" [message #254639] Fri, 27 July 2007 06:05 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
Is it possible to get value in "order by quarterly"
ie. for eg:
select field1,
field 2,..
from table 1,table2...
order by QUARTERLY;
That means depending on Q1,Q2,Q3,Q4 the data should come in the order.
I am able to get either of the quarters.



select a.name,
       a.actno,
       a.countrycd,
       a.curcd,
       (to_char(add_months(to_date(b.globtab_tbsdy,'yyyyMMDD'),3),'Q'))
from   ifmr a,
       globtab b
where a.actno='00062100001111'
order by (to_char(add_months(to_date(b.globtab_tbsdy,'YYYYMMDD'),3),'Q'))



thx,
Ajay

[Updated on: Fri, 27 July 2007 06:06]

Report message to a moderator

Re: is it possible to write "ORDER BY QUARTERLY" [message #254641 is a reply to message #254639] Fri, 27 July 2007 06:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What exactly is your question? What is the outcome of that query, and how does this not match your desired output?
Re: is it possible to write "ORDER BY QUARTERLY" [message #254646 is a reply to message #254641] Fri, 27 July 2007 06:22 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,

the output will be like below,
     NAME   ACTNO    COUNTRYCD CRCD RANGE OF QUARTER
     -----  ------   --------- ---- ----------------
     XYZ    ABC123    USD       21      1ST QUARTER ----> DATE may be any eg:20070105
     ABC    ABC124    USD       23      1ST QUARTER ----> DATE may be any eg:20070205
     ABC    ABC125    USD       23      1ST QUARTER ----> DATE may be any eg:20070205
     ABC    ABC125    USD       23      1ST QUARTER ----> DATE may be any eg:20070305

     XYZ    ABC111    USD       21      2ST QUARTER ----> DATE may be any eg:20070405
     ABC    ABC112    USD       23      2ST QUARTER ----> DATE may be any eg:20070505
     ABC    ABC113    USD       23      2ST QUARTER ----> DATE may be any eg:20070505

     ABC    ABC114    USD       23      3RD QUARTER ----> DATE may be any eg:20070705
     XYZ    ABC111    USD       21      3RD QUARTER ----> DATE may be any eg:20070805
     ABC    ABC112    USD       23      3RD QUARTER ----> DATE may be any eg:20070805

     ABC    ABC113    USD       23      4ST QUARTER ----> DATE may be any eg:20071005
     ABC    ABC114    USD       23      4ST QUARTER ----> DATE may be any eg:20071205


here the data is sorted by depending on the quarters (1st ,2nd,3rd, 4th)
thx,
Ajay

Re: is it possible to write "ORDER BY QUARTERLY" [message #254647 is a reply to message #254646] Fri, 27 July 2007 06:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if you order by to_date(b.globtab_tbsdy,'YYYYMMDD') ?
Re: is it possible to write "ORDER BY QUARTERLY" [message #254651 is a reply to message #254647] Fri, 27 July 2007 06:35 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
That is my string format date... YYYYMMDD, Presently i am able to get only one quarter results but i need all quarter results in asingle query.

that means in simple i want query ORDER BY QUARTER

[Updated on: Fri, 27 July 2007 06:37]

Report message to a moderator

Re: is it possible to write "ORDER BY QUARTERLY" [message #254653 is a reply to message #254651] Fri, 27 July 2007 06:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, you lost me completely.
I don't understand.
Re: is it possible to write "ORDER BY QUARTERLY" [message #254654 is a reply to message #254653] Fri, 27 July 2007 06:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, nice, update your response while I'm typing Smile

The fact that only 1 quarter of data is returned has nothing to do with an order by. the WHERE clause determines the resultset

Order by quarter = order by date (as long as you're in the same year)

[Updated on: Fri, 27 July 2007 06:40]

Report message to a moderator

Re: is it possible to write "ORDER BY QUARTERLY" [message #254658 is a reply to message #254653] Fri, 27 July 2007 06:46 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member

Frank,

suppose if we wil take a query,
      select  empno,
              empname,
              empdesig
      from    emptbl
      where   empsal > 20000
      order by empno;

similarly 
    
      select  empno,
              empname,
              empdesig
      from    emptbl
      where   empsal > 20000
      order by joinDate;
                        
               i am saying about joinDate 
 
for suppose join date is 20070727----> this one comes under 3rd quarter.
similarly if another persons join date is 20070220 -----> this one comes in 1st quarter...

i want the emp details sorted by their joinDate with regarding quarter(1ST quarter and 2nd quarter and 3rd quarter and 4th quarter)..in order

thx
Ajay


[Updated on: Fri, 27 July 2007 06:47]

Report message to a moderator

Re: is it possible to write "ORDER BY QUARTERLY" [message #254660 is a reply to message #254658] Fri, 27 July 2007 06:50 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You are probably going to spend several more posts trying to explain your requirements. You will probably find it much faster to provide create table and insert scripts to create some representative data and also supply expected results along with an explanation as to why 'these' are the expected results.
Re: is it possible to write "ORDER BY QUARTERLY" [message #254669 is a reply to message #254660] Fri, 27 July 2007 07:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Indeed. And in the same post, please explain how a recordset ordered by joindate will not be ordered by quarter.
Re: is it possible to write "ORDER BY QUARTERLY" [message #254689 is a reply to message #254639] Fri, 27 July 2007 09:05 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You have a major problem with your query, you have a cartisan join. In the below query replace the question marks with the column that tie the two tables together.

select a.name,
       a.actno,
       a.countrycd,
       a.curcd,
       decode(to_char(to_date(b.globtab_tbsdy,'yyyyMMDD'),'Q'),'1','1st Quarter',
                                                               '2','2nd Quarter',
                                                               '3','3rd Quarter',
                                                               '4','4th Quarter') Quarter
from   ifmr a,
       globtab b
where  a.??? = b.???
order by to_char(add_months(to_date(b.globtab_tbsdy,'YYYYMMDD'),3),'Q'),a.actno;

[Updated on: Fri, 27 July 2007 09:05]

Report message to a moderator

Previous Topic: Ref cursor
Next Topic: Distinct output
Goto Forum:
  


Current Time: Sat Dec 10 05:02:50 CST 2016

Total time taken to generate the page: 0.07944 seconds