| Author |
Message |
takugian
Newbie
Joined: 17 Oct 2012
Online Status: Offline
Posts: 24
|
   Topic: Substr Order by
Posted: 22 Oct 2013 at 12:33am |
|
Hi, guys!
Are you okay?
I need to order some registers. The values have the following format...
Number [1-6] . Number [1-6] . Number [1-N] . Number [1-N]
For example...
1.4.10.5
3.2.4.4
1.1.1.1
Etc.
Well, a simple order by will not work. This field is a VARCHAR.
Because of that, I'm using SUBSTR (Oracle) to order my registers. I take a part of the field and I order it, after I take other part, etc.
It seems that Crystal does not understand this command.
I need some idea to order these registers...Substr is not working, what could I use???
Thanks!!!
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:15am |
|
mid is the crystal version of substring.
you could create a formula...or a set of them, and let CR group/sort them.
the formula would look like:
local numbervar dot := instr({table.field}, ".");
val(left({table.field},dot-1))
of course for the second field it would be something like:
local numbervar dot1 = instr({table.field), "."};
local numbervar dot2 = instr(dot1 + 1, {table.field}, ".");
val(mid({table.field}, dot1 + 1, dot2 - dot1 - 1))
you may need to check the math, but it should be close.
HTH
|
|
IP Logged |
|
takugian
Newbie
Joined: 17 Oct 2012
Online Status: Offline
Posts: 24
|
Posted: 22 Oct 2013 at 5:29am |
|
Well, I don't how I use this formula.
Look at my order by...
ORDER BY TO_NUMBER(SUBSTR(DS_IDMISSAO, 1, 3)),
TO_NUMBER(SUBSTR(DS_IDMISSAO, 5, 7)),
R.id_recomendacao;
It works on Crystal and on Oracle. But when my application Java calls the report, don't!
The question is... How do I get a result of a select and I order it as I want???
I know how to order in desc, asc and original using a formula. In a chart, for example.
But how to order in a different method, I don't know.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:32am |
|
if you order it in the select, in CR for the group you can select 'In Original Order' then CR won't resort it.
Does that help?
|
|
IP Logged |
|
takugian
Newbie
Joined: 17 Oct 2012
Online Status: Offline
Posts: 24
|
Posted: 22 Oct 2013 at 6:10am |
|
Unfortunately, it does not work.
As I said, the my select works and the my report works using CR. But it does not work when Java calls the report.
It seems that the report's server does not understand the command SUBSTR.
I work at a big company in my country, I do not have access to the server where the reports are...
Thanks!
|
|
IP Logged |
|
|
|
