The parameter replacement is very simplistic. Wherever it sees $prm_abc it
replaces it with the value (eg. 1), irrespective of what string precedes or
follows the $prm_abc. So if you have $prm_abc1 you will get 11. It does this
before searching for the next parameter ($prm_abc1).

The solution is to use non-ambiguous parameter names such as (for instance) prm1
and prm2.

Phil.

--- In aqt@yahoogroups.com, Michaël Willemot <michael@...> wrote:
>
> When using $ as parameter delimiter and given query:
>
> --aqt setparm,parm=prm_abc,value=1
> --aqt setparm,parm=prm_abc1,value=2
> select 1 from sysibm.sysdummy1 where $prm_abc1=2
>
> i'd expect aqt to first replace prm_abc1 and then prm_abc, but the
> resulting query is:
>
> --aqt setparm,parm=prm_abc,value=1
> --aqt setparm,parm=prm_abc1,value=2
> select 1 from sysibm.sysdummy1 where 11=2
>
>
> I couldn't figure out why my query didn't return the correct
> result...until i saw that my parameter wasn't properly being replaced
> because a shorter-one was previously defined and was still in the list.
>
> Is this behavior by design or is it possible to first replace the longest
> parameter-names?
>
> thanks,
>
> Michaël Willemot
>
> --
> Michaël Willemot
> michael@...
> http://www.sotto.be
>

The issue is probably something called Data Execution Protection. See
http://www.querytool.com/help/1173.htm for further information.

Phil.

--- In aqt@yahoogroups.com, Abhishek Singhal <abhishek_singhal01@...> wrote:
>
> Hi,
>
> I just did my AQT setup and I'm getting the following error: "The exception
generated was c0000005 at address 0015CEA4 (<nosymbols>)"
>
> Any pointers how to resolve it?
>
> Regards,
> -----------------------------
> Abhishek Singhal
> Infosys Technologies Ltd
> Bhubaneswar
>
>
>
> [Non-text portions of this message have been removed]
>

When using $ as parameter delimiter and given query:

--aqt setparm,parm=prm_abc,value=1
--aqt setparm,parm=prm_abc1,value=2
select 1 from sysibm.sysdummy1 where $prm_abc1=2

i'd expect aqt to first replace prm_abc1 and then prm_abc, but the
resulting query is:

--aqt setparm,parm=prm_abc,value=1
--aqt setparm,parm=prm_abc1,value=2
select 1 from sysibm.sysdummy1 where 11=2


I couldn't figure out why my query didn't return the correct
result...until i saw that my parameter wasn't properly being replaced
because a shorter-one was previously defined and was still in the list.

Is this behavior by design or is it possible to first replace the longest
parameter-names?

thanks,

Michaël Willemot

--
Michaël Willemot
michael@...
http://www.sotto.be

Hi,

I just did my AQT setup and I'm getting the following error: "The exception
generated was c0000005 at address 0015CEA4 (<nosymbols>)"

Any pointers how to resolve it?

Regards,
-----------------------------
Abhishek Singhal
Infosys Technologies Ltd
Bhubaneswar



[Non-text portions of this message have been removed]

Yes, you say
--aqt setparm,parm=yesterday,value=$result(1)

This will set the parm to the first value returned by the most-recent query run.

I guess, long term, I could add a function so you can specify which database the
"value=(select" runs against.

Phil.



--- In aqt@yahoogroups.com, "dlw.sbux" <dwilliam@...> wrote:
>
> I know the AQT FAQ states that it does not support cross-database queries, but
the Help text on AQT scripting functions states that for SetParm:
> You can set the parm to a value returned by a query. Example:
>
> --aqt setparm,parm=yesterday,value=(select current date - 1 day from
sysibm.sysdummy1)
>
> I need to run a query on one database based on a single value retrieved from
another, so I created a script to
> 1)connect to one database
> 2)fetch a value via SetParm
> 3) connect to the other database
> 4) run a query that references the Setparm variable with the '$' prefix
>
> The query fails. When I check the .log file I see that, rather than inserting
the *result* of the first query, it actually inserts the *entire query
statement* from Setparm, not the *value returned* as stated in the help text; of
course, that won't work because then it's referencing a table that is not in the
same database.
>
> Is there a way to run a script that queries one database, fetches a value, and
uses that value in a subsequent query over another database?
>

I know the AQT FAQ states that it does not support cross-database queries, but
the Help text on AQT scripting functions states that for SetParm:
You can set the parm to a value returned by a query. Example:

--aqt setparm,parm=yesterday,value=(select current date - 1 day from
sysibm.sysdummy1)

I need to run a query on one database based on a single value retrieved from
another, so I created a script to
1)connect to one database
2)fetch a value via SetParm
3) connect to the other database
4) run a query that references the Setparm variable with the '$' prefix

The query fails. When I check the .log file I see that, rather than inserting
the *result* of the first query, it actually inserts the *entire query
statement* from Setparm, not the *value returned* as stated in the help text; of
course, that won't work because then it's referencing a table that is not in the
same database.

Is there a way to run a script that queries one database, fetches a value, and
uses that value in a subsequent query over another database?

Yep, did all that. What I figured out was that within the Tools/Parameters
window, just specify the field names without the leading colon or dollar sign;
those should only be used within the query statement itself.

--- In aqt@yahoogroups.com, "cardettnz" <group@...> wrote:
>
> On Tools > Parameters, make sure you have checked Enable Parameters. You will
also need to change "Indicate parameters with" to :
>
> Phil.
>
> --- In aqt@yahoogroups.com, "dlw.sbux" <dwilliam@> wrote:
> >
> > Can you please tell me what is wrong with this coding?
> > Tools/Parameters window:
> > Parameter = :Stores
> > Value = (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942, 5453, 7801,
10132, 10672, 11466, 11226, 9627, 6768, 9454)
> >
> > Query says:
> > Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
> > from APPDWH.SFT_POS_HDR_GB
> > where STORE_NUM IN :Stores
> >
> > Using Tools/View SQL after parameter replacement shows:
> > Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
> > from APPDWH.SFT_POS_HDR_GB
> > where STORE_NUM IN :Stores
> >
> > If I put the parentheses outside the Value as part of the SQL code, i.e.
where STORE_NUM IN (:Stores), I still get "where STORE_NUM IN (:Stores)" instead
of "where STORE_NUM IN (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942,
5453, 7801, 10132, 10672, 11466, 11226, 9627, 6768, 9454)"
> >
> > Same thing if I use "$" instead of ":" as the parameter identifier.
> >
> > Thanks,
> > Daniel
> >
>

On Tools > Parameters, make sure you have checked Enable Parameters. You will
also need to change "Indicate parameters with" to :

Phil.

--- In aqt@yahoogroups.com, "dlw.sbux" <dwilliam@...> wrote:
>
> Can you please tell me what is wrong with this coding?
> Tools/Parameters window:
> Parameter = :Stores
> Value = (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942, 5453, 7801,
10132, 10672, 11466, 11226, 9627, 6768, 9454)
>
> Query says:
> Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
> from APPDWH.SFT_POS_HDR_GB
> where STORE_NUM IN :Stores
>
> Using Tools/View SQL after parameter replacement shows:
> Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
> from APPDWH.SFT_POS_HDR_GB
> where STORE_NUM IN :Stores
>
> If I put the parentheses outside the Value as part of the SQL code, i.e. where
STORE_NUM IN (:Stores), I still get "where STORE_NUM IN (:Stores)" instead of
"where STORE_NUM IN (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942, 5453,
7801, 10132, 10672, 11466, 11226, 9627, 6768, 9454)"
>
> Same thing if I use "$" instead of ":" as the parameter identifier.
>
> Thanks,
> Daniel
>

Can you please tell me what is wrong with this coding?
Tools/Parameters window:
Parameter = :Stores
Value = (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942, 5453, 7801,
10132, 10672, 11466, 11226, 9627, 6768, 9454)

Query says:
Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
from APPDWH.SFT_POS_HDR_GB
where STORE_NUM IN :Stores

Using Tools/View SQL after parameter replacement shows:
Select STORE_NUM, to_char(BUS_DT), to_char(ACTUAL_DT), 'C', RGSTR_NUM,
TRANS_END_TM,  TRANS_NUM, CUST_TRANS_IND, SUB_TTL_LCL_AMT
from APPDWH.SFT_POS_HDR_GB
where STORE_NUM IN :Stores

If I put the parentheses outside the Value as part of the SQL code, i.e. where
STORE_NUM IN (:Stores), I still get "where STORE_NUM IN (:Stores)" instead of
"where STORE_NUM IN (3345, 3334, 317, 9319, 10529, 5524, 5704, 5916, 8942, 5453,
7801, 10132, 10672, 11466, 11226, 9627, 6768, 9454)"

Same thing if I use "$" instead of ":" as the parameter identifier.

Thanks,
Daniel

Hi,

To do this, use the DDL Generator. There is a basic version in AQT Standard
Edition (Tools/Simple DDL Generator) for tables, and a more advanced version for
tables, indexs, tablespaces etc) in AQT Extended Edition.

Simple DDL Generator, see:
http://www.querytool.com/help/254.htm

Advanced version, see:
http://www.querytool.com/help/772.htm

--
Troy Pascoe
Technical Support
Cardett Associates Ltd
troy@...
===================




--- In aqt@yahoogroups.com, "seetha_sen" <seetha_sen@...> wrote:
>
> Hi
>
> Kindly explain how to convert the Oracle db to DB2 db using AQT
>
> Thanks in Advance
>
> Thanks
>
> Sen
> seetha_sen@...
>

Hi

Kindly explain how to convert the Oracle db to DB2 db using AQT

Thanks in Advance

Thanks

Sen
seetha_sen@...

You do this by creating an Alias for that member. Eg:
CREATE ALIAS myLibrary/myAlias FOR memberLibrary/memberFile(memberName)

You then select from that Alias.

Phil.

--- In aqt@yahoogroups.com, "phillips.rich@..." <phillips.rich@...> wrote:
>
> Is there a SQL syntax or AQT tweak that will allow selecting from a member on
iSeries DB2 ?
>

Hi there,

the CloseXL function wasn't added until AQT v8.2.5. If you need to use this, you
should upgrade to AQT v8.

Regards,

Phil.

--- In aqt@yahoogroups.com, "Damjan" <djelovsek@...> wrote:
>
> I've been using AQT 7.17 for quite some time. Lately I'm having troubles with
scripting function called CloseXL. I want to use it in order to close excel file
that opens every time I export data to excel format. How can I avoid opening
excel or how can I use the above function (CloseXL) propperly? Thanks!
> Damjan
>

Is there a SQL syntax or AQT tweak that will allow selecting from a member on
iSeries DB2 ?

I've been using AQT 7.17 for quite some time. Lately I'm having troubles with
scripting function called CloseXL. I want to use it in order to close excel file
that opens every time I export data to excel format. How can I avoid opening
excel or how can I use the above function (CloseXL) propperly? Thanks!
Damjan

A fix for this can be found at http://www.querytool.com/aqt829e.zip

Regards,

Phil.

