Re: remove dups
Milos Prudek wrote:
>
> > I can see what you are trying with the SELECT FIRST, but you need the
> > FIRST of each group. So I opted for a stored procedure. Here is my test
> > case:
>
> Hi Mark,
>
> Thank you very much for your reply.
No problem.
> Now let me go a bit deeper with -944 error:
>
> I have two tables with many-to-many relationship, and I need to use just
> the first row from the second table. Example:
>
> primary table:
> id amount
> 1 20
> 1 30
> 2 10
> 3 20
> 3 40
>
> secondary table:
> id text
> 1 one
> 1 oonnee
> 2 two
> 2 ttwwoo
>
> desired result:
> id amount text
> 1 20 one
> 1 30 one
> 2 10 two
> 3 20
> 3 40
>
> (I don't care if the text is "two" or "ttwwoo", as long as it's always
> the same text. Let's say that I want the shortest text from the
> secondary table.)
Okay, I haven't tested this, but what about something like:
------------------------------------------------------------------------
CREATE PROCEDURE dedup()
RETURNING INT, INT, CHAR(20);
DEFINE i_id LIKE primary.id;
DEFINE i_amount LIKE primary.amount;
DEFINE o_text LIKE secondary.text;
DEFINE o_len INT;
FOREACH SELECT id, amount
INTO i_id, i_amount
FROM primary
ORDER BY id
LET o_len = 0;
LET o_text = "";
FOREACH SELECT text, LENGTH(text)
INTO o_text,
o_len
FROM secondary
WHERE secondary.id = i_id
ORDER BY 2
EXIT FOREACH;
END FOREACH;
RETURN i_id, i_amount, o_text WITH RESUME;
END FOREACH;
END PROCEDURE;
------------------------------------------------------------------------
> (I know that such relations should not be needed in good database
> design. Unfortunately, one of the tables if from an external source that
> I cannot influence)
Right!
> Since I couldn't figure a suitable select, I decided to simplify the
> problem: I added the "text" column to the structure of primary, and I
> wanted to use UPDATE:
>
> update primary
> set text=
> (select first 1 text from secondary
> where primary.id=secondary.id)
>
> ... This gives error 944: Unknown error
>
> Okay, I did not order secondary. So:
>
> update primary
> set text=
> (select first 1 text from secondary
> where primary.id=secondary.id order by text)
>
> ... Syntax error at the start of "order by".
>
> But I'm sure that SELECT FIRST per se works fine, because:
>
> select first 1 id,text from secondary where id="1">
> ...is OK.
>
> What's the problem?
I don't think you can use SELECT FIRST in sub-queries. I even had
trouble inside a nested SELECT in a stored procedure. It's probably
mentioned in the manual if you dig deep enough. ;-)
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+