need help on ORDER BY clause

[system]

Hi All,

Can we use ORDER BY clause in creation of a object in universe?

Thanks & Regards,

---

Rakesh_K (BOB member since 2007-12-11)

[system]

Not in an object, no. The only thing you can “touch” is the select and the from and the where.

The order by is generated by the query panel, if needed.

What are you trying to accomplish, exactly?

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Hi,
As said by Dave what you exactly want to achieve this.
I have sorted in deisgner object when I created one object for RANK function. Also in this you can put (i.e. sorting) only on measures.

so explain in detail why you need order by clause.

Hope this is not an interview question.

---

Omkar Paranjpe (BOB member since 2006-02-13)

[system]

Hi Dave/Om,

Actually we are creating Analytics using EPM, into that we have to show the date as DD-MON-YYYY (eg. 02-Mar-2008) or MON-YYYY (eg. Mar-2008).

We are using Oracle 10 g as database. In Database our default date format is mm/dd/yyyy (eg 03-02-2008).

To achive this we are using following code:

TO_CHAR(‘03-02-2008’, ‘DD-MON-YYYY’)

and storing it into another column. and also using order by clause to arrange according to date not as alphabatical.

but when we are creating an object in designer the order got changed because now new date column is of char datatype, so it will show alphabatically. This effects our analytics, because there 01-April-2008 comes first instead of 01-Mar-2008.

Now can you please guide how to achieve the required format.

Thanks & Regards,

---

Rakesh_K (BOB member since 2007-12-11)

[system]

@khandelwal85

…now new date column is of char datatype, so it will show alphabatically. This effects our analytics, because there 01-April-2008 comes first instead of 01-Mar-2008.

Why not fix the real problem and use a standard date format instead of a ridiculous character string?

---

Steve Krandel (BOB member since 2002-06-25)

[system]

Dear Steve,

its client requirement, so we show date in default format, he requires in different format, anyways the problem got solved.

cheers…

---

Rakesh_K (BOB member since 2007-12-11)

[system]

I have to show date in prompt LOV without time part. for example,

12/29/2008 instead of 12/29/2008 12:00:00:00 AM.

Object Format works for report not for LOV.

Any idea?

Thanks

Prashant

---

Prashant Bharti (BOB member since 2004-03-03)

[system]

Answered in the FAQ here:

FAQ: Designer

How can I eliminate the time portion from my dates and my date lists of values? Read this till I have time to summarize for the FAQ’s ;-)… Cindy Clayton (BOB member since 2002-06-11)

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Prashaant ,

Your issue has been many times in this forum. Try doing some search .Below is a link to one such thread:

Convert mm/dd/yyyy hh:ss:mm a to mm/dd/yyyy

I have a date dimesnion in universe which is of the format mm/dd/yyyy hh:mm:ss a, I’d like to convert the dimension to mm/dd/yyyy. I use sql server as backend. tried using Datepart(), is ther other function which gives me to mm/dd/yyyy rnola (BOB member since 2006-03-26)

---

rimpa (BOB member since 2008-04-14)

[system]

Prashant,

Just you need to change object format in universe itself to dd/mm/yyyy.

Best of Regards,

---

Rakesh_K (BOB member since 2007-12-11)