--- In aqt@yahoogroups.com, "tony_awbrey" <tony.awbrey@...> wrote:
>
> I have the following SQL and If I paste it into the SQL window and run it it
works fine. However, if I paste it into the SQL window and then take the Format
SQL option to format it 3 tables (WILL_SOURCE_MEANS, UHELP.APLTN_CD WILL_SOURCE,
UHELP.APLTN_CD WILL_TERM) are being removed from the FROM clause.
>
>
> Thanks,
>
> Tony
>
>
> SELECT WILL.ID AS ID , WILL.FUND_ACCT AS FUND_ACCT , WILL.WILL_DATEDB AS
WILL_DATEDB , WILL.WILL_AMT AS WILL_AMT , WILL.CNFDL_IND AS CNFDL_IND ,
CNFDL_IND.CNFDL_IND_DESCR AS CNFDL_IND_DESCR , WILL.WILL_TYPE AS WILL_TYPE ,
WILL.WILL_STTUS AS WILL_STTUS , WILL.WILL_BAL AS WILL_BAL , WILL.WILL_NUM AS
WILL_NUM , FUND.FUND_SHORT_TITLE AS FUND_SHORT_TITLE , WILL_TYPE.WILL_TYPE_DESCR
AS WILL_TYPE_DESCR , FUND.FUND_COLL_ATTRB AS FUND_COLL_ATTRB ,
FUND.FUND_DEPT_ATTRB AS FUND_DEPT_ATTRB , WILL.CMPGN_AMT AS CMPGN_AMT ,
FUND_COLL_ATTRB.FUND_COLL_DESCR AS FUND_COLL_DESCR ,
FUND_DEPT_ATTRB.FUND_DEPT_DESCR AS FUND_DEPT_DESCR , WILL.UPDT_DATEDB AS
UPDT_DATEDB , WILL.ACTG_WILL_VAL AS ACTG_WILL_VAL , WILL.USER_VARBL_DATEDB1 AS
USER_VARBL_DATEDB1 , WILL.DECEASED_DATEDB AS DECEASED_DATEDB , WILL.CSV AS CSV ,
WILL.WILL_DCMNT_RCVD AS WILL_DCMNT_RCVD , WILL.WILL_USER_VARBL1 AS
WILL_USER_VARBL1 , WILL.WILL_USER_VARBL2 AS WILL_USER_VARBL2 ,
WILL.WILL_USER_VARBL3 AS WILL_USER_VARBL3 , WILL.CMPGN_CD AS CMPGN_CD ,
WILL.CMPGN_EXTSN AS CMPGN_EXTSN , WILL.SLCT_CD AS SLCT_CD , WILL.INCM_ATTRB AS
INCM_ATTRB , WILL.INCM_TYPE AS INCM_TYPE , WILL.ASSET_TYPE AS ASSET_TYPE ,
ASSET_TYPE.ASSET_TYPE_DESCR AS ASSET_TYPE_DESCR , INCM_TYPE.INCM_TYPE_DESCR AS
INCM_TYPE_DESCR , WILL.PROJ_NUM AS PROJ_NUM , CASE WHEN WRITE_OFF_IND = 'V' THEN
'Written Off' WHEN WRITE_OFF_IND = 'X' THEN 'Cancelled' WHEN WRITE_OFF_IND = ' '
THEN 'Active' ELSE WRITE_OFF_IND END AS WILL_WRITE_OFF_IND , WILL.LEVY_CD AS
LEVY_CD , WILL.LEVY_VAL AS LEVY_VAL , CMPGN_CD.CMPGN_DESCR AS CMPGN_DESCR ,
WILL.PRPSL_NUM AS PRPSL_NUM , CMPGN_EXTSN.CMPGN_EXTSN_DESCR AS CMPGN_EXTSN_DESCR
, SLCT_CD.SLCT_DESCR AS SLCT_DESCR , WILL.WILL_PRESENT_VALUE_DATEDB AS
WILL_PRESENT_VALUE_DATEDB , WILL.WILL_VALUE_ADJ_AMOUNT AS WILL_VALUE_ADJ_AMOUNT
, WILL.WILL_VALUE_ADJ_DATEDB AS WILL_VALUE_ADJ_DATEDB ,
WILL.WILL_ESTIMATION_TYPE AS WILL_ESTIMATION_TYPE , WILL.WILL_GIFT_LEVEL AS
WILL_GIFT_LEVEL , WILL.WILL_ACCOUNTING_DATEDB AS WILL_ACCOUNTING_DATEDB ,
WILL.WILL_MATURITY_DATEDB AS WILL_MATURITY_DATEDB , WILL.WILL_REVOCABLE AS
WILL_REVOCABLE , WILL.WILL_RECEIVED AS WILL_RECEIVED , WILL.WILL_PUBLICIZE AS
WILL_PUBLICIZE , WILL.WILL_NOTIFICATION AS WILL_NOTIFICATION ,
WILL.WILL_NOTIFICATION_DATEDB AS WILL_NOTIFICATION_DATEDB ,
WILL.WILL_USER_VARBL4 AS WILL_USER_VARBL4 , WILL.WILL_USER_VARBL5 AS
WILL_USER_VARBL5 , WILL.WILL_USER_VARBL6 AS WILL_USER_VARBL6 ,
WILL.WILL_USER_VARBL7 AS WILL_USER_VARBL7 , WILL.WILL_USER_VARBL8 AS
WILL_USER_VARBL8 , WILL.WILL_USER_VARBL9 AS WILL_USER_VARBL9 ,
WILL.WILL_USER_VARBL10 AS WILL_USER_VARBL10 , WILL.WILL_USER_VARBL11 AS
WILL_USER_VARBL11 , WILL.WILL_USER_VARBL12 AS WILL_USER_VARBL12 ,
WILL.USER_VARBL_DATEDB2 AS USER_VARBL_DATEDB2 , WILL.USER_VARBL_DATEDB3 AS
USER_VARBL_DATEDB3 , WILL.USER_VARBL_DATEDB4 AS USER_VARBL_DATEDB4 ,
WILL.WILL_NONCHARITABLE_AMOUNT AS WILL_NONCHARITABLE_AMOUNT ,
WILL.WILL_INSTRUMENT AS WILL_INSTRUMENT , WILL_INSTRUMENT.WILL_INSTRUMENT_DESCR
AS WILL_INSTRUMENT_DESCR , WILL_PROGRESS.WILL_PROGRESS_DESCR AS
WILL_PROGRESS_DESCR , WILL_GIFT_LEVEL.APLTN_CD_DESCR AS WILL_GIFT_LEVEL_DESCR ,
WILL_PUBLICIZE.APLTN_CD_DESCR AS WILL_PUBLICIZE_DESCR ,
WILL.WILL_ESTIMATION_DATEDB AS WILL_ESTIMATION_DATEDB ,
WILL_ESTIMATN_TYPE.APLTN_CD_DESCR AS WILL_ESTIMATION_TYPE_DESCR , WILL.WILL_TERM
AS WILL_TERM , WILL.WILL_TERM_VALUE AS WILL_TERM_VALUE ,
WILL_TERM.APLTN_CD_DESCR AS WILL_TERM_DESCR , WILL.WILL_PAYOUT_AMOUNT AS
WILL_PAYOUT_AMOUNT , WILL.WILL_PAYOUT_PERCENT AS WILL_PAYOUT_PERCENT ,
WILL.WILL_PAYOUT_TERM AS WILL_PAYOUT_TERM , WILL_PAYOUT_TERM.APLTN_CD_DESCR AS
WILL_PAYOUT_TERM_DESCR , WILL.WILL_PAYOUT_DATEDB AS WILL_PAYOUT_DATEDB ,
WILL.WILL_TRUSTEE_IND AS WILL_TRUSTEE_IND , WILL.WILL_CONTINGENT_IND AS
WILL_CONTINGENT_IND , WILL.WILL_CONFIRMED_IND AS WILL_CONFIRMED_IND ,
WILL.WILL_NOTIFIED_ID AS WILL_NOTIFIED_ID , WILL.WILL_SOURCE AS WILL_SOURCE ,
WILL_SOURCE.APLTN_CD_DESCR AS WILL_SOURCE_DESCR , WILL.WILL_SOURCE_MEANS AS
WILL_SOURCE_MEANS , WILL_SOURCE_MEANS.APLTN_CD_DESCR AS WILL_SOURCE_MEANS_DESCR
, WILL.WILL_SOURCE_DATEDB AS WILL_SOURCE_DATEDB , WILL.WILL_NOTIFICATION_MEANS
AS WILL_NOTIFICATION_MEANS , WILL_NOTFCTN_MEANS.APLTN_CD_DESCR AS
WILL_NOTIFICATION_MEANS_DESCR , WILL_NOTIFICATION.APLTN_CD_DESCR AS
WILL_NOTIFICATION_DESCR , FUND.FUND_ORG_LVL1 AS FUND_ORG_LVL1 ,
FUND.FUND_ORG_LVL2 AS FUND_ORG_LVL2 , FUND.FUND_ORG_LVL3 AS FUND_ORG_LVL3 ,
FUND.FUND_ORG_LVL4 AS FUND_ORG_LVL4 FROM UHELP.CNFDL_IND CNFDL_IND ,
UHELP.WILL_PROGRESS WILL_PROGRESS , UHELP.WILL_INSTRUMENT WILL_INSTRUMENT ,
UHELP.WILL_TYPE WILL_TYPE , UHELP.SLCT_CD SLCT_CD , UHELP.INCM_TYPE INCM_TYPE ,
UHELP.FUND_DEPT_ATTRB FUND_DEPT_ATTRB , UHELP.FUND_COLL_ATTRB FUND_COLL_ATTRB ,
UHELP.CMPGN_EXTSN CMPGN_EXTSN , UHELP.CMPGN_CD CMPGN_CD , UHELP.ASSET_TYPE
ASSET_TYPE , UHELP.APLTN_CD WILL_TERM , UHELP.APLTN_CD WILL_SOURCE_MEANS ,
UHELP.APLTN_CD WILL_SOURCE , UHELP.APLTN_CD WILL_PUBLICIZE , UHELP.APLTN_CD
WILL_PAYOUT_TERM , UHELP.APLTN_CD WILL_NOTIFICATION , UHELP.APLTN_CD
WILL_NOTFCTN_MEANS , UHELP.APLTN_CD WILL_GIFT_LEVEL , UHELP.APLTN_CD
WILL_ESTIMATN_TYPE , SUMMIT.WILL WILL , SUMMIT.FUND FUND WHERE ( WILL.FUND_ACCT
= FUND.FUND_ACCT ) AND (WILL.CNFDL_IND = CNFDL_IND.CNFDL_IND ) AND (
WILL.WILL_INSTRUMENT = WILL_INSTRUMENT.WILL_INSTRUMENT ) AND (WILL.WILL_STTUS =
WILL_PROGRESS.WILL_PROGRESS) AND ( WILL.WILL_ESTIMATION_TYPE =
WILL_ESTIMATN_TYPE.APLTN_CD AND WILL_ESTIMATN_TYPE.FLD_CTGRY =
'WILL_ESTIMATN_TYPE' ) AND ( WILL.WILL_GIFT_LEVEL = WILL_GIFT_LEVEL.APLTN_CD AND
WILL_GIFT_LEVEL.FLD_CTGRY = 'WILL_GIFT_LEVEL' ) AND (
WILL.WILL_NOTIFICATION_MEANS = WILL_NOTFCTN_MEANS.APLTN_CD AND
WILL_NOTFCTN_MEANS.FLD_CTGRY = 'WILL_NOTFCTN_MEANS' ) AND (
WILL.WILL_NOTIFICATION = WILL_NOTIFICATION.APLTN_CD AND
WILL_NOTIFICATION.FLD_CTGRY = 'WILL_NOTIFICATION' ) AND ( WILL.WILL_PAYOUT_TERM
= WILL_PAYOUT_TERM.APLTN_CD AND WILL_PAYOUT_TERM.FLD_CTGRY = 'WILL_PAYOUT_TERM'
) AND ( WILL.WILL_PUBLICIZE = WILL_PUBLICIZE.APLTN_CD AND
WILL_PUBLICIZE.FLD_CTGRY = 'WILL_PUBLICIZE' ) AND ( WILL.WILL_SOURCE =
WILL_SOURCE.APLTN_CD AND WILL_SOURCE.FLD_CTGRY = 'WILL_SOURCE' ) AND (
WILL.WILL_SOURCE_MEANS = WILL_SOURCE_MEANS.APLTN_CD AND
WILL_SOURCE_MEANS.FLD_CTGRY = 'WILL_SOURCE_MEANS' ) AND ( WILL.WILL_TERM =
WILL_TERM.APLTN_CD AND WILL_TERM.FLD_CTGRY = 'WILL_TERM' ) AND ( WILL.WILL_TYPE
= WILL_TYPE.WILL_TYPE ) AND ( FUND.FUND_COLL_ATTRB =
FUND_COLL_ATTRB.FUND_COLL_ATTRB ) AND ( FUND.FUND_DEPT_ATTRB =
FUND_DEPT_ATTRB.FUND_DEPT_ATTRB ) AND ( WILL.ASSET_TYPE = ASSET_TYPE.ASSET_TYPE
) AND ( WILL.INCM_TYPE = INCM_TYPE.INCM_TYPE ) AND (WILL.SLCT_CD =
SLCT_CD.SLCT_CD) AND (WILL.CMPGN_CD = CMPGN_CD.CMPGN_CD) AND (CMPGN_EXTSN.CMPGN
= WILL.CMPGN_CD) AND (WILL.CMPGN_EXTSN = CMPGN_EXTSN.CMPGN_EXTSN) AND ( (
WILL.ID = ? ) OR (WILL.WILL_JOINT_ID = ?))
>

