List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 17 1999 5:37pm
Subject:Re: complex select
View as plain text  
On Fri, 1999-09-17 12:35:55 -0400, James Manning wrote:
> > This should it make much easier to work with this data ...
> 
> I'd certainly agree, but with the tables as they stand, is there
> any option of using COALESCE in the select and with a single query
> use the coalesce output as part of the WHERE clause?

Unfortunately column aliases can't be used in the WHERE part, but just
repeating whole the COALESCE term should work.
I'm still with V3.22.19b here so I can't test this myself, but I see
no reason why it shouln't work.

Replacing COALESCE with a sequence of IFNULL() functions, the query
might look like this:

  SELECT
     IFNULL(H_ORG_B_TDM,IFNULL(H_ORG_C_TDM,H_ORG_D_TDM)) AS tandem
   , LERG_7.STREET
   , LERG_7.CITY
   , LERG_7.STATE
   , LERG_1.OCN_NAME
  FROM
     LERG_7_SHA
   , LERG_7
   , LERG_1
  WHERE
       LERG_7_SHA.SWITCH='WLBNNYXARD0'
   AND IFNULL(H_ORG_B_TDM,IFNULL(H_ORG_C_TDM,H_ORG_D_TDM))=LERG_7.SWITCH
   AND LERG_7.OCN=LERG_1.OCN;

God, I'd choose different table and column names ... :)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
unable to compile 3.23.3 with any charset besides defaultAleksandar Ivanisevic17 Sep
  • select uniqueNathan Stratton17 Sep
    • Re: select uniqueJames Manning17 Sep
    • complex selectNathan Stratton17 Sep
      • Re: complex selectJames Manning17 Sep
      • Re: complex selectMartin Ramsch17 Sep
        • Re: complex selectJames Manning17 Sep
          • Re: complex selectMartin Ramsch17 Sep
        • Re: complex selectNathan Stratton17 Sep
          • Re: complex selectMartin Ramsch18 Sep
            • Re: complex selectJames Manning18 Sep
    • Re: select uniqueTonu Samuel17 Sep
  • unable to compile 3.23.3 with any charset besides defaultMichael Widenius19 Sep
  • unable to compile 3.23.3 with any charset besides defaultMichael Widenius19 Sep
  • Re: unable to compile 3.23.3 with any charset besides defaultAleksandar Ivanisevic26 Sep
Re: unable to compile 3.23.3 with any charset besides defaultMichael Widenius27 Sep
  • Re: unable to compile 3.23.3 with any charset besides defaultAleksandar Ivanisevic28 Sep