Insert .. into .. select aSequence.nextval , .. From .. Left outer join (View). 2006-10-13 - By Michael Neuber
Back
Hi folks,
I am about to gather some countings from old data
into some tables which hold graphics information (coordinates).
For that, I
(a) created some Calendar table, just holding nothing else but all
relevant Calendar dates,
(b) a view, which counts some DB entries of other tables in the DB
according to their state,
and then I try to do something like the following (in Oracle Mode):
INSERT INTO
survey_Data (dataID,t_EvaluationDate, y_cnt1, y_cnt2, ...)
SELECT
dataID.nextval,
cal.aDay,
nvl(View1.count,0) as cnt_1,
nvl(View2.count,0) as cnt_2,
...
FROM dba.Calendar cal
LEFT OUTER JOIN dba.userView_Special View1
ON View1.date=cal.aDay AND View1.state='green'
LEFT OUTER JOIN dba.userView_Special View2
ON View2.date=cal.aDay AND View2.state='blue'
....
where cal.aDay between to_Date('2000-01-01','YYYY-DD-MM') and sysdate
A bit disappointing now is, that SQLStudio rejects this query with:
General Error : -9000 POS ... System Error: Not yet implemented.
...
Thereby, in the repeated query statement in the status window, the comma
after dataID.nextval is marked red (hard to recognize, though).
So far I found out, that
(a) an INSERT INTO ... SELECT.... works fine, if I just use the sequence
dataID.nextval and some static values,
(b) the INSERT INTO ... SELECT.... works fine, if I use the complex
query and try to create the value for the PK dataID in another way, e.g.
if I derive it using some differences using cal.aDay and some static date,
(c) but each time I combine the LEFT OUTER JOINS on the views and the
sequence, the whole query is rejected as described above.
For certain cases, I am able to use a work-around as described in (b),
but not for all cases yet to come. That's why I am really interested in
a solution for the problem, or at least some hints on a better
work-around and the date/version of MaxDB 7.5 /7.6 in which the
possibility to use sequences in the described way will be given.
Any comments appreciated.
Regards
Michael Neuber
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