This looks like a bug. I'll let you know when I have a fix.

Phil.

--- In aqt@yahoogroups.com, "tony_awbrey" <tony.awbrey@...> wrote:
>
> I have the following SQL and If I paste it into the SQL window and run it it
works fine. However, if I paste it into the SQL window and then take the Format
SQL option to format it 3 tables (WILL_SOURCE_MEANS, UHELP.APLTN_CD WILL_SOURCE,
UHELP.APLTN_CD WILL_TERM) are being removed from the FROM clause.
>
>
> Thanks,
>
> Tony
>
>
> SELECT WILL.ID AS ID , WILL.FUND_ACCT AS FUND_ACCT , WILL.WILL_DATEDB AS
WILL_DATEDB , WILL.WILL_AMT AS WILL_AMT , WILL.CNFDL_IND AS CNFDL_IND ,
CNFDL_IND.CNFDL_IND_DESCR AS CNFDL_IND_DESCR , WILL.WILL_TYPE AS WILL_TYPE ,
WILL.WILL_STTUS AS WILL_STTUS , WILL.WILL_BAL AS WILL_BAL , WILL.WILL_NUM AS
WILL_NUM , FUND.FUND_SHORT_TITLE AS FUND_SHORT_TITLE , WILL_TYPE.WILL_TYPE_DESCR
AS WILL_TYPE_DESCR , FUND.FUND_COLL_ATTRB AS FUND_COLL_ATTRB ,
FUND.FUND_DEPT_ATTRB AS FUND_DEPT_ATTRB , WILL.CMPGN_AMT AS CMPGN_AMT ,
FUND_COLL_ATTRB.FUND_COLL_DESCR AS FUND_COLL_DESCR ,
FUND_DEPT_ATTRB.FUND_DEPT_DESCR AS FUND_DEPT_DESCR , WILL.UPDT_DATEDB AS
UPDT_DATEDB , WILL.ACTG_WILL_VAL AS ACTG_WILL_VAL , WILL.USER_VARBL_DATEDB1 AS
USER_VARBL_DATEDB1 , WILL.DECEASED_DATEDB AS DECEASED_DATEDB , WILL.CSV AS CSV ,
WILL.WILL_DCMNT_RCVD AS WILL_DCMNT_RCVD , WILL.WILL_USER_VARBL1 AS
WILL_USER_VARBL1 , WILL.WILL_USER_VARBL2 AS WILL_USER_VARBL2 ,
WILL.WILL_USER_VARBL3 AS WILL_USER_VARBL3 , WILL.CMPGN_CD AS CMPGN_CD ,
WILL.CMPGN_EXTSN AS CMPGN_EXTSN , WILL.SLCT_CD AS SLCT_CD , WILL.INCM_ATTRB AS
INCM_ATTRB , WILL.INCM_TYPE AS INCM_TYPE , WILL.ASSET_TYPE AS ASSET_TYPE ,
ASSET_TYPE.ASSET_TYPE_DESCR AS ASSET_TYPE_DESCR , INCM_TYPE.INCM_TYPE_DESCR AS
INCM_TYPE_DESCR , WILL.PROJ_NUM AS PROJ_NUM , CASE WHEN WRITE_OFF_IND = 'V' THEN
'Written Off' WHEN WRITE_OFF_IND = 'X' THEN 'Cancelled' WHEN WRITE_OFF_IND = ' '
THEN 'Active' ELSE WRITE_OFF_IND END AS WILL_WRITE_OFF_IND , WILL.LEVY_CD AS
LEVY_CD , WILL.LEVY_VAL AS LEVY_VAL , CMPGN_CD.CMPGN_DESCR AS CMPGN_DESCR ,
WILL.PRPSL_NUM AS PRPSL_NUM , CMPGN_EXTSN.CMPGN_EXTSN_DESCR AS CMPGN_EXTSN_DESCR
, SLCT_CD.SLCT_DESCR AS SLCT_DESCR , WILL.WILL_PRESENT_VALUE_DATEDB AS
WILL_PRESENT_VALUE_DATEDB , WILL.WILL_VALUE_ADJ_AMOUNT AS WILL_VALUE_ADJ_AMOUNT
, WILL.WILL_VALUE_ADJ_DATEDB AS WILL_VALUE_ADJ_DATEDB ,
WILL.WILL_ESTIMATION_TYPE AS WILL_ESTIMATION_TYPE , WILL.WILL_GIFT_LEVEL AS
WILL_GIFT_LEVEL , WILL.WILL_ACCOUNTING_DATEDB AS WILL_ACCOUNTING_DATEDB ,
WILL.WILL_MATURITY_DATEDB AS WILL_MATURITY_DATEDB , WILL.WILL_REVOCABLE AS
WILL_REVOCABLE , WILL.WILL_RECEIVED AS WILL_RECEIVED , WILL.WILL_PUBLICIZE AS
WILL_PUBLICIZE , WILL.WILL_NOTIFICATION AS WILL_NOTIFICATION ,
WILL.WILL_NOTIFICATION_DATEDB AS WILL_NOTIFICATION_DATEDB ,
WILL.WILL_USER_VARBL4 AS WILL_USER_VARBL4 , WILL.WILL_USER_VARBL5 AS
WILL_USER_VARBL5 , WILL.WILL_USER_VARBL6 AS WILL_USER_VARBL6 ,
WILL.WILL_USER_VARBL7 AS WILL_USER_VARBL7 , WILL.WILL_USER_VARBL8 AS
WILL_USER_VARBL8 , WILL.WILL_USER_VARBL9 AS WILL_USER_VARBL9 ,
WILL.WILL_USER_VARBL10 AS WILL_USER_VARBL10 , WILL.WILL_USER_VARBL11 AS
WILL_USER_VARBL11 , WILL.WILL_USER_VARBL12 AS WILL_USER_VARBL12 ,
WILL.USER_VARBL_DATEDB2 AS USER_VARBL_DATEDB2 , WILL.USER_VARBL_DATEDB3 AS
USER_VARBL_DATEDB3 , WILL.USER_VARBL_DATEDB4 AS USER_VARBL_DATEDB4 ,
WILL.WILL_NONCHARITABLE_AMOUNT AS WILL_NONCHARITABLE_AMOUNT ,
WILL.WILL_INSTRUMENT AS WILL_INSTRUMENT , WILL_INSTRUMENT.WILL_INSTRUMENT_DESCR
AS WILL_INSTRUMENT_DESCR , WILL_PROGRESS.WILL_PROGRESS_DESCR AS
WILL_PROGRESS_DESCR , WILL_GIFT_LEVEL.APLTN_CD_DESCR AS WILL_GIFT_LEVEL_DESCR ,
WILL_PUBLICIZE.APLTN_CD_DESCR AS WILL_PUBLICIZE_DESCR ,
WILL.WILL_ESTIMATION_DATEDB AS WILL_ESTIMATION_DATEDB ,
WILL_ESTIMATN_TYPE.APLTN_CD_DESCR AS WILL_ESTIMATION_TYPE_DESCR , WILL.WILL_TERM
AS WILL_TERM , WILL.WILL_TERM_VALUE AS WILL_TERM_VALUE ,
WILL_TERM.APLTN_CD_DESCR AS WILL_TERM_DESCR , WILL.WILL_PAYOUT_AMOUNT AS
WILL_PAYOUT_AMOUNT , WILL.WILL_PAYOUT_PERCENT AS WILL_PAYOUT_PERCENT ,
WILL.WILL_PAYOUT_TERM AS WILL_PAYOUT_TERM , WILL_PAYOUT_TERM.APLTN_CD_DESCR AS
WILL_PAYOUT_TERM_DESCR , WILL.WILL_PAYOUT_DATEDB AS WILL_PAYOUT_DATEDB ,
WILL.WILL_TRUSTEE_IND AS WILL_TRUSTEE_IND , WILL.WILL_CONTINGENT_IND AS
WILL_CONTINGENT_IND , WILL.WILL_CONFIRMED_IND AS WILL_CONFIRMED_IND ,
WILL.WILL_NOTIFIED_ID AS WILL_NOTIFIED_ID , WILL.WILL_SOURCE AS WILL_SOURCE ,
WILL_SOURCE.APLTN_CD_DESCR AS WILL_SOURCE_DESCR , WILL.WILL_SOURCE_MEANS AS
WILL_SOURCE_MEANS , WILL_SOURCE_MEANS.APLTN_CD_DESCR AS WILL_SOURCE_MEANS_DESCR
, WILL.WILL_SOURCE_DATEDB AS WILL_SOURCE_DATEDB , WILL.WILL_NOTIFICATION_MEANS
AS WILL_NOTIFICATION_MEANS , WILL_NOTFCTN_MEANS.APLTN_CD_DESCR AS
WILL_NOTIFICATION_MEANS_DESCR , WILL_NOTIFICATION.APLTN_CD_DESCR AS
WILL_NOTIFICATION_DESCR , FUND.FUND_ORG_LVL1 AS FUND_ORG_LVL1 ,
FUND.FUND_ORG_LVL2 AS FUND_ORG_LVL2 , FUND.FUND_ORG_LVL3 AS FUND_ORG_LVL3 ,
FUND.FUND_ORG_LVL4 AS FUND_ORG_LVL4 FROM UHELP.CNFDL_IND CNFDL_IND ,
UHELP.WILL_PROGRESS WILL_PROGRESS , UHELP.WILL_INSTRUMENT WILL_INSTRUMENT ,
UHELP.WILL_TYPE WILL_TYPE , UHELP.SLCT_CD SLCT_CD , UHELP.INCM_TYPE INCM_TYPE ,
UHELP.FUND_DEPT_ATTRB FUND_DEPT_ATTRB , UHELP.FUND_COLL_ATTRB FUND_COLL_ATTRB ,
UHELP.CMPGN_EXTSN CMPGN_EXTSN , UHELP.CMPGN_CD CMPGN_CD , UHELP.ASSET_TYPE
ASSET_TYPE , UHELP.APLTN_CD WILL_TERM , UHELP.APLTN_CD WILL_SOURCE_MEANS ,
UHELP.APLTN_CD WILL_SOURCE , UHELP.APLTN_CD WILL_PUBLICIZE , UHELP.APLTN_CD
WILL_PAYOUT_TERM , UHELP.APLTN_CD WILL_NOTIFICATION , UHELP.APLTN_CD
WILL_NOTFCTN_MEANS , UHELP.APLTN_CD WILL_GIFT_LEVEL , UHELP.APLTN_CD
WILL_ESTIMATN_TYPE , SUMMIT.WILL WILL , SUMMIT.FUND FUND WHERE ( WILL.FUND_ACCT
= FUND.FUND_ACCT ) AND (WILL.CNFDL_IND = CNFDL_IND.CNFDL_IND ) AND (
WILL.WILL_INSTRUMENT = WILL_INSTRUMENT.WILL_INSTRUMENT ) AND (WILL.WILL_STTUS =
WILL_PROGRESS.WILL_PROGRESS) AND ( WILL.WILL_ESTIMATION_TYPE =
WILL_ESTIMATN_TYPE.APLTN_CD AND WILL_ESTIMATN_TYPE.FLD_CTGRY =
'WILL_ESTIMATN_TYPE' ) AND ( WILL.WILL_GIFT_LEVEL = WILL_GIFT_LEVEL.APLTN_CD AND
WILL_GIFT_LEVEL.FLD_CTGRY = 'WILL_GIFT_LEVEL' ) AND (
WILL.WILL_NOTIFICATION_MEANS = WILL_NOTFCTN_MEANS.APLTN_CD AND
WILL_NOTFCTN_MEANS.FLD_CTGRY = 'WILL_NOTFCTN_MEANS' ) AND (
WILL.WILL_NOTIFICATION = WILL_NOTIFICATION.APLTN_CD AND
WILL_NOTIFICATION.FLD_CTGRY = 'WILL_NOTIFICATION' ) AND ( WILL.WILL_PAYOUT_TERM
= WILL_PAYOUT_TERM.APLTN_CD AND WILL_PAYOUT_TERM.FLD_CTGRY = 'WILL_PAYOUT_TERM'
) AND ( WILL.WILL_PUBLICIZE = WILL_PUBLICIZE.APLTN_CD AND
WILL_PUBLICIZE.FLD_CTGRY = 'WILL_PUBLICIZE' ) AND ( WILL.WILL_SOURCE =
WILL_SOURCE.APLTN_CD AND WILL_SOURCE.FLD_CTGRY = 'WILL_SOURCE' ) AND (
WILL.WILL_SOURCE_MEANS = WILL_SOURCE_MEANS.APLTN_CD AND
WILL_SOURCE_MEANS.FLD_CTGRY = 'WILL_SOURCE_MEANS' ) AND ( WILL.WILL_TERM =
WILL_TERM.APLTN_CD AND WILL_TERM.FLD_CTGRY = 'WILL_TERM' ) AND ( WILL.WILL_TYPE
= WILL_TYPE.WILL_TYPE ) AND ( FUND.FUND_COLL_ATTRB =
FUND_COLL_ATTRB.FUND_COLL_ATTRB ) AND ( FUND.FUND_DEPT_ATTRB =
FUND_DEPT_ATTRB.FUND_DEPT_ATTRB ) AND ( WILL.ASSET_TYPE = ASSET_TYPE.ASSET_TYPE
) AND ( WILL.INCM_TYPE = INCM_TYPE.INCM_TYPE ) AND (WILL.SLCT_CD =
SLCT_CD.SLCT_CD) AND (WILL.CMPGN_CD = CMPGN_CD.CMPGN_CD) AND (CMPGN_EXTSN.CMPGN
= WILL.CMPGN_CD) AND (WILL.CMPGN_EXTSN = CMPGN_EXTSN.CMPGN_EXTSN) AND ( (
WILL.ID = ? ) OR (WILL.WILL_JOINT_ID = ?))
>

