database - Convert MySQL Script to ORACLE -


i'm trying convert above script mysql oracle , i'm total beginner using oracle database , pl/sql. read ifnull in mysql equivalent nvl in oracle, still cannot convert beyond script. please help.

set @r:=0;  create table country_mondial  select @r:=@r+1 rown, @dtf0:=dtf0 dtf0, @dtf1:=dtf1 dtf1,  ifnull((log(log(@dtf0)+1)+1),0)*9.531408863445597 +  ifnull((log(log(@dtf1)+1)+1),0)*9.531408863445696 score, id,id_city, id, dl  (select pkey, max(case when wordid=205 count else 0 end) dtf0,  max(case when wordid=223 count else 0 end) dtf1  country_i wordid in (205,223)  group pkey having dtf0>0 or dtf1>0) cnt, country t1  cnt.pkey = id order score desc;

thanks in advance.

here's came (this isn't tested yet)

create table country_mondial   select row_number() on (order t.score desc) rown      , c.dtf0      , c.dtf1      , coalesce((log(log(c.dtf0)+1)+1),0)*9.531408863445597         + coalesce((log(log(c.dtf1)+1)+1), 0)*9.531408863445696 score      , t.id      , t.id_city      , t.id id2      , t.dl   ( select i.pkey               , max(case when i.wordid=205 i.count else 0 end) dtf0               , max(case when i.wordid=223 i.count else 0 end) dtf1            country_i           i.wordid in (205,223)           group              i.pkey          having 0 < max(case when i.wordid=205 i.count else 0 end)              or 0 < max(case when i.wordid=223 i.count else 0 end)        ) c   join country t     on t.id = c.pkey  order     t.score desc;

some notes:

i don't believe can reference alias assigned expression in having clause. (this may have changed, , allowed in more recent versions of oracle, in having clause, had repeat expression.

an alias can referenced in order clause (except union/union queries);

we can reference column position in select list.

i'm not sure if can reference column alias in order clause, in row_number() over(). query above that, repeating expression workaround.

qualifying column references table alias practice.

we can't have 2 columns of same name (id) in table, @ least 1 of occurrences need assigned different name, better specify in our query (we avoid oracle throwing error, or generating assigned name.)

in query above, coalesce function performs same duties nvl() or case expression. (i believe coalesce more portable , sql-92 compatible option.)

i think count keyword, qualifying table alias (i.count) makes unambiguous.


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

php - MySQLi multi_query results for later use -

i using mysqli multi_query work several select statemets @ time. know how handle results, able use them later in code. example: <?php //connection stuff $query = "select name, surname database1;"; $query.= "select car, year, type database2 carid='1';"; $query.= "select product, price database3;"; if ($mysqli->multi_query($query)) { if($result = $mysqli->store_result()) { while($row = $result->fetch_row()) { --> here? } } } ?> <html> <div id='persona'> <?php foreach() { --> print name + surname } ?> </div> <div id='cars'> <?php foreach() { --> print car + year + type } ?> </div> <div id='product'> <?php foreach() { --> print product + price } ?> </div> </html> one more thing, prepared statements not possible when using multiple_query, right? there no benefit in putting unrelated queries in 1 multi ...
Read more