select "_Pnumber", substring("_Description",1,80)
from pub."_Sql_Qplan"
where "_Pnumber" = (select max( "_Pnumber" )
from pub."_Sql_Qplan"
where "_Ptype" > 0 );
[COLOR=#943634][FONT="]SELECT [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.knote, [/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.kdoctype, [/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.contact[/FONT][/COLOR]
[COLOR=#943634][FONT="]FROM [/FONT][/COLOR]
[COLOR=#943634][FONT="] PUB.order [/FONT][/COLOR]
[COLOR=#943634][FONT="]INNER JOIN [/FONT][/COLOR]
[COLOR=#943634][FONT="] PUB.orderdoc [/FONT][/COLOR]
[COLOR=#943634][FONT="]ON [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kco = orderdoc.kco AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kdoctype = orderdoc.korddoctype AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.knote = orderdoc.kordnote[/FONT][/COLOR]
[COLOR=#943634][FONT="]WHERE [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kco = 1 AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kdaccount = 'S1234' AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.orderstatus = 1 AND[/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.kdoctype = ‘GO’[/FONT][/COLOR]
[COLOR=#943634][FONT="]SELECT [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.knote, [/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.kdoctype, [/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.contact[/FONT][/COLOR]
[COLOR=#943634][FONT="]FROM [/FONT][/COLOR]
[COLOR=#943634][FONT="] PUB.order [/FONT][/COLOR]
[COLOR=#943634][FONT="]INNER JOIN [/FONT][/COLOR]
[COLOR=#943634][FONT="] PUB.orderdoc [/FONT][/COLOR]
[COLOR=#943634][FONT="]ON [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kco = orderdoc.kco AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kdoctype = orderdoc.korddoctype AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.knote = orderdoc.kordnote[/FONT][/COLOR]
[COLOR=#943634][FONT="]WHERE [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kco = 1 AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.kdaccount = 'S1234' AND [/FONT][/COLOR]
[COLOR=#943634][FONT="] order.orderstatus = 1 AND[/FONT][/COLOR]
[COLOR=#943634][FONT="] orderdoc.contact like 'Martin%'[/FONT][/COLOR]
It is pointless to compare SQL in the Progress editor with that from Excel. The former is SQL-89 executed by the AVM and the later is SQL-92 executed by an entirely separate process. If the SQL-89 does what you expect and SQL-92 doesn't, it is a pure fluke ... in fact, more likely a bug in SQL-89.
Among other things, SQL-89 is going to use ABL index resolution at compile time and SQL-92 using run-time optimization. Run UPDATE STATISTICS recently? If not, you are asking SQL to work blind.
From Solaris root prompt:
root@server # sqlexp -db DBName -H HostName -S ServiceName
Connecting user "root" to URL "jdbc:datadirect:openedge://HostName:-
1;databaseName=DBName;serviceName=ServiceName"... (8920)
SQLExplorer>update table statistics and index statistics
1> and all column statistics for PUB.mytable;
Update count = 0.
SQLExplorer>commit;
SQLExplorer>