The Cols table of the SQL Window does not refresh existing tables when
the "Build list-of-tables from SQL" button is pressed.

I often work with large SQL scripts. If I modify a table I created
earlier in the script the changes are not reflected when I press the
"Build list-of-tables from SQL" button.

That I am aware of it cannot be done. That I am aware of Teradata does not have
a feature such as MS SQL Servers Linked Servers. I too work between three huge
Teradata warehouses and yez it is a pain. I can say Teradata's Perallel
Transporter has become my friend. It will move data from one system to another
and do it in parallel so it is very FAST.

--- In aqt@yahoogroups.com, "naidu_mca2" <naidu_mca2@...> wrote:
>
> Hi Everyone nice to meet you through this forum.
>
> I am using teradata . I have tables in two different teradata servers. Is it
possible for me to access tables from two different servers.
>
> can anyone of you help me.
>

I have the following SQL and If I paste it into the SQL window and run it it
works fine. However, if I paste it into the SQL window and then take the Format
SQL option to format it 3 tables (WILL_SOURCE_MEANS, UHELP.APLTN_CD WILL_SOURCE,
UHELP.APLTN_CD WILL_TERM) are being removed from the FROM clause.


Thanks,

Tony


SELECT WILL.ID AS ID , WILL.FUND_ACCT AS FUND_ACCT , WILL.WILL_DATEDB AS
WILL_DATEDB , WILL.WILL_AMT AS WILL_AMT , WILL.CNFDL_IND AS CNFDL_IND ,
CNFDL_IND.CNFDL_IND_DESCR AS CNFDL_IND_DESCR , WILL.WILL_TYPE AS WILL_TYPE ,
WILL.WILL_STTUS AS WILL_STTUS , WILL.WILL_BAL AS WILL_BAL , WILL.WILL_NUM AS
WILL_NUM , FUND.FUND_SHORT_TITLE AS FUND_SHORT_TITLE , WILL_TYPE.WILL_TYPE_DESCR
AS WILL_TYPE_DESCR , FUND.FUND_COLL_ATTRB AS FUND_COLL_ATTRB ,
FUND.FUND_DEPT_ATTRB AS FUND_DEPT_ATTRB , WILL.CMPGN_AMT AS CMPGN_AMT ,
FUND_COLL_ATTRB.FUND_COLL_DESCR AS FUND_COLL_DESCR ,
FUND_DEPT_ATTRB.FUND_DEPT_DESCR AS FUND_DEPT_DESCR , WILL.UPDT_DATEDB AS
UPDT_DATEDB , WILL.ACTG_WILL_VAL AS ACTG_WILL_VAL , WILL.USER_VARBL_DATEDB1 AS
USER_VARBL_DATEDB1 , WILL.DECEASED_DATEDB AS DECEASED_DATEDB , WILL.CSV AS CSV ,
WILL.WILL_DCMNT_RCVD AS WILL_DCMNT_RCVD , WILL.WILL_USER_VARBL1 AS
WILL_USER_VARBL1 , WILL.WILL_USER_VARBL2 AS WILL_USER_VARBL2 ,
WILL.WILL_USER_VARBL3 AS WILL_USER_VARBL3 , WILL.CMPGN_CD AS CMPGN_CD ,
WILL.CMPGN_EXTSN AS CMPGN_EXTSN , WILL.SLCT_CD AS SLCT_CD , WILL.INCM_ATTRB AS
INCM_ATTRB , WILL.INCM_TYPE AS INCM_TYPE , WILL.ASSET_TYPE AS ASSET_TYPE ,
ASSET_TYPE.ASSET_TYPE_DESCR AS ASSET_TYPE_DESCR , INCM_TYPE.INCM_TYPE_DESCR AS
INCM_TYPE_DESCR , WILL.PROJ_NUM AS PROJ_NUM , CASE WHEN WRITE_OFF_IND = 'V' THEN
'Written Off' WHEN WRITE_OFF_IND = 'X' THEN 'Cancelled' WHEN WRITE_OFF_IND = ' '
THEN 'Active' ELSE WRITE_OFF_IND END AS WILL_WRITE_OFF_IND , WILL.LEVY_CD AS
LEVY_CD , WILL.LEVY_VAL AS LEVY_VAL , CMPGN_CD.CMPGN_DESCR AS CMPGN_DESCR ,
WILL.PRPSL_NUM AS PRPSL_NUM , CMPGN_EXTSN.CMPGN_EXTSN_DESCR AS CMPGN_EXTSN_DESCR
, SLCT_CD.SLCT_DESCR AS SLCT_DESCR , WILL.WILL_PRESENT_VALUE_DATEDB AS
WILL_PRESENT_VALUE_DATEDB , WILL.WILL_VALUE_ADJ_AMOUNT AS WILL_VALUE_ADJ_AMOUNT
, WILL.WILL_VALUE_ADJ_DATEDB AS WILL_VALUE_ADJ_DATEDB ,
WILL.WILL_ESTIMATION_TYPE AS WILL_ESTIMATION_TYPE , WILL.WILL_GIFT_LEVEL AS
WILL_GIFT_LEVEL , WILL.WILL_ACCOUNTING_DATEDB AS WILL_ACCOUNTING_DATEDB ,
WILL.WILL_MATURITY_DATEDB AS WILL_MATURITY_DATEDB , WILL.WILL_REVOCABLE AS
WILL_REVOCABLE , WILL.WILL_RECEIVED AS WILL_RECEIVED , WILL.WILL_PUBLICIZE AS
WILL_PUBLICIZE , WILL.WILL_NOTIFICATION AS WILL_NOTIFICATION ,
WILL.WILL_NOTIFICATION_DATEDB AS WILL_NOTIFICATION_DATEDB ,
WILL.WILL_USER_VARBL4 AS WILL_USER_VARBL4 , WILL.WILL_USER_VARBL5 AS
WILL_USER_VARBL5 , WILL.WILL_USER_VARBL6 AS WILL_USER_VARBL6 ,
WILL.WILL_USER_VARBL7 AS WILL_USER_VARBL7 , WILL.WILL_USER_VARBL8 AS
WILL_USER_VARBL8 , WILL.WILL_USER_VARBL9 AS WILL_USER_VARBL9 ,
WILL.WILL_USER_VARBL10 AS WILL_USER_VARBL10 , WILL.WILL_USER_VARBL11 AS
WILL_USER_VARBL11 , WILL.WILL_USER_VARBL12 AS WILL_USER_VARBL12 ,
WILL.USER_VARBL_DATEDB2 AS USER_VARBL_DATEDB2 , WILL.USER_VARBL_DATEDB3 AS
USER_VARBL_DATEDB3 , WILL.USER_VARBL_DATEDB4 AS USER_VARBL_DATEDB4 ,
WILL.WILL_NONCHARITABLE_AMOUNT AS WILL_NONCHARITABLE_AMOUNT ,
WILL.WILL_INSTRUMENT AS WILL_INSTRUMENT , WILL_INSTRUMENT.WILL_INSTRUMENT_DESCR
AS WILL_INSTRUMENT_DESCR , WILL_PROGRESS.WILL_PROGRESS_DESCR AS
WILL_PROGRESS_DESCR , WILL_GIFT_LEVEL.APLTN_CD_DESCR AS WILL_GIFT_LEVEL_DESCR ,
WILL_PUBLICIZE.APLTN_CD_DESCR AS WILL_PUBLICIZE_DESCR ,
WILL.WILL_ESTIMATION_DATEDB AS WILL_ESTIMATION_DATEDB ,
WILL_ESTIMATN_TYPE.APLTN_CD_DESCR AS WILL_ESTIMATION_TYPE_DESCR , WILL.WILL_TERM
AS WILL_TERM , WILL.WILL_TERM_VALUE AS WILL_TERM_VALUE ,
WILL_TERM.APLTN_CD_DESCR AS WILL_TERM_DESCR , WILL.WILL_PAYOUT_AMOUNT AS
WILL_PAYOUT_AMOUNT , WILL.WILL_PAYOUT_PERCENT AS WILL_PAYOUT_PERCENT ,
WILL.WILL_PAYOUT_TERM AS WILL_PAYOUT_TERM , WILL_PAYOUT_TERM.APLTN_CD_DESCR AS
WILL_PAYOUT_TERM_DESCR , WILL.WILL_PAYOUT_DATEDB AS WILL_PAYOUT_DATEDB ,
WILL.WILL_TRUSTEE_IND AS WILL_TRUSTEE_IND , WILL.WILL_CONTINGENT_IND AS
WILL_CONTINGENT_IND , WILL.WILL_CONFIRMED_IND AS WILL_CONFIRMED_IND ,
WILL.WILL_NOTIFIED_ID AS WILL_NOTIFIED_ID , WILL.WILL_SOURCE AS WILL_SOURCE ,
WILL_SOURCE.APLTN_CD_DESCR AS WILL_SOURCE_DESCR , WILL.WILL_SOURCE_MEANS AS
WILL_SOURCE_MEANS , WILL_SOURCE_MEANS.APLTN_CD_DESCR AS WILL_SOURCE_MEANS_DESCR
, WILL.WILL_SOURCE_DATEDB AS WILL_SOURCE_DATEDB , WILL.WILL_NOTIFICATION_MEANS
AS WILL_NOTIFICATION_MEANS , WILL_NOTFCTN_MEANS.APLTN_CD_DESCR AS
WILL_NOTIFICATION_MEANS_DESCR , WILL_NOTIFICATION.APLTN_CD_DESCR AS
WILL_NOTIFICATION_DESCR , FUND.FUND_ORG_LVL1 AS FUND_ORG_LVL1 ,
FUND.FUND_ORG_LVL2 AS FUND_ORG_LVL2 , FUND.FUND_ORG_LVL3 AS FUND_ORG_LVL3 ,
FUND.FUND_ORG_LVL4 AS FUND_ORG_LVL4 FROM UHELP.CNFDL_IND CNFDL_IND ,
UHELP.WILL_PROGRESS WILL_PROGRESS , UHELP.WILL_INSTRUMENT WILL_INSTRUMENT ,
UHELP.WILL_TYPE WILL_TYPE , UHELP.SLCT_CD SLCT_CD , UHELP.INCM_TYPE INCM_TYPE ,
UHELP.FUND_DEPT_ATTRB FUND_DEPT_ATTRB , UHELP.FUND_COLL_ATTRB FUND_COLL_ATTRB ,
UHELP.CMPGN_EXTSN CMPGN_EXTSN , UHELP.CMPGN_CD CMPGN_CD , UHELP.ASSET_TYPE
ASSET_TYPE , UHELP.APLTN_CD WILL_TERM , UHELP.APLTN_CD WILL_SOURCE_MEANS ,
UHELP.APLTN_CD WILL_SOURCE , UHELP.APLTN_CD WILL_PUBLICIZE , UHELP.APLTN_CD
WILL_PAYOUT_TERM , UHELP.APLTN_CD WILL_NOTIFICATION , UHELP.APLTN_CD
WILL_NOTFCTN_MEANS , UHELP.APLTN_CD WILL_GIFT_LEVEL , UHELP.APLTN_CD
WILL_ESTIMATN_TYPE , SUMMIT.WILL WILL , SUMMIT.FUND FUND WHERE ( WILL.FUND_ACCT
= FUND.FUND_ACCT ) AND (WILL.CNFDL_IND = CNFDL_IND.CNFDL_IND ) AND (
WILL.WILL_INSTRUMENT = WILL_INSTRUMENT.WILL_INSTRUMENT ) AND (WILL.WILL_STTUS =
WILL_PROGRESS.WILL_PROGRESS) AND ( WILL.WILL_ESTIMATION_TYPE =
WILL_ESTIMATN_TYPE.APLTN_CD AND WILL_ESTIMATN_TYPE.FLD_CTGRY =
'WILL_ESTIMATN_TYPE' ) AND ( WILL.WILL_GIFT_LEVEL = WILL_GIFT_LEVEL.APLTN_CD AND
WILL_GIFT_LEVEL.FLD_CTGRY = 'WILL_GIFT_LEVEL' ) AND (
WILL.WILL_NOTIFICATION_MEANS = WILL_NOTFCTN_MEANS.APLTN_CD AND
WILL_NOTFCTN_MEANS.FLD_CTGRY = 'WILL_NOTFCTN_MEANS' ) AND (
WILL.WILL_NOTIFICATION = WILL_NOTIFICATION.APLTN_CD AND
WILL_NOTIFICATION.FLD_CTGRY = 'WILL_NOTIFICATION' ) AND ( WILL.WILL_PAYOUT_TERM
= WILL_PAYOUT_TERM.APLTN_CD AND WILL_PAYOUT_TERM.FLD_CTGRY = 'WILL_PAYOUT_TERM'
) AND ( WILL.WILL_PUBLICIZE = WILL_PUBLICIZE.APLTN_CD AND
WILL_PUBLICIZE.FLD_CTGRY = 'WILL_PUBLICIZE' ) AND ( WILL.WILL_SOURCE =
WILL_SOURCE.APLTN_CD AND WILL_SOURCE.FLD_CTGRY = 'WILL_SOURCE' ) AND (
WILL.WILL_SOURCE_MEANS = WILL_SOURCE_MEANS.APLTN_CD AND
WILL_SOURCE_MEANS.FLD_CTGRY = 'WILL_SOURCE_MEANS' ) AND ( WILL.WILL_TERM =
WILL_TERM.APLTN_CD AND WILL_TERM.FLD_CTGRY = 'WILL_TERM' ) AND ( WILL.WILL_TYPE
= WILL_TYPE.WILL_TYPE ) AND ( FUND.FUND_COLL_ATTRB =
FUND_COLL_ATTRB.FUND_COLL_ATTRB ) AND ( FUND.FUND_DEPT_ATTRB =
FUND_DEPT_ATTRB.FUND_DEPT_ATTRB ) AND ( WILL.ASSET_TYPE = ASSET_TYPE.ASSET_TYPE
) AND ( WILL.INCM_TYPE = INCM_TYPE.INCM_TYPE ) AND (WILL.SLCT_CD =
SLCT_CD.SLCT_CD) AND (WILL.CMPGN_CD = CMPGN_CD.CMPGN_CD) AND (CMPGN_EXTSN.CMPGN
= WILL.CMPGN_CD) AND (WILL.CMPGN_EXTSN = CMPGN_EXTSN.CMPGN_EXTSN) AND ( (
WILL.ID = ? ) OR (WILL.WILL_JOINT_ID = ?))

