Hi,
nice problem.
Ok, let's try.
First we are searching for the row with actual date(the current date or the row
with the lowest jahr/monat (year/month) combination?).
BTW: auftrag means order.
If we assume the current month, we use this:
declare current_rows cursor for
select *
from xyz
where jahr = year(date) and monat = month(date)
for reuse
If I assume the lowest, this makes things worse:
declare current_rows cursor for
select *
from xyz seltab
where (pnr, auftrag, makedate(jahr, monat)) = any
(select pnr, auftrag, min (makedate(jahr, monat))
from xyz
group by pnr, auftrag)
for reuse
Now we have to build an update on top:
update xyz
set (vorgabe1, vorgabe2, vorgabe3, vorgabe4) =
(select vorgabe1, vorgabe2, vorgabe3, vorgabe4
from current_rows subtab
where xyz.pnr = subtab.pnr and xyz.auftrag = subtab.auftrag)
where NOT (jahr = year(date) and monat = month(date))
The two things (one select and the update) cannot be put into one
command as then the updated table is the same as that used for selecting.
And (not in your case but in other possible cases) the select may differ
depending when it will be done (before the first update or after having updated
some of the rows or...) And then the resulting update would differ depending
on the handling of this correlation. This has to be avoided, therefore the
select and the update have to be two commands to prepare another 'table'
with the needed current_rows.
And if you are using a client / a tool not being able to handle user-specified
resulttablenames, then do not say
declare .. cursor for select ...
but
create table temp.current_rows as select ...
With my mini-test both select/update-combinations work.
They differ of course in the result in case the first row is not filled with
the current month.
Good luck.
Elke
SAP Labs Berlin
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi,
Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso
Plattner
Registergericht/Commercial Register Mannheim No HRB 350269
Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige
vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich
erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung
oder Weitergabe der E-Mail ausdrücklich untersagt.
Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen
Dank.
This e-mail may contain trade secrets or privileged, undisclosed, or otherwise
confidential information. If you have received this e-mail in error, you are
hereby notified that any review, copying, or distribution of it is strictly
prohibited. Please inform us immediately and destroy the original transmittal.
Thank you for your cooperation.
> -----Original Message-----
> From: Beermann, Albert [mailto:[EMAIL PROTECTED]
> Sent: Freitag, 11. Mai 2007 09:51
> To: maxdb@lists.mysql.com
> Subject: SQL Statement needed (if possible)?
>
> Hello Everybody
>
>
>
> I use Maxdb 7.6
>
> I need an idea for the following problem
>
>
>
> Table xyz
>
> id = unique key
>
> pnr
>
> auftrag
>
> jahr
>
> monat
>
> vorgabe1
>
> vorgabe2
>
> vorgabe3
>
> vorgabe4
>
>
>
> I have one record for
>
> pnr(111),auftrag(xxx),jahr(2007),monat(5)
>
>
>
> I have 6 future records for the same pnr,auftrag combination !!
>
> pnr(111),auftrag(xxx),jahr(2007),monat(6)
>
> pnr(111),auftrag(xxx),jahr(2007),monat(7)
>
> ...
>
> pnr(111),auftrag(xxx),jahr(2007),monat(11)
>
>
>
> Only the actual record(2007,5) has correct vorgabe1 to vorgabe4 values
>
> Now i have to push vorgabe1 to vorgabe4 from the actual
> record into the
> 6 future records!?
>
>
>
> Can this be done with an sql command ???
>
> Any help welcomed
>
>
>
> Best regards
>
> Albert
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
