Substr Order by
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20143
Printed Date: 03 May 2024 at 5:58pm
Topic: Substr Order by
Posted By: takugian
Subject: Substr Order by
Date 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!!!
|
Replies:
Posted By: lockwelle
Date 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
|
Posted By: takugian
Date 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.
|
Posted By: lockwelle
Date 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?
|
Posted By: takugian
Date 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!
|
|