Hi there,

most of the AQT settings are held in the Registry (in
HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Cardett), so unless you
saved the registry you probably can't restore the bulk of your settings.

History queries are held in files aqt_history_xxxx.txt. On your old machine
these may be in C:\Program Files\Advanced Query Tool\workfiles. On your new
machine AQT is probably looking for them in
C:\Documents and Settings\<username>\Application Data\Advanced Query
Tool\Workfiles. This will depend on what your settings are for Options > File
Locations.


Phil.

--- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@...> wrote:
>
> Hi,
>
> I have had to re-image my PC due to a major problem that had cropped up (not
related to AQT).  After the re-image, I installed the full 8.2.8 program which I
downloaded from the AQT website and then copied in the version 8.2.9 AQT.exe
executable and registered the program with my registration key.
>
> Before the re-image, I saved all of the the files in C:\Program Files\Advanced
Query Tool\ to an external drive.  Can I restore my previous AQT settings (and
history queries) by copying over some of those backup files to my new image ? 
If so, which files would need to be restored ?  Thanks a lot.
>
> Glen
>

Hi,

I have had to re-image my PC due to a major problem that had cropped up (not
related to AQT).  After the re-image, I installed the full 8.2.8 program which I
downloaded from the AQT website and then copied in the version 8.2.9 AQT.exe
executable and registered the program with my registration key.

Before the re-image, I saved all of the the files in C:\Program Files\Advanced
Query Tool\ to an external drive.  Can I restore my previous AQT settings (and
history queries) by copying over some of those backup files to my new image ? 
If so, which files would need to be restored ?  Thanks a lot.

Glen

Phil,
I've been away from the office a couple of weeks so I'm just now testing 8.2.9.
 
The errorlevel is working pefectly now. 
 
Thanks for spending time to find a solution to this issue.
 
David Hays.


--- On Wed, 10/14/09, cardettnz <group@...> wrote:


From: cardettnz <group@...>
Subject: [aqt] Re: AQT not generating errorlevel 8 error code in batch mode
To: aqt@yahoogroups.com
Date: Wednesday, October 14, 2009, 2:03 PM


 



I've spent several days on this and think I have a resolution. Basically I had
to do a lot of code restructure so that, when running a batch script, AQT
doesn't touch any screen elements (this appeared to be the cause of the
problem).

The result is at http://www.querytoo l.com/aqt829c. zip - let me know how it
goes.

Phil.

