we are running SAPDB 7.3.0.30 on our productive System and now SAPDB 7.4.3.27 on our Testsystem.
We have a problem with a SQL Statement on our Test system (7.4) which works fine in the productive system (7.3). In both cases we start the Query using either SQL Studio (7.3 / 7.4) or the DBD::ODBC connection via perl.
Here the query:
DECLARE C CURSOR FOR WITH RECURSIVE CLUBTREE (ikNodeId, ikNodeParent,iNodeLevel) AS
( SELECT ikClub, ikClubparent, 0
FROM Clubstructure
WHERE ikClub IN ( 241 )
UNION ALL
SELECT ikClub, ikClubParent, iNodeLevel + 1
FROM ClubStructure, CLUBTREE
WHERE CLUBTREE.ikNodeId = ClubStructure.ikClubParent )
SELECT vNamefirst, vNamelast, vTime, Sc.vSex AS vSex,
Sc.ikClubhome AS ikClub, Club.vClub, vDescription, vPlace,
Sc.ikPerson as ikPerson, iBirthyear, ikAgecatrelay, dDate,
Sc.ikChampionship AS ikChampionship
FROM Sctime Sc, Person, Club, Championship Ch
WHERE Sc.ikClubhome IN ( SELECT DISTINCT ikNodeId FROM Clubtree )
AND Sc.dDate BETWEEN '2004-01-01' AND '2004-03-06'
AND Sc.ikDiscipline = 15 AND Sc.vSex = 'M' AND Sc.vkStadiontype = '50'
AND Sc.vOfficial = 'Y' AND Sc.ikPerson = Person.ikPerson
AND Sc.ikClubhome = Club.ikClub
AND Sc.ikChampionship = Ch.ikChampionship
ORDER BY vTime, ikPerson, ikClubhome-- End Query ---
The error message is "Base table or view not found;-4004 POS(668) Unknown table name:CLUBTREE"
If we replace the second select statement with e. g. "SELECT DISTINCT ikNodeId FROM Clubtree" it works fine.
We assume the problem is that the "Clubtree" in the second SELECT occurs in a subquery. But is this a bug or a feature? :-)
Thanks for your hints and best regards
Hannes Degenhart GPS mbH
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
