| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
  
Posted: 23 Jan 2013 at 8:07am |
assuming you cannot use a stored proc or a view as your data source (easier solution) you can write the sql as a commadn object and use that as your source
SELECT TOP (20) field1, field2, Field3, datefield
FROM table
ORDER BY datefield DESC
That limits your source data to the top 20 based on date sort.
once that is brought into the report you can reort those top 20 anyway you want
|
 IP Logged |
|
|
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 23 Jan 2013 at 10:33am |
That is actuallly where I had started. This was a sql that I copied over. It was suppose to sort by the location field but never worked right. I tried with and without the order by in the sql and neither one worked right. The problem with doing the TOP 20 is I need to have it use the parameter I created.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 11:14am |
use the command and you can add a parameter in the command. It is on the right hand side of the command screen. Create the param and then insert it into the comand sql. Thsi will then change the record set returned at run time
This uses a param called "AmountToReturn"
SELECT TOP ({?AmountToReturn}) field1, field2, Field3, datefield
FROM table
ORDER BY datefield DESC
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 23 Jan 2013 at 12:32pm |
When I add "TOP ({?Number_Of_Records})" into the select statement I get an informix syntax error.
This is my Select statement:
select TOP ({?Number_Of_Records})a.opid,o.name,
a.from_location[1,8] frm,
a.date[9,10]||':'||a.date[11,12] ptime,
b.to_location[1,7] to
KevV Edited by KevV - 23 Jan 2013 at 12:35pm
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Jan 2013 at 4:06am |
|
I do not see any join from a to b, nor is a or b identified as the actual table name and hter is no order by.
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 24 Jan 2013 at 5:44am |
This is the full sql statement:
select TOP ({?Number_Of_Records}) a.opid,o.name,a.from_load_id,
a.from_location[1,8] frm,
a.date[9,10]||':'||a.date[11,12] ptime,
b.to_location[1,7] to
from audits a,audits b,operator o
where a.date[1,10] >= '2013012108'
and a.date[1,10] < '2013012110'
and a.transaction_type = 'q'
and a.transaction_subtyp = 'W1'
and b.transaction_type = 'c'
and b.transaction_subtyp = 'PW'
and b.date > a.date
and a.from_load_id = b.from_load_id
and a.opid = b.opid
and b.opid = o.opid
and a.from_location != b.to_location
and b.to_location[1] = 'G'
order by 6;
KevV
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Jan 2013 at 6:05am |
to trouble shoot
1. Did you create the param in the param list on the right? If not do that first and then double click on it to insert it into the sql. if not
2. replace the {?Number_Of_Records} with 20 and see if it runs OK. (my guess is it still fails). If it fails try and create that same SQL statement sql studio manager and fix it up there/ Then just copy it over to the command. But at least you know it is not the top N that is the problem.
Someone else better versed in SQL might see the issue and post too ...
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 24 Jan 2013 at 6:15am |
I did create the param first and I also tried replacing with a fixed number and your right it does still fail. If I leave that out of the statement and just put it in the condition to suppress the section it works. I just cant sort it by the location.
KevV.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Jan 2013 at 6:29am |
|
you are adding this in at an 'add command' window, not in the select statement formula window, correct?
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 25 Jan 2013 at 9:01am |
correct. I do not havy aything in the select statement.
KevV Edited by KevV - 25 Jan 2013 at 9:01am
|
| IP Logged |
|
|
|