--- In aqt@yahoogroups. com, "glen.wenzel" <glen.wenzel@ ...> wrote:
>
> Hi Phil,
>
> This site:
> http://www.codeguru .com/forum/ showthread. php?t=253791
>
> suggests that this VB code will work to set the ERRORLEVEL value:
>
> Option Explicit
> Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)
>
> Private Sub Command1_Click( )
> ExitProcess 5
> Unload Me
> End Sub
>
> On another site, I read that we may need to initiate AQT.exe with the START
command's /WAIT parameter, in order to let it finish before continuing with the
commands in the BATCH file. Like this:
> START /WAIT AQT.EXE
>
>
> I'm really hoping (along with David Hays and likely quite a number of other
AQT users), that we can find a solution to get ERRORLEVEL set to some non-zero
value when an error occurs.
>
> I upgraded to version 8.2.8 and am successfully using the setexitcode= no
Option, to prevent AQT from crashing and stopping everything. That's a big
improvement ! Now, all we need to do is solve the ERRORLEVEL issue and we'll be
tickled pink. Thanks a lot !
>
> Glen
>
>
> --- In aqt@yahoogroups. com, "cardettnz" <group@> wrote:
> >
> > As with AQT v8.2.8, you can prevent the crash by setting:
> > --aqt options,setexitcode =no
> > however AQT then doesn't set the exit code so your batch script can tell
whether or not AQT has failed.
> >
> > Yes, I tried getting AQT set another environment variable. This then got
bogged down in the mess that Windows is in this area. If I set an environment
variable, it only applies for that process (AQT) and doesn't get seen by the
calling process (the batch script). I can set a system-wide environment
variable, but it still isn't seen by the batch script - the batch script seems
to pick these up when it starts so doesn't see any changes to them. I spent a
couple of days on this without finding a way to pass back the exit code.
> >
> > Phil.
> >
> > --- In aqt@yahoogroups. com, "asdfghjkl" <d_haysjr@> wrote:
> > >
> > > Phil,
> > >
> > > I think there's lots of us that would love to see a solution for that
crash. If the error occurs when AQT tries to set the errorlevel, then what about
this idea for a workaround? Instead of setting errorlevel set some other
environment variable (e.g., AQT_error) to some value to indicate an error. In
our batch files we could check that environment variable instead of errorlevel.
> > >
> > > David Hays
> > >
> > >
> > >
> > > --- In aqt@yahoogroups. com, "cardettnz" <group@> wrote:
> > > >
> > > > This is a problem with AQT. When AQT tries to set the errorlevel, it
crashes. We don't have a resolution to this unfortunately, despite having spent
quite some time on this.
> > > >
> > > > See http://www.querytoo l.com/help/ 1171.htm for more on this.
> > > >
> > > > Phil.
> > > >
> > > > --- In aqt@yahoogroups. com, "glen.wenzel" <glen.wenzel@ > wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I am trying to get a batch file to create an errorlevel 8 DOS error
code when the AQT sql job fails. However, the errorlevel value always seems to
stay at 0 (even thought the .LOG file indicates that the .SQL step terminated
with: 10/1/2009 7:50:00 PM -----AQT terminating code 8)
> > > > >
> > > > > Here is my test BATCH file:
> > > > >
> > > > > "C:\Program Files\Advanced Query Tool\aqt.exe" "R:\MIS_CPW\ IAG_BD_IT\
Scripts\Send AQT Error Message Test.sql" "R:\MIS_CPW\ IAG_BD_IT\ Scripts\Send
AQT Error Message Test.log"
> > > > >
> > > > > if errorlevel 8 goto err8
> > > > > echo "AQT has completed OK"
> > > > > goto exit
> > > > >
> > > > > :err8
> > > > > echo "AQT has finished with errors"
> > > > >
> > > > > :exit
> > > > > pause Hit any key to continue
> > > > >
> > > > >
> > > > > Here is the .sql file which has several syntax errors and should fail:
> > > > >
> > > > > --aqt connect,dbs= TMS-Prod
> > > > > --aqt options,stoponerror =yes
> > > > >
> > > > > --aqt setparm,parm= Today,value= <date>
> > > > > --aqt export,file= "c:\parameter1. xls",type= excel,filemode=
replace,header= yes,prompt= no,sheet= "Sheet1"
> > > > > select first 1 (today -1) from location ;
> > > > > --aqt setparm,parm= Yesterday, value=$result( 1)
> > > > >
> > > > > --aqt export,file= "R:\MIS_CPW\ IAG_BD_IT\ Scripts\Send AQT Error
Message.xls" ,type=excel, filemode= replace,header= yes,prompt= no,sheet= Sheet1
> > > > >
> > > > > set isolation to dirty read;
> > > > >
> > > > > SELECT *
> > > > > FROM code c,
> > > > > WHERE c.bad_field_ name = 'garbage'
> > > > > ORDER BY 1;
> > > > >
> > > > > When I run the BATCH file, after it runs the AQT .sql file, an AQT
dialogue box titled "Smartuishared: aqt.exe Application Error" appears, and
there is a message that says "The instruction at "0x734fee96b" referenced memory
at "0x00000010" . The memory could not be "read". Click OK to terminate the
program."
> > > > >
> > > > > So I click on OK, and then the batch file continues from there as if
the errorlevel was 0 (and not 8).
> > > > >
> > > > > Any suggestions as to what I might be doing wrong would be
appreciated. Thanks.
> > > > >
> > > > > Glen
> > > > >
> > > >
> > >
> >
>



















[Non-text portions of this message have been removed]

Ah thanks Phil, I thought that parameter was still required.  It now works
perfectly !!   Thank you so very much.  If I every make it to New Zealand,
dinner in your favourite restaurant will most definitely be on me.  Thanks again
Phil.  We really appreciate the time you've spent getting this to work !

If you ever have a prospective customer who needs a reference about the level of
customer service that is provided by Cardett Associates, please direct them to
me.

Glen


--- In aqt@yahoogroups.com, "cardettnz" <group@...> wrote:
>
> You shoud remove the setexitcode=no parameter.
>
> Phil.
>
> --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> >
> > Hi Phil,
> >
> > Sorry for not replying - I was away for a few days vacation.
> >
> > I moved the ver 8.2.9 program into the AQT Program Files folder, and renamed
the ver 8.2.8 pgm to another name.  However, I have been unable to get the
Errorlevel value to change from 0.  Here is my test .sql query that generates a
terminating code of 8 in the .log file:
> >
> > --  Send AQT Error Message Test.sql
> >
> > --aqt connect,dbs=TMS-Prod
> > --aqt options,setexitcode=no
> >
> > set isolation to dirty read;
> >
> > SELECT   *
> > FROM     code c,
> > WHERE    c.bad_field_name = 'garbage'
> > ;
> >
> >
> > And here is my test DOS batch file:
> >
> > REM  Send AQT Error Message Test.bat
> >
> > echo Errorlevel = %ERRORLEVEL%
> >
> > pause
> >
> > "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
> >
> > echo Errorlevel = %ERRORLEVEL%
> >
> > pause
> >
> > if %ERRORLEVEL% neq 0 goto err8
> >
> > echo."AQT has completed OK"
> > goto exit
> >
> > :err8
> > echo.AQT has finished with errors.  Errorlevel was %ERRORLEVEL%
> > pause
> >
> > :exit
> > pause
> >
> > Maybe I'm doing something wrong ?   Thanks Phil.
> >
> > Glen
> >
> > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > >
> > > I've spent several days on this and think I have a resolution. Basically I
had to do a lot of code restructure so that, when running a batch script, AQT
doesn't touch any screen elements (this appeared to be the cause of the
problem).
> > >
> > > The result is at http://www.querytool.com/aqt829c.zip - let me know how it
goes.
> > >
> > > Phil.
> > >
> > >
> > > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > > >
> > > > Hi Phil,
> > > >
> > > > This site:
> > > > http://www.codeguru.com/forum/showthread.php?t=253791
> > > >
> > > > suggests that this VB code will work to set the ERRORLEVEL value:
> > > >
> > > > Option Explicit
> > > > Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)
> > > >
> > > > Private Sub Command1_Click()
> > > > ExitProcess 5
> > > > Unload Me
> > > > End Sub
> > > >
> > > > On another site, I read that we may need to initiate AQT.exe with the
START command's /WAIT parameter, in order to let it finish before continuing
with the commands in the BATCH file.  Like this:
> > > > START /WAIT AQT.EXE
> > > >
> > > >
> > > > I'm really hoping (along with David Hays and likely quite a number of
other AQT users), that we can find a solution to get ERRORLEVEL set to some
non-zero value when an error occurs.
> > > >
> > > > I upgraded to version 8.2.8 and am successfully using the setexitcode=no
Option, to prevent AQT from crashing and stopping everything.  That's a big
improvement !  Now, all we need to do is solve the ERRORLEVEL issue and we'll be
tickled pink.  Thanks a lot !
> > > >
> > > > Glen
> > > >
> > > >
> > > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > > >
> > > > > As with AQT v8.2.8, you can prevent the crash by setting:
> > > > > --aqt options,setexitcode=no
> > > > > however AQT then doesn't set the exit code so your batch script can
tell whether or not AQT has failed.
> > > > >
> > > > > Yes, I tried getting AQT set another environment variable. This then
got bogged down in the mess that Windows is in this area. If I set an
environment variable, it only applies for that process (AQT) and doesn't get
seen by the calling process (the batch script). I can set a system-wide
environment variable, but it still isn't seen by the batch script - the batch
script seems to pick these up when it starts so doesn't see any changes to them.
I spent a couple of days on this without finding a way to pass back the exit
code.
> > > > >
> > > > > Phil.
> > > > >
> > > > > --- In aqt@yahoogroups.com, "asdfghjkl" <d_haysjr@> wrote:
> > > > > >
> > > > > > Phil,
> > > > > >
> > > > > > I think there's lots of us that would love to see a solution for
that crash.  If the error occurs when AQT tries to set the errorlevel, then what
about this idea for a workaround?  Instead of setting errorlevel set some other
environment variable (e.g., AQT_error) to some value to indicate an error.  In
our batch files we could check that environment variable instead of errorlevel.
> > > > > >
> > > > > > David Hays
> > > > > >
> > > > > >
> > > > > >
> > > > > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > > > > >
> > > > > > > This is a problem with AQT. When AQT tries to set the errorlevel,
it crashes. We don't have a resolution to this unfortunately, despite having
spent quite some time on this.
> > > > > > >
> > > > > > > See http://www.querytool.com/help/1171.htm for more on this.
> > > > > > >
> > > > > > > Phil.
> > > > > > >
> > > > > > > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I am trying to get a batch file to create an errorlevel 8 DOS
error code when the AQT sql job fails.  However, the errorlevel value always
seems to stay at 0 (even thought the .LOG file indicates that the .SQL step
terminated with:  10/1/2009 7:50:00 PM -----AQT terminating code 8)
> > > > > > > >
> > > > > > > > Here is my test BATCH file:
> > > > > > > >
> > > > > > > > "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
> > > > > > > >
> > > > > > > > if errorlevel 8 goto err8
> > > > > > > > echo "AQT has completed OK"
> > > > > > > > goto exit
> > > > > > > >
> > > > > > > > :err8
> > > > > > > > echo "AQT has finished with errors"
> > > > > > > >
> > > > > > > > :exit
> > > > > > > > pause Hit any key to continue
> > > > > > > >
> > > > > > > >
> > > > > > > > Here is the .sql file which has several syntax errors and should
fail:
> > > > > > > >
> > > > > > > > --aqt connect,dbs=TMS-Prod
> > > > > > > > --aqt options,stoponerror=yes
> > > > > > > >
> > > > > > > > --aqt setparm,parm=Today,value=<date>
> > > > > > > > --aqt
export,file="c:\parameter1.xls",type=excel,filemode=replace,header=yes,prompt=no\
,sheet="Sheet1"
> > > > > > > > select first 1 (today -1) from location ;
> > > > > > > > --aqt setparm,parm=Yesterday,value=$result(1)
> > > > > > > >
> > > > > > > > --aqt export,file="R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error
Message.xls",type=excel,filemode=replace,header=yes,prompt=no,sheet=Sheet1
> > > > > > > >
> > > > > > > > set isolation to dirty read;
> > > > > > > >
> > > > > > > > SELECT   *
> > > > > > > > FROM     code c,
> > > > > > > > WHERE    c.bad_field_name = 'garbage'
> > > > > > > > ORDER BY 1;
> > > > > > > >
> > > > > > > > When I run the BATCH file, after it runs the AQT .sql file, an
AQT dialogue box titled "Smartuishared: aqt.exe  Application Error" appears, and
there is a message that says "The instruction at "0x734fee96b" referenced memory
at "0x00000010".  The memory could not be "read".  Click OK to terminate the
program."
> > > > > > > >
> > > > > > > > So I click on OK, and then the batch file continues from there
as if the errorlevel was 0 (and not 8).
> > > > > > > >
> > > > > > > > Any suggestions as to what I might be doing wrong would be
appreciated.  Thanks.
> > > > > > > >
> > > > > > > > Glen
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

