Re: iNav Run SQL Scripts

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/26/2018
11:18:54 AM:

1 - Is there a copy of IM02T in your library list which would cause it

to

use that instead of whatever is in CURRENT SCHEMA? Some SQL naming

thing?

2 - Some weird thing where it doesn't close IM02T and uses an old open
path? Something a hack may resolve like adding FOR FETCH ONLY, or WITH

NC

or some such thing?
3 - If you run it line by line are there any indications in the Messages

that something didn't quite work right? Like it never changed current
schema?

I can't think of any way to change
from IM02T
to
from current_schema.IM02T

I got it to work by creating an alias using dynamic SQL -- as follows. My
only question is... Is there a shorter way to do this error checking for
each SQL statement? Thanks.

If CompanyStatus IN ('Active','Inactive')
And CompanyLibrary > ' ' Then
Set Str1 = 'Create Alias QTEMP/IM02TA for
'||Trim(CompanyLibrary)||'/IM02T';
Execute Immediate Str1;
Get Current Diagnostics Condition 1 CurState =
Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
If Substr(CurState,1,2) = '01' Then
Drop Alias QTEMP/IM02TA;
Iterate Company_Loop;
End If;
Set Str1 = 'Create alias for
'||Trim(CompanyLibrary)||' failed.'
|| ' (' || CURSTATE || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

Select Count(*) into RowCount from QTEMP/IM02TA;
Get Current Diagnostics Condition 1 CurState =
Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
Drop Alias QTEMP/IM02TA;
Set Str1 = 'Select count from
'||Trim(CompanyLibrary)||' failed.'
|| ' (' || CURSTATE || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

Insert into QTEMP/DLC_TEMP_TABLE
Values(CompanyNumber, 'IM02T', RowCount);
Get Current Diagnostics Condition 1 CurState =
Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
Drop Alias QTEMP/IM02TA;
Set Str1 = 'Insert count for
'||Trim(CompanyLibrary)||' failed.'
|| ' (' || CURSTATE || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;

Drop Alias QTEMP/IM02TA;
Get Current Diagnostics Condition 1 CurState =
Returned_SqlState;
If Substr(CurState,1,2) <> '00' Then
Set Str1 = 'Drop alias for
'||Trim(CompanyLibrary)||' failed.'
|| ' (' || CURSTATE || ')';
Signal SqlState '88W00' Set Message_Text = Str1;
End If;
End If;


Sincerely,

Dave Clark