How to convert this kind of query to Oracle SQL

I have one very comlicate query which is done in MySQL and right now I want to re-write it in Oracle SQL and I don’t have enought experience in Oracle SQL I stuck here and I don’t know how to write this kind of query in Oracle SQL

if($month != 0 && $year != 0){              $sql="  SELECT                         SUM(                             TIME_TO_SEC( mi.`Time`)                         ) AS seconds,                         uti.`Date`                         FROM                              users AS ui                         LEFT JOIN project_users AS pui                             ON(ui.`UserID` = pui.`UserID`)                         LEFT JOIN user_timesheets AS uti                             ON(ui.`UserID` = uti.`user_id`)                         LEFT JOIN moments AS mi                             ON(uti.`UserTimesheetsID` = mi.`UserTimesheetsID`)                         WHERE                              uti.`user_id` = $userId AND mi.`Time` != ''                         AND                              MONTH(uti.Date) = $month                         AND                              YEAR(uti.Date) = $year                         AND                              mi.AtestStatus = 1                         GROUP BY                             uti.Date                         HAVING                               SUM(                                 TIME_TO_SEC( mi.`Time`)                             ) > 28800;";          } else{              $sql="  SELECT                         SUM(                             TIME_TO_SEC( mi.`Time`)                         ) AS seconds,                         uti.`Date`                         FROM                              users AS ui                         LEFT JOIN project_users AS pui                             ON(ui.`UserID` = pui.`UserID`)                         LEFT JOIN user_timesheets AS uti                             ON(ui.`UserID` = uti.`user_id`)                         LEFT JOIN moments AS mi                             ON(uti.`UserTimesheetsID` = mi.`UserTimesheetsID`)                         WHERE                              uti.`user_id` = $userId AND mi.`Time` != ''                         AND                              MONTH(uti.Date) = MONTH(CURRENT_DATE())                         AND                              YEAR(uti.Date) = YEAR(CURRENT_DATE())                         AND                              mi.AtestStatus = 1                          GROUP BY                             uti.Date                         HAVING                               SUM(                                 TIME_TO_SEC( mi.`Time`)                             ) > 28800;";          } 

So far as I know in Oracle SQL there is no IF ELSE and I have no idea how to translate TIME_TO_SEC and MONTH and YEAR field in Oracle SQL. So far I start with this line

                      SELECT                         SUM(TIME_TO_SEC( mi.Time)) AS seconds,                         uti.Date_                         FROM                              users  ui                         LEFT JOIN project_users  pui                             ON(ui.UserID = pui.UserID)                         LEFT JOIN user_timesheets  uti                             ON(ui.UserID = uti.user_id)                         LEFT JOIN moments mi                             ON(uti.UserTimesheetsID = mi.UserTimesheetsID)                         WHERE                              uti.user_id = 1 AND mi.Time != ''                         AND                              MONTH(uti.Date_) =                          AND                              YEAR(uti.Date_) = $year                         AND                              mi.AtestStatus = 1                         GROUP BY                             uti.Date_                         HAVING                               SUM(TIME_TO_SEC( mi.Time)) > 28800; 

Please be patient and carefull since I am begginer in Oracle SQL and some of this staff I never heard about before

Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.