You shoud remove the setexitcode=no parameter.

Phil.

--- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@...> wrote:
>
> Hi Phil,
>
> Sorry for not replying - I was away for a few days vacation.
>
> I moved the ver 8.2.9 program into the AQT Program Files folder, and renamed
the ver 8.2.8 pgm to another name.  However, I have been unable to get the
Errorlevel value to change from 0.  Here is my test .sql query that generates a
terminating code of 8 in the .log file:
>
> --  Send AQT Error Message Test.sql
>
> --aqt connect,dbs=TMS-Prod
> --aqt options,setexitcode=no
>
> set isolation to dirty read;
>
> SELECT   *
> FROM     code c,
> WHERE    c.bad_field_name = 'garbage'
> ;
>
>
> And here is my test DOS batch file:
>
> REM  Send AQT Error Message Test.bat
>
> echo Errorlevel = %ERRORLEVEL%
>
> pause
>
> "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
>
> echo Errorlevel = %ERRORLEVEL%
>
> pause
>
> if %ERRORLEVEL% neq 0 goto err8
>
> echo."AQT has completed OK"
> goto exit
>
> :err8
> echo.AQT has finished with errors.  Errorlevel was %ERRORLEVEL%
> pause
>
> :exit
> pause
>
> Maybe I'm doing something wrong ?   Thanks Phil.
>
> Glen
>
> --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> >
> > I've spent several days on this and think I have a resolution. Basically I
had to do a lot of code restructure so that, when running a batch script, AQT
doesn't touch any screen elements (this appeared to be the cause of the
problem).
> >
> > The result is at http://www.querytool.com/aqt829c.zip - let me know how it
goes.
> >
> > Phil.
> >
> >
> > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > >
> > > Hi Phil,
> > >
> > > This site:
> > > http://www.codeguru.com/forum/showthread.php?t=253791
> > >
> > > suggests that this VB code will work to set the ERRORLEVEL value:
> > >
> > > Option Explicit
> > > Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)
> > >
> > > Private Sub Command1_Click()
> > > ExitProcess 5
> > > Unload Me
> > > End Sub
> > >
> > > On another site, I read that we may need to initiate AQT.exe with the
START command's /WAIT parameter, in order to let it finish before continuing
with the commands in the BATCH file.  Like this:
> > > START /WAIT AQT.EXE
> > >
> > >
> > > I'm really hoping (along with David Hays and likely quite a number of
other AQT users), that we can find a solution to get ERRORLEVEL set to some
non-zero value when an error occurs.
> > >
> > > I upgraded to version 8.2.8 and am successfully using the setexitcode=no
Option, to prevent AQT from crashing and stopping everything.  That's a big
improvement !  Now, all we need to do is solve the ERRORLEVEL issue and we'll be
tickled pink.  Thanks a lot !
> > >
> > > Glen
> > >
> > >
> > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > >
> > > > As with AQT v8.2.8, you can prevent the crash by setting:
> > > > --aqt options,setexitcode=no
> > > > however AQT then doesn't set the exit code so your batch script can tell
whether or not AQT has failed.
> > > >
> > > > Yes, I tried getting AQT set another environment variable. This then got
bogged down in the mess that Windows is in this area. If I set an environment
variable, it only applies for that process (AQT) and doesn't get seen by the
calling process (the batch script). I can set a system-wide environment
variable, but it still isn't seen by the batch script - the batch script seems
to pick these up when it starts so doesn't see any changes to them. I spent a
couple of days on this without finding a way to pass back the exit code.
> > > >
> > > > Phil.
> > > >
> > > > --- In aqt@yahoogroups.com, "asdfghjkl" <d_haysjr@> wrote:
> > > > >
> > > > > Phil,
> > > > >
> > > > > I think there's lots of us that would love to see a solution for that
crash.  If the error occurs when AQT tries to set the errorlevel, then what
about this idea for a workaround?  Instead of setting errorlevel set some other
environment variable (e.g., AQT_error) to some value to indicate an error.  In
our batch files we could check that environment variable instead of errorlevel.
> > > > >
> > > > > David Hays
> > > > >
> > > > >
> > > > >
> > > > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > > > >
> > > > > > This is a problem with AQT. When AQT tries to set the errorlevel, it
crashes. We don't have a resolution to this unfortunately, despite having spent
quite some time on this.
> > > > > >
> > > > > > See http://www.querytool.com/help/1171.htm for more on this.
> > > > > >
> > > > > > Phil.
> > > > > >
> > > > > > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I am trying to get a batch file to create an errorlevel 8 DOS
error code when the AQT sql job fails.  However, the errorlevel value always
seems to stay at 0 (even thought the .LOG file indicates that the .SQL step
terminated with:  10/1/2009 7:50:00 PM -----AQT terminating code 8)
> > > > > > >
> > > > > > > Here is my test BATCH file:
> > > > > > >
> > > > > > > "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
> > > > > > >
> > > > > > > if errorlevel 8 goto err8
> > > > > > > echo "AQT has completed OK"
> > > > > > > goto exit
> > > > > > >
> > > > > > > :err8
> > > > > > > echo "AQT has finished with errors"
> > > > > > >
> > > > > > > :exit
> > > > > > > pause Hit any key to continue
> > > > > > >
> > > > > > >
> > > > > > > Here is the .sql file which has several syntax errors and should
fail:
> > > > > > >
> > > > > > > --aqt connect,dbs=TMS-Prod
> > > > > > > --aqt options,stoponerror=yes
> > > > > > >
> > > > > > > --aqt setparm,parm=Today,value=<date>
> > > > > > > --aqt
export,file="c:\parameter1.xls",type=excel,filemode=replace,header=yes,prompt=no\
,sheet="Sheet1"
> > > > > > > select first 1 (today -1) from location ;
> > > > > > > --aqt setparm,parm=Yesterday,value=$result(1)
> > > > > > >
> > > > > > > --aqt export,file="R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error
Message.xls",type=excel,filemode=replace,header=yes,prompt=no,sheet=Sheet1
> > > > > > >
> > > > > > > set isolation to dirty read;
> > > > > > >
> > > > > > > SELECT   *
> > > > > > > FROM     code c,
> > > > > > > WHERE    c.bad_field_name = 'garbage'
> > > > > > > ORDER BY 1;
> > > > > > >
> > > > > > > When I run the BATCH file, after it runs the AQT .sql file, an AQT
dialogue box titled "Smartuishared: aqt.exe  Application Error" appears, and
there is a message that says "The instruction at "0x734fee96b" referenced memory
at "0x00000010".  The memory could not be "read".  Click OK to terminate the
program."
> > > > > > >
> > > > > > > So I click on OK, and then the batch file continues from there as
if the errorlevel was 0 (and not 8).
> > > > > > >
> > > > > > > Any suggestions as to what I might be doing wrong would be
appreciated.  Thanks.
> > > > > > >
> > > > > > > Glen
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

Hi Phil,

Sorry for not replying - I was away for a few days vacation.

I moved the ver 8.2.9 program into the AQT Program Files folder, and renamed the
ver 8.2.8 pgm to another name.  However, I have been unable to get the
Errorlevel value to change from 0.  Here is my test .sql query that generates a
terminating code of 8 in the .log file:

--  Send AQT Error Message Test.sql

--aqt connect,dbs=TMS-Prod
--aqt options,setexitcode=no

set isolation to dirty read;

SELECT   *
FROM     code c,
WHERE    c.bad_field_name = 'garbage'
;


And here is my test DOS batch file:

REM  Send AQT Error Message Test.bat

echo Errorlevel = %ERRORLEVEL%

pause

"C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"

echo Errorlevel = %ERRORLEVEL%

pause

if %ERRORLEVEL% neq 0 goto err8

echo."AQT has completed OK"
goto exit

:err8
echo.AQT has finished with errors.  Errorlevel was %ERRORLEVEL%
pause

:exit
pause

Maybe I'm doing something wrong ?   Thanks Phil.

Glen

