cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Trying to convert oracle sql to databricks sql but not getting the desired output

chiruinfo5262
New Contributor II

ORACLE SQL:

 

COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END ) SELECTED_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END ) COMPARISON_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )/5 YEARLY_BM,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_FAILED_ASSETS_SELECTED_PERIOD,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 AND COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD
, 'NONLINEAR' as classType,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_3_months_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_6_months_BM
FROM
(SELECT
ASSETLIST.ASSETNUM,
MONTHS.M ORDER_ID,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)+1) SELECTED_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M)) SELECTED_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-72)+1) COMPARISON_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)) COMPARISON_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-3)+1) SELECTED_PERIOD_START_DATE_3months,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-6)+1) SELECTED_PERIOD_START_DATE_6months

 

 

Databricks SQL

 

COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) AS SELECTED_PERIOD_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) AS COMPARISON_PERIOD_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) / 5 AS YEARLY_BM,
    CASE 
        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 
        THEN 1 ELSE 0 
    END AS NO_OF_FAILED_ASSETS_SELECTED_PERIOD,
    CASE 
        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 
        AND COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) > 0 
        THEN 1 ELSE 0 
    END AS NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD,
    'NONLINEAR' AS classType,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_3_months_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_6_months_BM
 
 
 
 
 
    FROM
      (
        select ASSETLIST.asset,
    MONTHS.M AS ORDER_ID,
    
DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M - 12),1) AS SELECTED_PERIOD_START_DATE,
  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M) AS SELECTED_PERIOD_END_DATE,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 72),1) AS COMPARISON_PERIOD_START_DATE,
  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 12) AS COMPARISON_PERIOD_END_DATE,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 3),1)  AS SELECTED_PERIOD_START_DATE_3months,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')),-MONTHS.M - 6),1)  AS SELECTED_PERIOD_START_DATE_6months

 

 

 

 

 

 

3 REPLIES 3

nikhilj0421
Databricks Employee
Databricks Employee

Hi @chiruinfo5262 , what's the difference you are observing in the output? 

Could you please elaborate more on it?

Hi @nikhilj0421 

There is a difference of 18000 records in the output between oracle code output and databricks code output, not sure why

nikhilj0421
Databricks Employee
Databricks Employee

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now