Hi;

I have a sql report and the result looks like the following group by Resource and business role

Resource Business Role Function Role
Oracle FR 1
Oracle FR 2
Oracle FR 3
Oracle AP Accountant FR 4
Oracle AP Manager FR 5

However i want to see Business Role sort first and put all the records with empty business role at the end of report. Is it feasible?

Resource Business Role Function Role
Oracle AP Accountant FR 4
Oracle AP Manager FR 5
Oracle FR 1
Oracle FR 2
Oracle FR 3

Thanks

-Maria

This message has been edited. Last edited by: Kerry,

Not in an SQL query. In a WF request you can change the sort order however you want it. SQL just doesn't handle it that easily.

If you have the following structure:

SQL PREPARE SQLOUT...
Select...;

TABLE FILE SQLOUT

Then you can just add a DEFINE FILE SQLOUT and add a defined field that sets the value of the business role to ZZZ when blank and sort by that field with a NOPRINT.

A simple sort using BY won't solve the issue because "blank" is the lowest value, thus appearing first. You can use BY HIGHEST to make the blank values come last, but that would also reverse the order of your other business role values so MANAGER would appear first, followed by Accountant, then blanks.

Hey, Darin;

Thanks for your help. How do i do evaluation to say if role is empty, then set to 'ZZZ'

DEFINE FILE SQLOUT
FILL/A1 = 'ZZZ';
END

Thanks

-Maria

You can do it in SQL, as in a SQL Passthrough. Just use the "ORDER BY" (selected field)

SELECT T1.'AmountSpent',T1.'CategoryID' 
FROM TFNexpenses T1
ORDER BY T1.'CategoryID';

OR, are you saying that you want an alpha-numeric sort instead of a numberic-alpha sort?

quote:

Thanks for your help. How do i do evaluation to say if role is empty, then set to 'ZZZ'

That's a pretty basic question! From your last post, it looks like you might need to read up on DEFINE syntax. It does go in a DEFINE (or COMPUTE). First, is the value empty (null) or is it blank? They are NOT the same thing. I'm guessing it's blank.

BUS_ROLE/A20=IF BUSINESS_ROLE EQ ' ' THEN 'ZZZ' ELSE BUSINESS_ROLE;

You've got to make the format of the defined field match the format of your field and/or static value.

Hi, Darin;

Thanks a lot for your help. It works.