Re: From Mysql to Oracle

sguazt_at_tiscali.it (sguazt) wrote in message news:<509dfc26.0207081154.61ed6a6a_at_posting.google.com>...
> Hi,
> I am a very beginner on ORACLE so excuse me if some question is very
> trivial.
> I have a lot of code written for MySQL; now I have to pass to ORACLE
> 8i, but, as
> you can imagine ;), I have some problem; for example I have query
> like:
> SELECT COUNT(*),COUNT(IF(field1 = val1 OR field2 = val2, foo,
> NULL)),COUNT(IF(field3 = val3, bar, NULL)) FROM ...
> where with COUNT(IF(...)) I intend to count only that fields that
> satisfies
> certain conditions; furthermore this type of query is very efficient
> since it
> collects different counting (on possible different condition) with
> ONLY ONE
> query, saving network traffic.
> So IF(cond,true_val,false_val) equals to the well now C operator "cond
> ? true_val : false_val"; well in Oracle I didn't find a similar
> function.
> Other problems with a MySQL LIMIT like operator;
> SELECT * FROM baz ... ORDER BY some_field LIMIT offset,size
> let us to consider only records starting from a position (offset),
> limiting the query result to size records. So this is VERY useful to
> show a long list of
> item divided for pages:
> for page 1, I will use LIMIT 0,size
> for page 2, I will use LIMIT size,size
> ...
> for page N, I will use LIMIT (N-1)*size, size
> Other problems may arise with the use of other MySQL function (like
> REGEXP used
> to perform a fulltext search using regular expression, ....).
> So anyone has an idea to solve this problem??
> Furthermore if someone knows a good site on this topic ... please send
> me it!
> Thank you in advance!!!!
> Marco Guazzone

Use case when .. then .. else .. end
instead of if.

use rownum() over(...) instead of limit

Look at this example and try to understand it

set feedback off

create table case_tbl (

  field1 varchar2(20),
  field2 varchar2(20),
  field3 varchar2(20)

insert into case_tbl values ('val1'  ,'zoo',    'clock' );
insert into case_tbl values ('mouse' ,'elefant','cat'   );
insert into case_tbl values ('mySql' ,'val2'   ,'val3'  );
insert into case_tbl values ('helmet','copy'   ,'val3'  );
insert into case_tbl values ('val1'  ,'val2'   ,'nose'  );


select count(*) f_all, 
       count( case when field1 = 'val1' or field2 = 'val2' 
                   then 1
                   else null
              end ) f1_2,
       count( case when field3 = 'val3'                    
                   then 1 
                   else null
              end ) f3 

select field1, field2, field3 from
  (select field1, field2, field3, row_number() over (order by field1) r    from case_tbl)
 where r between 2 and 4;

drop table case_tbl;

Hope this helped

Rene Received on Tue Jul 09 2002 - 09:42:35 CEST