Converting Oracle to MySQL : Different Outputs Seen

Question

I have an SQL query from Oracle which I am trying to convert to MySQL.

Below is part of the query from both. The database structure is different hence you will find the names of tables and columns to be different.

I do not get any results in MYSQL part when I run it, need help in finding out why?

Oracle SQL Query :

    (SELECT  all_ips.child_itm_num,
             pres.child_itm_num AS present,
             prev.child_itm_num AS potential_prev_version
     FROM
            (SELECT DISTINCT child_itm_num
             FROM    EDW.dpdm_bom bom
             WHERE   root_itm_num IN ('a','b')) all_ips,
            (SELECT DISTINCT child_itm_num
             FROM    EDW.dpdm_bom bom
             WHERE   root_itm_num = 'b') pres,
   (SELECT DISTINCT child_itm_num
    FROM    EDW.dpdm_bom bom
    WHERE   root_itm_num = 'a') prev 
     WHERE  SUBSTR(all_ips.child_itm_num,0,LENGTH(all_ips.child_itm_num) - 3) = SUBSTR(pres.child_itm_num, 0,LENGTH(pres.child_itm_num) - 3)
     AND    SUBSTR(prev.child_itm_num,0,LENGTH(prev.child_itm_num) - 3)       = SUBSTR(pres.child_itm_num, 0,LENGTH(pres.child_itm_num) - 3)
     AND    prev.child_itm_num NOT IN (SELECT child_itm_num 
                                       FROM    EDW.dpdm_bom bom
                                       WHERE   root_itm_num ='b'))

MySQL Query :

    (SELECT  all_ips.child_name,
             pres.child_name AS present,
             prev.child_name AS potential_prev_version
     FROM
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   child_name IN ('a','b')) all_ips,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'b') pres,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'a') prev 
  WHERE  SUBSTR(all_ips.child_name,0,LENGTH(all_ips.child_name) - 3) = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    SUBSTR(prev.child_name,0,LENGTH(prev.child_name) - 3)       = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    prev.child_name NOT IN (SELECT child_name 
                                       FROM    DPDM_BOM bom
                                       WHERE   revision_name = 'b'))

Please Help !

Anusha H.

Harshil Doshi · Accepted Answer · 2017-11-29 20:30:11Z

1

I think you should replace WHERE child_name IN ('a','b') with WHERE revision_name IN ('a','b'). Also, use CHAR_LENGTH instead of LENGTH (although Mysql supports LENTGH too).

(SELECT  all_ips.child_name,
             pres.child_name AS present,
             prev.child_name AS potential_prev_version
     FROM
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name IN ('a','b')) all_ips,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'b') pres,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'a') prev 
  WHERE  SUBSTR(all_ips.child_name,0,CHAR_LENGTH(all_ips.child_name) - 3) = SUBSTR(pres.child_name, 0,CHAR_LENGTH(pres.child_name) - 3)
  AND    SUBSTR(prev.child_name,0,LENGTH(prev.child_name) - 3)       = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    prev.child_name NOT IN (SELECT child_name 
                                       FROM    DPDM_BOM bom
                                       WHERE   revision_name = 'b'))

Hope it helps!

answered Nov 29, 2017 at 20:30

Harshil Doshi

3,6123 gold badges18 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mandara31 Over a year ago

In my case I need to search for Child_name itself. Thanks.

Harshil Doshi Over a year ago

But in your oracle query, you are searching for root_itm_num IN ('a','b'). It's equivalent in mysql schema is revision_name IN ('a','b'). Hence, I suggested.

Mandara31 Over a year ago

Ah yes you are right ! I had to change it in a couple of other places too. Thanks :)

Collectives™ on Stack Overflow

Converting Oracle to MySQL : Different Outputs Seen

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related