| Sorting issue [message #870785] |
Wed, 09 May 2012 12:24  |
Eclipse User |
|
|
|
I have a sorting problem in BIRT or maybe my SQL. I'm joining two data sets into a joint data set. What I want the report to do is to sort the report by Contractor. I have generated the report and I get everything I want the only problem is that it doesn't sort correctly. I will get contractors that begin with A,B,C but then get a contractor that starts with S and then it will continue down the list alphabetically. I have attached a word document with what the report should look like and a screen shot of the joint data set from BIRT.
This is DATA SET 1
SELECT SCV.SC_CONTRACTOR AS CONTRACTOR,
SCV.SC_DESC AS SC_DESC,
SCV.SC_CONTRACT AS CC,
SCV.SC_TOTAL AS CONTRACT_AMOUNT,
SUM (SCI.SCI_TOTAL) AS SPENT
FROM SERVICECONTRACT AS SCV
LEFT JOIN SERVICECONTRACT_INVOICE AS SCI
ON SCV.SC_CONTRACT = SCI.SCI_CONTRACT_NO
WHERE SCV.SC_STATUS = 'AWARDED'
AND SCI.SCI_STATUS NOT IN ('REJECTED', 'VOID')
GROUP BY SCV.SC_CONTRACTOR,
SCV.SC_DESC,
SCV.SC_CONTRACT,
SCV.SC_TOTAL
This is DATA SET 2
SELECT SCV.SC_CONTRACTOR AS CONTRACTOR,
SCV.SC_DESC AS SC_DESC,
SCV.SC_CONTRACT AS CC,
SCV.SC_TOTAL AS CONTRACT_AMOUNT
FROM SERVICECONTRACT AS SCV
WHERE SCV.SC_STATUS = 'AWARDED'
ORDER BY SCV.SC_CONTRACTOR ASC
|
|
|
|
| Re: Sorting issue [message #870801 is a reply to message #870785] |
Wed, 09 May 2012 14:07  |
| Eclipse User |
|
|
|
Is the output in the word doc from dataset 2 above? And is this actual output or a reproduction that you typed into word?
If this is dataset 2 example output then my my first thought would be to look at the data in the SCV.SC_CONTRACTOR field and see if there are leading or trialing spaces, or maybe extra spaces between the word "Contractor" and the letter that could be screwing up the sort.
If this is output from dataset 1 then that output is not sorted by contractor.
|
|
|
Powered by
FUDForum. Page generated in 0.30883 seconds
Home