On Tuesday 11 November 2003 10:05, Svein Erling wrote:

> Hi Yves!
>
> --- In firebird-support@yahoogroups.com, Yves Glodt <yg@m...> wrote:
> > Hello,
> >
> > please consider this query:
> >
> > SELECT
> > WT_CORR.EFFECT_DATE,
> > WT_CORR.CORR_TYPE,
> > WT_CORR.CORR_STATE,
> > WT_CORR.CORR_INFO_1,
> > WT_CORR.CORR_INFO_2,
> > WT_CORR.CORR_INFO_3,
> > WT_CORR.TYPE_CORR,
> > WT_CORR.CREATOR,
> > WT_NOMS.NAME_NAME,
> > WT_MOTIFS.REASON_COLOUR,
> > WT_MOTIFS.REASON_TYPE
> > FROM WT_CORR,WT_NOMS,WT_MOTIFS
> > WHERE WT_CORR.IPN=31 AND
> > WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end
>
> AND
>
> > WT_NOMS.NAME_TYPE=1400 AND
> > (WT_CORR.CORR_TYPE = WT_NOMS NAME_NUMBER) AND
> > (WT_CORR.CORR_TYPE = WT_NOMS.REASON_NUMBER)
> > ORDER BY WT_CORR.EFFECT_DATE
> >
> >
> > This is a rough draft, but it works well.
> > The 9th column it returns is the textual name of the numeric value
>
> from
>
> > column 2. Names are stored in the WT_NOMS table, and in this query
>
> are
>
> > linked through the section number "WT_NOMS.NAME_TYPE = 1400"
> >
> >
> > But I need to get another name from the WT_NOMS table, in a section
>
> that
>
> > I get in the 10th column, which of course will vary for each
>
> returned
>
> > row...
> >
> > Is there a way to use values returned in a row in a WHERE clause in
>
> the
>
> > same query, like "where WT_NOMS.NAME_TYPE=$(value_from_column #10)"
> > I was thinkin about a subquery, but I would also need a former
>
> result in
>
> > the WHERE.
> >
> > Is this possible at all with bare sql?
>
> Probably, the reason that you haven't got any reply may be that we do
> not quite understand what you want to do.
>
> Try something like:
>
> SELECT
> WT_CORR.EFFECT_DATE,
> WT_CORR.CORR_TYPE,
> WT_CORR.CORR_STATE,
> WT_CORR.CORR_INFO_1,
> WT_CORR.CORR_INFO_2,
> WT_CORR.CORR_INFO_3,
> WT_CORR.TYPE_CORR,
> WT_CORR.CREATOR,
> WN1.NAME_NAME,
> WT_MOTIFS.REASON_COLOUR,
> WN2.NAME_NAME as COLOURNAME
> WT_MOTIFS.REASON_TYPE
> FROM WT_CORR
> JOIN WT_NOMS WN1 ON
> WN1.NAME_NUMBER = WT_CORR.CORR_TYPE
> JOIN WT_NOMS WN2 ON
> WN2.NAME_NUMBER = WT_MOTIFS.REASON_COLOUR
> JOIN WT_MOTIFS ON
> WT_CORR.CORR_TYPE = WT_MOTIFS.REASON_NUMBER
> WHERE
> WT_CORR.IPN=31 AND
> WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end AND
> WN1.NAME_TYPE=1400 AND
> WN2.NAME_TYPE=1400 /*I don't know whether you want this
> ORDER BY WT_CORR.EFFECT_DATE
>
> As you see, I've also rewritten your query from SQL-89 to SQL-92
> (i.e. used JOIN). This query should work whether or not you do that,
> but in general it is preferable to use JOIN.

Thank you Svein, I resolved the problem by myself, and your introduction
to SQL-92 was a big help!!

Thanks again, and best regards,
Yves

> HTH,
> Set
>
> - I support Firebird, I am a FirebirdSQL Foundation member.
> - Join today at http://www.firebirdsql.org/ff/foundation
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~--> Buy Ink Cartridges or Refill Kits for your
> HP, Epson, Canon or Lexmark Printer at MyInks.com. Free s/h on orders
> $50 or more to the US & Canada.
> http://www.c1tracking.com/l.asp?cid=5511
> http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/67folB/TM
> ---------------------------------------------------------------------
>~->
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/

--
Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
22:49:28 up 14 min, 1 user, load average: 0.40, 0.34, 0.23