--- In aqt@yahoogroups.com, "cardettnz" <group@...> wrote:
>
> I've spent several days on this and think I have a resolution. Basically I had
to do a lot of code restructure so that, when running a batch script, AQT
doesn't touch any screen elements (this appeared to be the cause of the
problem).
>
> The result is at http://www.querytool.com/aqt829c.zip - let me know how it
goes.
>
> Phil.
>
>
> --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> >
> > Hi Phil,
> >
> > This site:
> > http://www.codeguru.com/forum/showthread.php?t=253791
> >
> > suggests that this VB code will work to set the ERRORLEVEL value:
> >
> > Option Explicit
> > Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)
> >
> > Private Sub Command1_Click()
> > ExitProcess 5
> > Unload Me
> > End Sub
> >
> > On another site, I read that we may need to initiate AQT.exe with the START
command's /WAIT parameter, in order to let it finish before continuing with the
commands in the BATCH file.  Like this:
> > START /WAIT AQT.EXE
> >
> >
> > I'm really hoping (along with David Hays and likely quite a number of other
AQT users), that we can find a solution to get ERRORLEVEL set to some non-zero
value when an error occurs.
> >
> > I upgraded to version 8.2.8 and am successfully using the setexitcode=no
Option, to prevent AQT from crashing and stopping everything.  That's a big
improvement !  Now, all we need to do is solve the ERRORLEVEL issue and we'll be
tickled pink.  Thanks a lot !
> >
> > Glen
> >
> >
> > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > >
> > > As with AQT v8.2.8, you can prevent the crash by setting:
> > > --aqt options,setexitcode=no
> > > however AQT then doesn't set the exit code so your batch script can tell
whether or not AQT has failed.
> > >
> > > Yes, I tried getting AQT set another environment variable. This then got
bogged down in the mess that Windows is in this area. If I set an environment
variable, it only applies for that process (AQT) and doesn't get seen by the
calling process (the batch script). I can set a system-wide environment
variable, but it still isn't seen by the batch script - the batch script seems
to pick these up when it starts so doesn't see any changes to them. I spent a
couple of days on this without finding a way to pass back the exit code.
> > >
> > > Phil.
> > >
> > > --- In aqt@yahoogroups.com, "asdfghjkl" <d_haysjr@> wrote:
> > > >
> > > > Phil,
> > > >
> > > > I think there's lots of us that would love to see a solution for that
crash.  If the error occurs when AQT tries to set the errorlevel, then what
about this idea for a workaround?  Instead of setting errorlevel set some other
environment variable (e.g., AQT_error) to some value to indicate an error.  In
our batch files we could check that environment variable instead of errorlevel.
> > > >
> > > > David Hays
> > > >
> > > >
> > > >
> > > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > > >
> > > > > This is a problem with AQT. When AQT tries to set the errorlevel, it
crashes. We don't have a resolution to this unfortunately, despite having spent
quite some time on this.
> > > > >
> > > > > See http://www.querytool.com/help/1171.htm for more on this.
> > > > >
> > > > > Phil.
> > > > >
> > > > > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I am trying to get a batch file to create an errorlevel 8 DOS error
code when the AQT sql job fails.  However, the errorlevel value always seems to
stay at 0 (even thought the .LOG file indicates that the .SQL step terminated
with:  10/1/2009 7:50:00 PM -----AQT terminating code 8)
> > > > > >
> > > > > > Here is my test BATCH file:
> > > > > >
> > > > > > "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
> > > > > >
> > > > > > if errorlevel 8 goto err8
> > > > > > echo "AQT has completed OK"
> > > > > > goto exit
> > > > > >
> > > > > > :err8
> > > > > > echo "AQT has finished with errors"
> > > > > >
> > > > > > :exit
> > > > > > pause Hit any key to continue
> > > > > >
> > > > > >
> > > > > > Here is the .sql file which has several syntax errors and should
fail:
> > > > > >
> > > > > > --aqt connect,dbs=TMS-Prod
> > > > > > --aqt options,stoponerror=yes
> > > > > >
> > > > > > --aqt setparm,parm=Today,value=<date>
> > > > > > --aqt
export,file="c:\parameter1.xls",type=excel,filemode=replace,header=yes,prompt=no\
,sheet="Sheet1"
> > > > > > select first 1 (today -1) from location ;
> > > > > > --aqt setparm,parm=Yesterday,value=$result(1)
> > > > > >
> > > > > > --aqt export,file="R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error
Message.xls",type=excel,filemode=replace,header=yes,prompt=no,sheet=Sheet1
> > > > > >
> > > > > > set isolation to dirty read;
> > > > > >
> > > > > > SELECT   *
> > > > > > FROM     code c,
> > > > > > WHERE    c.bad_field_name = 'garbage'
> > > > > > ORDER BY 1;
> > > > > >
> > > > > > When I run the BATCH file, after it runs the AQT .sql file, an AQT
dialogue box titled "Smartuishared: aqt.exe  Application Error" appears, and
there is a message that says "The instruction at "0x734fee96b" referenced memory
at "0x00000010".  The memory could not be "read".  Click OK to terminate the
program."
> > > > > >
> > > > > > So I click on OK, and then the batch file continues from there as if
the errorlevel was 0 (and not 8).
> > > > > >
> > > > > > Any suggestions as to what I might be doing wrong would be
appreciated.  Thanks.
> > > > > >
> > > > > > Glen
> > > > > >
> > > > >
> > > >
> > >
> >
>

AWESOME! thank you very much!
Laura







-----Original Message-----
From: cardettnz <group@...>
To: aqt@yahoogroups.com
Sent: Fri, Oct 16, 2009 4:47 pm
Subject: [aqt] Re: How can I use DateAdd in AQT? Trying to convert MS Access sql

























Hi there,



many functions are different between MS Access and SQL Server.



Example functions are:

DATEADD(day,-30,getdate())

plus

DATEDIFF(day,datecol,getdate())

plus

CONVERT(char,datecol,20)   returns as yyyy-mm-dd hh:mi:ss



So your function is:



left(Convert(char,DateAdd(day,-30-DateDiff(day, [Enter last report run
date],getdate()),getdate()),20),10)



Phil.



--- In aqt@yahoogroups.com, "ljscmichu" <lJSCMICHU@...> wrote:

>

> Hello-

> I'm trying to learn AQT. I have this statement in my Access query that I'm
trying to change over to AQT because I know it'll run faster.

> I tried to use the "where" tab and then DateAdd, but that's as far as I got.
Haven't been able to find a decent example to follow.

> I know much of what is in this statement is to accommodate Access' "pickyness"

>

> Anyone out there care to tackle changing this over to AQT acceptable sql?

>

>

> >=Format(DateAdd("d",-30-DateDiff("d",[Enter last report run
date],Date()),Date()),"yyyy-mm-dd")

>

> Thanks

> Laura

>


























[Non-text portions of this message have been removed]

Hi there,

many functions are different between MS Access and SQL Server.

Example functions are:
DATEADD(day,-30,getdate())
plus
DATEDIFF(day,datecol,getdate())
plus
CONVERT(char,datecol,20)   returns as yyyy-mm-dd hh:mi:ss

So your function is:

left(Convert(char,DateAdd(day,-30-DateDiff(day, [Enter last report run
date],getdate()),getdate()),20),10)


Phil.

--- In aqt@yahoogroups.com, "ljscmichu" <lJSCMICHU@...> wrote:
>
> Hello-
> I'm trying to learn AQT. I have this statement in my Access query that I'm
trying to change over to AQT because I know it'll run faster.
> I tried to use the "where" tab and then DateAdd, but that's as far as I got.
Haven't been able to find a decent example to follow.
> I know much of what is in this statement is to accommodate Access' "pickyness"
>
> Anyone out there care to tackle changing this over to AQT acceptable sql?
>
>
> >=Format(DateAdd("d",-30-DateDiff("d",[Enter last report run
date],Date()),Date()),"yyyy-mm-dd")
>
> Thanks
> Laura
>

Hello-
I'm trying to learn AQT. I have this statement in my Access query that I'm
trying to change over to AQT because I know it'll run faster.
I tried to use the "where" tab and then DateAdd, but that's as far as I got.
Haven't been able to find a decent example to follow.
I know much of what is in this statement is to accommodate Access' "pickyness"

Anyone out there care to tackle changing this over to AQT acceptable sql?


>=Format(DateAdd("d",-30-DateDiff("d",[Enter last report run
date],Date()),Date()),"yyyy-mm-dd")

Thanks
Laura

I've spent several days on this and think I have a resolution. Basically I had
to do a lot of code restructure so that, when running a batch script, AQT
doesn't touch any screen elements (this appeared to be the cause of the
problem).

The result is at http://www.querytool.com/aqt829c.zip - let me know how it goes.

Phil.


--- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@...> wrote:
>
> Hi Phil,
>
> This site:
> http://www.codeguru.com/forum/showthread.php?t=253791
>
> suggests that this VB code will work to set the ERRORLEVEL value:
>
> Option Explicit
> Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)
>
> Private Sub Command1_Click()
> ExitProcess 5
> Unload Me
> End Sub
>
> On another site, I read that we may need to initiate AQT.exe with the START
command's /WAIT parameter, in order to let it finish before continuing with the
commands in the BATCH file.  Like this:
> START /WAIT AQT.EXE
>
>
> I'm really hoping (along with David Hays and likely quite a number of other
AQT users), that we can find a solution to get ERRORLEVEL set to some non-zero
value when an error occurs.
>
> I upgraded to version 8.2.8 and am successfully using the setexitcode=no
Option, to prevent AQT from crashing and stopping everything.  That's a big
improvement !  Now, all we need to do is solve the ERRORLEVEL issue and we'll be
tickled pink.  Thanks a lot !
>
> Glen
>
>
> --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> >
> > As with AQT v8.2.8, you can prevent the crash by setting:
> > --aqt options,setexitcode=no
> > however AQT then doesn't set the exit code so your batch script can tell
whether or not AQT has failed.
> >
> > Yes, I tried getting AQT set another environment variable. This then got
bogged down in the mess that Windows is in this area. If I set an environment
variable, it only applies for that process (AQT) and doesn't get seen by the
calling process (the batch script). I can set a system-wide environment
variable, but it still isn't seen by the batch script - the batch script seems
to pick these up when it starts so doesn't see any changes to them. I spent a
couple of days on this without finding a way to pass back the exit code.
> >
> > Phil.
> >
> > --- In aqt@yahoogroups.com, "asdfghjkl" <d_haysjr@> wrote:
> > >
> > > Phil,
> > >
> > > I think there's lots of us that would love to see a solution for that
crash.  If the error occurs when AQT tries to set the errorlevel, then what
about this idea for a workaround?  Instead of setting errorlevel set some other
environment variable (e.g., AQT_error) to some value to indicate an error.  In
our batch files we could check that environment variable instead of errorlevel.
> > >
> > > David Hays
> > >
> > >
> > >
> > > --- In aqt@yahoogroups.com, "cardettnz" <group@> wrote:
> > > >
> > > > This is a problem with AQT. When AQT tries to set the errorlevel, it
crashes. We don't have a resolution to this unfortunately, despite having spent
quite some time on this.
> > > >
> > > > See http://www.querytool.com/help/1171.htm for more on this.
> > > >
> > > > Phil.
> > > >
> > > > --- In aqt@yahoogroups.com, "glen.wenzel" <glen.wenzel@> wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I am trying to get a batch file to create an errorlevel 8 DOS error
code when the AQT sql job fails.  However, the errorlevel value always seems to
stay at 0 (even thought the .LOG file indicates that the .SQL step terminated
with:  10/1/2009 7:50:00 PM -----AQT terminating code 8)
> > > > >
> > > > > Here is my test BATCH file:
> > > > >
> > > > > "C:\Program Files\Advanced Query Tool\aqt.exe"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.sql"
"R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error Message Test.log"
> > > > >
> > > > > if errorlevel 8 goto err8
> > > > > echo "AQT has completed OK"
> > > > > goto exit
> > > > >
> > > > > :err8
> > > > > echo "AQT has finished with errors"
> > > > >
> > > > > :exit
> > > > > pause Hit any key to continue
> > > > >
> > > > >
> > > > > Here is the .sql file which has several syntax errors and should fail:
> > > > >
> > > > > --aqt connect,dbs=TMS-Prod
> > > > > --aqt options,stoponerror=yes
> > > > >
> > > > > --aqt setparm,parm=Today,value=<date>
> > > > > --aqt
export,file="c:\parameter1.xls",type=excel,filemode=replace,header=yes,prompt=no\
,sheet="Sheet1"
> > > > > select first 1 (today -1) from location ;
> > > > > --aqt setparm,parm=Yesterday,value=$result(1)
> > > > >
> > > > > --aqt export,file="R:\MIS_CPW\IAG_BD_IT\Scripts\Send AQT Error
Message.xls",type=excel,filemode=replace,header=yes,prompt=no,sheet=Sheet1
> > > > >
> > > > > set isolation to dirty read;
> > > > >
> > > > > SELECT   *
> > > > > FROM     code c,
> > > > > WHERE    c.bad_field_name = 'garbage'
> > > > > ORDER BY 1;
> > > > >
> > > > > When I run the BATCH file, after it runs the AQT .sql file, an AQT
dialogue box titled "Smartuishared: aqt.exe  Application Error" appears, and
there is a message that says "The instruction at "0x734fee96b" referenced memory
at "0x00000010".  The memory could not be "read".  Click OK to terminate the
program."
> > > > >
> > > > > So I click on OK, and then the batch file continues from there as if
the errorlevel was 0 (and not 8).
> > > > >
> > > > > Any suggestions as to what I might be doing wrong would be
appreciated.  Thanks.
> > > > >
> > > > > Glen
> > > > >
> > > >
> > >
> >
>