Re: Inline Query ?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 14 Aug 2003 06:09:46 -0700
Message-ID: <1a75df45.0308140509.23de5bc5@posting.google.com>

message_4_u2001_at_yahoo.com (Veronica Loria) wrote

> SELECT
> to_char(start_date, 'YYYYMM'),
> count(home_phone_number) "Valide Customers"
> FROM
> customer
> WHERE
> home_phone_number NOT LIKE '%##%' AND
> (start_date between add_months(sysdate, -13) AND
> add_months(sysdate, -1))
> GROUP BY to_char(start_date, 'YYYYMM')
> ORDER BY to_char(start_date, 'YYYYMM');

<snipped>

> However, do you know of a way I can add to this query a 3rd column
> that would give me the total base records for each month ? so
> basically the 3rd column would contain the monthly totals of ALL
> records created (valid and invalid)...

For that you need some IF THEN ELSE logic in the SELECT part.Something like:
IF home_phone_number NOT LIKE '%##%'

   ADD 1 TO VALID_CUSTOMERS. And a simple COUNT(*) for getting the TOTAL_BASE.

So here goes:

SELECT
   to_char(start_date, 'YYYYMM'),
   count(home_phone_number) "TOTAL BASE",    sum(
   decode(

       trim(
        translate( lower(home_phone_number),
         'abcdefghijklmnopqrstuvwxyz1234567890',
         '                                    ' 
                  )
            ) ,
       '##', 1,
        0 )

    ) "VALID_CUSTOMERS"
FROM
  customer
WHERE
  (start_date between add_months(sysdate, -13) AND   add_months(sysdate, -1))
GROUP BY to_char(start_date, 'YYYYMM') ORDER BY to_char(start_date, 'YYYYMM');

To explain.

I use the TRANSLATE function to change all valid characters into spaces. Thus '1234##1234GOOD' becomes ' ## '.

The TRIM function changes that to '##'.

Play with this to see how it works:
 select
   trim(

       translate( lower('12983ABC##187263goofy'),
         'abcdefghijklmnopqrstuvwxyz1234567890',
         '                                    '
                )
       )   TRANSLATED_RESULT

  from dual

The DECODE wraps it and says:
  if TRANSLATE & TRIM returns '##' then

     return 1
  else

     return 0
  end if

The SUM wraps the DECODE and does the summing of the DECODED results.

Oh yeah - also used the LOWER function to deal with only lower chase chars. It does not deal with ?, periods and other characters though. You may want to add them to the translation and also make them spaces.

Another option would be to do 2 SQLs. One for valid numbers. One for all numbers. Then you join these on month.

However, that option will result you in hitting the _same_ table twice. WHich could be a performance issue. The above method hits it once, however you have an overhead per row of complex function processing.

--
Billy

Received on Thu Aug 14 2003 - 08:09:46 CDT