Converting an Oracle to MySQL Query

Question

Can anyone help or advise? I am new to Oracle I have this query which needs to be converted to a MySQL Query

with dset as (
select count(column_1) as Volumes, to_char(creation_date,'MM-YYYY') as 
Month, REPLACE(record_type, 3, 2) as RecordType from
table_1 where
transaction_type = 'PG' and
category = 'CCB' and
trunc(creation_date) >= to_date(?1, 'DD/MM/YYYY') and
trunc(creation_date) <= to_date(?2, 'DD/MM/YYYY')
group by to_char(creation_date,'MM-YYYY'), REPLACE(record_type, 3, 2)
)
   SELECT    x.DateMonth
      ,MAX(DECODE(x.RecordType, '1', x.Volumes)) CONSUMER
      ,MAX(DECODE(x.RecordType, '2', x.Volumes)) COMMERCIAL
   FROM    (
          SELECT    r.DateMonth
                  ,r.RecordType
                  ,r.Volumes
          FROM    dset r
      ) x
 GROUP BY x.DatetMonth

What would be the best approach to get this query working in MySQL.

Many Thanks

SSMS is not the data base (and is normally used with sql server not oracle or mysql) so the question remains which version of mysql? — P.Salmon
– P.Salmon, Commented May 22, 2018 at 10:41
I bet it also contains the words Microsoft SQL Server and if it does this question is tagged incorrectly (it should be tagged sqlserver). It's important to get this basic info right - mysql and sqlserver are not the same and if there is uncertainty about the tags at best you will get a delayed response at worst a solution which works but not in the db you wanted. — P.Salmon
– P.Salmon, Commented May 22, 2018 at 11:34

Sean · Accepted Answer · 2018-05-22 12:18:06Z

0

SQL generally follows certain standards across the different database systems. So your query should mostly work on all SQL platforms. However some of the built in functions are different.

The best approach is to just try and run the query and see what errors it generates and work through them.

You will have to look at the documentation for the convert MSSQL function, as you will be needing that.

If you get stuck you can ask a more specific question about what it is you are stuck on.

answered May 22, 2018 at 12:18

Sean

1,4742 gold badges19 silver badges56 bronze badges

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

3 Comments

Vinnie S Over a year ago

Since migrating an Oracle database to SQL Server a lot of the database table names have been changed which is where I am getting the errors. I wanted to check that the query in general would be ok to run in SQL. So thank you all for your advise on this. I will attempt at running the query and work with the errors generated.

Vinnie S Over a year ago

Hi In my query in SSMS I am trying to figure out bits of the query above. So far in my recordType column i get returned 1 and 2 I want to run this query in SSMS ,MAX(DECODE(x.RecordType, '1', x.Volumes)) CONSUMER ,MAX(DECODE(x.RecordType, '2', x.Volumes)) COMMERCIAL the end result should give me 1 = consumer and 2 = commercial any idea what I'm doing wrong? full query above works in Oracle but not in SSMS.

Sean Over a year ago

There is no DECODE function in MSSQL. Lookup the case expression or the ternary function iif

Collectives™ on Stack Overflow

Converting an Oracle to MySQL Query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related