Please help.
The following query will run but won't give me what I need.
When I execute the query and enter the name of a query to retrieve the
query data from I get layout data of the query name entered instead !
i.e. When I enter the query name 'jobhis' I don't get the data stored in
ttadv380 but get the layout data of the jobhis query while ttadv380 doesn't contain
any layout data.

select ttadv380.*
from ttadv380
where ttadv380._compnr = 000
and ttadv380.cqur >= ttadv380.cqur.f
and ttadv380.cqur <= ttadv380.cqur.t

When I enter : jobhis

I should get :
----------------
Field pos type len # value
cqur 1) 1 str 9 1 jobhis
logn 10 str 12 1 wg01
desc 22 mbst 30 1 Job history overzicht.
desi 52 str 12 1 Walter
date 64 date 4 1 13-08-2002
outp 68 enum 1 1 1 (ttadv.qotp.list)
rcmp 69 enum 1 1 2 (ttyeno.no)
repo 70 str 14 1 tcsqljobhis
prop 84 str 25 1 ascif
selc 109 text 4 1 157724

But I get :
----------------

Datum: 14-01-03 [17:11] Job history overzic
Logistiek I'P en B'W

-----------------------------------------------------------------------------------------------------
Job | Omschrijving |Uitvoeringsdatum | Reden | Uitvoeringstijd | Beëindigingsdatum | Beëindigingstijd
----+--------------+-----------------+-------+-----------------+-------------------+-----------------
| | | | 00:00 | | 00:00

What's going on ?

Walter

It seems that you want a list of jobs using Easy SQL. You should create a query like:

select ttaad510.*
from ttaad510
where ttaad510.cjob >= ttaad510.cjob.f
and ttaad510.cjob <= ttaad510.cjob.t

Is that what you want?

Hello Niek,

I need a list with Query names and the related report names and text numbers.

Walter

Hi,

What is the contents if you do ttaad4500 on the ttadv380 table in company 000?

GN

Hi GN,

These are the contents of ttaad4500 -> ttadv380 in 000.

Field pos type len # value
cqur 1) 1 str 9 1 jobhis
logn 10 str 12 1 wg01
desc 22 mbst 30 1 Job history overzicht.
desi 52 str 12 1 Walter
date 64 date 4 1 13-08-2002
outp 68 enum 1 1 1 (ttadv.qotp.list)
rcmp 69 enum 1 1 2 (ttyeno.no)
repo 70 str 14 1 tcsqljobhis
prop 84 str 25 1 ascif
selc 109 text 4 1 157724


Walter

Hi,

Has the data, which is now in the ttadv380 table, been imported from another company, or has the data been put into the ttadv380 table after running the batchjob?
Does it concern ALL the data which is in the ttadv380 or are there also records with correct values?

GN

try using the same query from the command line via the qptool6.1 - if that works then maybe its restrictions on the fields etc - if you are not superuser.

GN,

All the data in ttadv380 has been entered with session ttadv3180m000 "Edit Query".
Also we ar not experiencing any problems with any query. Except the one listed above.
When I enter any query name that exists in ttadv380, I get the layout of that
query instead of the query data as present in ttadv380. So it doesn't concern
specific queries.


NR,

I'm superuser.
I have tried the same query with qptool6.1 and that works !!!

Output shown below:
-------------------
' ' ' ' ' ' 0 'jobhis ' 08132002 'Job history overzicht. ' 'Walter ' 1 'wg01 ' 0 1 'ascif ' 2 'tcsqljobhis ' 0 157724 0


Maybe it has something to do with the report that goes wrong ?
I have removed the query+report and renamed, typed-in, regenerated, compiled the query+report several times
but to no avail.

Run the job with setting:
-- -set TT_SQL_TRACE=041 -set DBSLOG=010 -keeplog -logfile trace.txt ttaad5203m000
and see what happens with your data in the dbs.log file and in the trace.txt file.

I ran the query with :
ba6.1 -- -set TT_SQL_TRACE=041 -set DBSLOG=7577 -keeplog -logfile trace.txt ttadv3280m000

Below is an extract of the DBSLOG file.

(Somewhere in DBSLOG a select is done for the query name jobhis)

12349 ----- DBMS Where Input ----
12350 Bind nr 1 : cqur : string : 'jobhis '
12351 SQL> SELECT /*+ FIRST_ROWS index(a tttadv380000$idx1) */ a.t$cqur,a.t$repo,a.t$selc
FROM baan.tttadv380000 a WHERE a.t$cqur = :1

