BQT - Sort result set before comparison

[jpmat]

Dear all

Is there a way to tell to BQT to order result set before comparison with expected result ? I have an SQL query with random order of results.

I know I could use ORDER BY in the SQL but I don't like much this solution because it impacts the query plan.

TIA

Jean-Pierre

[shawkins]

BQT does not have a notion of order insensitive results.  You could log an enhancement request for that or a workaround would be to use an anonymous procedure block as the query:

begin

insert into #temp ... desired query ...;

select * from #temp order by ... desired ordering ...;

end

That will separate the affect of the sort.

[van.halbert]

Jean-Pierre,

Currently, there's no option to do a sort after retrieving the results.    But I can see your reason for wanting one.   If you could create an issue in the github instance, we can take a look at it.

Thanks.

Van

[jpmat]

Hi Steven

Could you give a short example of use of anonymous procedure block ? I have not been able to find by myself.

Thanks in advance

[jpmat]

Hi Van

vhalbert/SQLBulkQueryTool · GitHub

I cannot create issue in GitHub. It looks like "Issue Creation" is not enabled on it.

Thanks

Jean-Pierre

[shawkins]

Example sql for an anonymous block is shown above, it's just begin / end wrapping a series of statements.  You can issue it the same way you would a regular sql statement.  See also DML Commands - Teiid 9.0 (draft) - Project Documentation Editor

[jpmat]

I've tried, with SQUirreL:

BEGIN

       select *

       from dimME_OCN.MATERIAL ma

       where ma.material_number = 10131227

END

-- => Error TEIID31100 Parsing error: Encountered "ma.material_number = 10131227 [*]END[*]"

SELECT *

FROM (

       BEGIN

              select *

              from dimME_OCN.MATERIAL ma

              where ma.material_number = 10131227

       END) as t1

-- => Error TEIID31100 Parsing error: Encountered "* FROM ( [*]BEGIN[*] select *" at line 3, column 9.

SELECT *

FROM ( EXEC

       BEGIN

              select *

              from dimME_OCN.MATERIAL ma

              where ma.material_number = 10131227

       END) as t1

-- => Error TEIID31100 Parsing error: Encountered "FROM ( EXEC [*]BEGIN[*] select *" at line 3, column 9.

I'm using JBoss DV 6.2 with Teiid 8.7. Is it the reason it doesn't work ?

[shawkins]

> I'm using JBoss DV 6.2 with Teiid 8.7. Is it the reason it doesn't work ?

Yes, the first one is lacking a ; at the end of the statement in the block.

... ma.material_number = 10131227;

END

On the others you can't nest an anonymous procedure block because it is a top level command not a query expression.

[jpmat]

With semicolon ';' I got:

It seems SQuirreL executes only content before the semicolon. But with same query in BQT, it works well. Thanks for the help.

May I ask you what is the JDBC client you use yourself ?

[shawkins]

In squirrel under the sql tab in the session properties, there is a setting for the statement separator.  You have to use something other than a semicolon.

> May I ask you what is the JDBC client you use yourself ?

SQuirreL or Teiid Designer mostly.

[jpmat]

Thanks. The anonymous procedure does what I'd like in BQT.

I have also create Jira entry as you requested:

TEIID-3877