Querying Infostore, need to return characters

[Bobbber]

Hi all (CE9),

I want to query the InfoStore and require the result to be ordered by SI_NAME. However, I only want it to take into account the 2nd character in the name.

This query :

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS ORDER BY SI_NAME

Returns something like the following :

SI_ID SI_NAME
----------------------------
1 RA1
2 TB2
3 WA3

I want :

SI_ID SI_NAME
----------------------------
1 RA1
3 WA3
2 TB2
^ this character to be the sort order.

I've tried things like :

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS ORDER BY mid(SI_NAME, 1, 1)

But this is not a 'valid query' according to the CSP Query Builder.

Any ideas folks?!?!?!

Thanks in advance!

Rob





Stay Blue, Bob. x

 

[KingfisherINC]

Can not be done directly in the SQL (AFAIK). Crystal SQL only supports a small sub set of "standard" SQL (no joins, no sub queries, limited functions).

You would need to post process the records.

 

[synapsevampire]

Try:

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS ORDER BY substring(SI_NAME, 1, 1)

The above works on my Oracle back end database, and should work for SQL Server.

Mid would not work as you need to use the correct syntax for the database, not for Crystal.

Since you neither shared the version of CE nor the database housing the CE objects, I can't give you a specific answer, please include technical information when posting.

-k

 

[synapsevampire]

Oooops, I though about it afterwards and realized that you wanted the 2nd character, your example had 1,1 so I incorrectly used that:

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS ORDER BY substring(SI_NAME, 2, 1)

-k

 

[synapsevampire]

Scratch that, I should have examined the ouput more carefully, it didn't work...

-k

 

[synapsevampire]

Try using Enterprise Manager directly to create the SQL, or you can even build a report in Crystal to query this.

Then you can create a SQL Expression to return the second character (though you might have it start at the second character rather than just use the 2nd character).

I just created both and I tested, they worked fine.

Here's the formula I used in Crystal to allow for a field to sort by:

mid({APS_InfoObjects.ObjName},2)

-k

 

[mdwyer]

Using a SQL expression that is valid for varchar2 columns in the database is not likely to work on the objects retrieved by CE. If CE doesn't support the syntax, it isn't supported. Your substr() function is valid Oracle SQL syntax, and CE doesn't complain - it just ignores it. (It should reject what it won't use, of course, but it doesn't.)