(Baan finds the record(s) I'm looking for. Queryname, Reportname and Textnumber)

12364 ------ QPS Output Row -------
12365 Bind 1 : cqur : string : 'jobhis '
12366 Bind 2 : repo : string : 'tcsqljobhis '
12367 Bind 3 : selc : long : <157724>

(What is this for ?)

12387 ----- DBMS Where Input ----
12388 Bind nr 1 : pacc : string : ' '
12389 Bind nr 2 : keyr : string : 'moduttstp '
12390 SQL> SELECT /*+ index(a tttadv999000$idx1) */ t$pacc,t$keyr,t$desc,t$Refcntd,t$Refcntu
FROM baan.tttadv999000 a WHERE t$pacc=:1 AND t$keyr=:2

(I guess this has something to do with the print spooler)

12425 ----- DBMS Where Input ----
12426 Bind nr 1 : pacc : string : 'OPER_C3 '
12427 Bind nr 2 : keyr : string : 'progttstpsplopen 01'
12428 SQL> SELECT /*+ index(a tttadv999000$idx1) */ t$pacc,t$keyr,t$desc,t$Refcntd,t$Refcntu
FROM baan.tttadv999000 a WHERE t$pacc=:1 AND t$keyr=:2

(Further down DBSLOG Baan searches for the jobhis report which I didn't specify)

12787 ----- DBMS Where Input ----
12788 Bind nr 1 : clan : string : '1'
12789 Bind nr 2 : cpac : string : 'tc'
12790 Bind nr 3 : cmod : string : 'sql'
12791 Bind nr 4 : crep : string : 'jobhis '
12792 SQL> SELECT /*+ FIRST_ROWS index(a tttadv330000$idx1) */ a.t$bmrg,a.t$clan,a.t$cmod,a.t$cpac,a.t$crep,
a.t$cust,a.t$date,a.t$desc,a.t$desi,a.t$expi,a.t$fmrg,a.t$font,a.t$lanl,a.t$lmrg,a.t$mwhu,a.t$nrlb,
a.t$oblf,a.t$plen,a.t$rele,a.t$rtim,a.t$scpt,a.t$tmrg,a.t$txtc,a.t$vers,a.t$wlen
FROM baan.tttadv330000 a WHERE a.t$clan = :1 AND a.t$cpac =:2 AND a.t$cmod = :3 AND a.t$crep = :4

I can't find an entry in the DBSLOG file where Baan starts processing my query output as if it has to be executed or something, but I'll keep searching.

walter01: 12351 SQL> SELECT /*+ FIRST_ROWS index(a tttadv380000$idx1) */ a.t$cqur,a.t$repo,a.t$selc
FROM baan.tttadv380000 a WHERE a.t$cqur = :1You run a level 2 DBdriver, the query gets passed to the DBMS(What is this for ?)Don't know12390 SQL> SELECT /*+ index(a tttadv999000$idx1) */ t$pacc,t$keyr,t$desc,t$Refcntd,t$Refcntu
FROM baan.tttadv999000 a WHERE t$pacc=:1 AND t$keyr=:2ttadv999 is user defaults per session in this case the ttstpsplopen, so at this time your query has run already and the data (data is found) is going to be spooled to your report by the name of ...12788 Bind nr 1 : clan : string : '1'
12789 Bind nr 2 : cpac : string : 'tc'
12790 Bind nr 3 : cmod : string : 'sql'
12791 Bind nr 4 : crep : string : 'jobhis '
12792 SQL> SELECT /*+ FIRST_ROWS index(a tttadv330000$idx1) */ a.t$bmrg,a.t$clan,a.t$cmod,a.t$cpac,a.t$crep,
a.t$cust,a.t$date,a.t$desc,a.t$desi,a.t$expi,a.t$fmrg,a.t$font,a.t$lanl,a.t$lmrg,a.t$mwhu,a.t$nrlb,
a.t$oblf,a.t$plen,a.t$rele,a.t$rtim,a.t$scpt,a.t$tmrg,a.t$txtc,a.t$vers,a.t$wlen
FROM baan.tttadv330000 a WHERE a.t$clan = :1 AND a.t$cpac =:2 AND a.t$cmod = :3 AND a.t$crep = :4Your query wants to send data don't you? Data gets printed to a report, this is your (by the query generated) report which can only be found when the current developers package combination is set to the same package-VRC as where your report is in, in this case tc and the VRC of the report.

I didn't mention this before but my test query is named wgtest08.
At compile time it generated a report called tcsqlwgtest08 which is correct.
I'm sorry about the dutch language but here are the settings for my test query.

+--- My test Query --------------------------------------------------+
| Query : wgtest08 Bedrijf: 000 |
| Gebruiker : |
| Omschrijving : Restore data test. |
| Aangemaakt door: wg01 |
| Datum : 14-01-03 |
| Soort uitvoer : Reportlijst Helpinformatie : Nee |
| Report : tcsqlwgtest08 Tech. document. : Nee |
| Opties printen : ascif |
+--------------------------------------------------------------------+

+--- Generated Report data ------------------------------------------+
| Taal : 1 Nederlands VRK : B40O c3 live|
| Pakket : tc Common BAAN IV |
| Module : sql Easy SQL |
| Report : wgtest08 Vervallen : Nee |
| Aan taal kopp : |
| Omschrijving : Restore data test. |
| BAAN-gebruiker : wg01 |
| Datum : 14-01-03 |
| Reportgrootte : |
| Script aanwezig: Nee Tech. document. : Nee |
| Helptextcode : Release notes : Nee |
+--------------------------------------------------------------------+

When I execute the query it is supposed to use the wgtest08 report, but it uses
the report of the query name I entered in the ttadv380.cqur field of the select statement.
I used the name 'jobhis' as an example, but when I execute the wgtest08 query it
always uses the report of the query name I entered in the select statement.

As you can see in the lines 12788 through 12791 of the DBSLOG file the wrong report
is used for sending the query output to.
It should be:
Bind nr 1 : clan : string : '1'
Bind nr 2 : cpac : string : 'tc'
Bind nr 3 : cmod : string : 'sql'
Bind nr 4 : crep : string : 'wgtest08' ( and not jobhis )

Vrc settings etc are correct. All my (working) queries are in this pvrc.

The only other thing I can think of - re-compile the actual easy sql report and see if that fixes the problem.

also did you use the query by example or actualy write the query using the text manager option in the easy SQL session ? I have noticed problems when using the query by example and always end up by writing the sql myself.

I always write the query myself and I have renamed, removed, recompiled, used other names etc. But I still get the same problem.

Your data gets selected (according DBSLOG), but not printed on a correct report... The report handling is done by ttstpsplopen / ttstpsplclose. Get the newest versions of these.
What tmp files are being made?

Two temp files are created. They both contain the layout of the
wrong report. Also the report script of the wrong report is executed.
I can't try the latest version of ttstpsplopen / ttstpsplclose since our test machine is currently down.

could be the the "wrong" version of the report object/script is being picked up. Use the Display Objects by Report session and make sure the option to see all VRC's is set in you current PVRC setting. Mybe there is an report/object named the same but it is only designed to display the few fields, and this report/object is being picked up instead of the "wanted" version that displays all the data.

- The settings in ttadv0140m000 allow me to see all available software components.
- Session ttadv3536m000 "objects/report" shows vrc B40Oc3live for both the source
and object vrc. No other versions of sqlwgtest08 are present.
I also checked this on the OS level.

./tcB40O_c3_live/otcsql1/osqlwgtest081
./tcB40O_c3_live/otcsql1/usqlwgtest081

well, I really cannot think of anything else other than logging a case with Baan support......unless someone else comes up with something.

What if you run with
-- -keeplog -logfile fileaccs.txt -dgbfdev ttaad5203m000
Can you see the wrong report come by? Also the right one with an error?

Maybe it is a good idea to reorganize all of the ttadv tables with session ttadv4228m000 to repair the files if they are corrupt (index wrong) and then try again?

Everybody thanks for your efforts but this is what I got from Baan Support.

SITUATION DESCRIPTION:
Querying the ttadv380 table gives strange results.

SOLUTION DESCRIPTION:
In the session it self also the ttadv380 table is used.
And the record read in the session itself is overwritten with the record from the query.
Therefor you see this stange behaviour.

To get this fully solved all records used in the session should be buffered.
This has to much impact on the session behaviour and also on the performance etc...
And also on the stability of the session.
And it will not only be the ttadv380 table but all other tables used within this session.

So you should accept this side effect when running this session on tables used by this session.

-----

Since qptools does give me the list I require I'll have to figure something out outside Baan.

Hello,

We were facing the same problem and opened an inquiry.
The problem was solved after reorganization of ttadv380 (thru ttaad4225) !

The only remining small problem is that baan goes to a different qury after processing the one on the table ttadv380, but the report is OK.

Regards

Hello Richard,

I wonder how reliable your solution is. I won't be suprised if you'll need to reorganize again some time in the future.

Walter

Hi Walter,

you could allways use a small script in your wgtest08 report to get the data.
(Like we did all the time ..... ;) .....maybe you'll recognize my profile )
just start with some silly small query to get the report started and use the layout.again() command to get all your details from adv380