I need help converting T-SQL query to Oracle suppo

问题:

I am executing this query in SQL Server and it is working fine but when I try to execute it in Oracle, it is not giving the same results.

You can see in my attached photo the data of one customer, which have got the code 1, 2,4, 8 and he should get 0.70 value for having code 1,2,4 and then for having code 8 he should get 0.75 so after multiplication it should return 0.52 as value. I tried it in Oracle by replacing is null by nvl but it returned 1 instead of 0.52. Please help me convert this query in an oracle supported query which will return the same results.

Here is my query

 SELECT [id] ,[name],r = isnull(nullif(
    max(CASE WHEN [code]  IN (1,2,4) then 0.70 else 0 end)
  ,0),1)
* isnull(nullif(
   min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
    * max(CASE WHEN [code]  IN (4) then 0.20 else 0 end)
  ,0),1)
* isnull(nullif(
    max(CASE WHEN [code] IN (8) then 0.75 else 0 end)
  ,0),1)
  FROM  (values (1, 'ali',4)
        ,(1, 'ali',1)
        ,(1, 'ali',8)
        ,(1, 'ali',2)
        ,(2, 'sunny',1)
     ,(4, 'arslan',4)) as t(id, name,code)
  GROUP BY id, name;

回答1:

Since now you are multiplying scores, first we need to decide, what is the score if non of codes is matched. I suppose, it should be 0. Next, we should break all possible codes into independent groups, that is which results do not depend on other groups members. Here they are (1,2,4) and (8). And define the rule for every group. So

SELECT [id] ,[name],r = 
  -- At least one of values needed to get score > 0
  MAX(CASE WHEN code IN (1,2,4, 8) THEN 1.0  ELSE 0.0 END) *
  -- Now rules for every independent set of codes. Rule should return score if matched or 1.0 if not matched
  -- (1,2,4)
  coalesce(MAX(CASE WHEN [code] IN (1,2,4) THEN 0.70 END), 1.0 ) *
  -- (8) 
  coalesce(MAX(CASE WHEN [code] IN (8) THEN 0.75 END), 1.0)
  -- more ?
  FROM  (values (1, 'ali',4)
        ,(1, 'ali',1)
        ,(1, 'ali',8)
        ,(1, 'ali',2)
        ,(2, 'sunny',1)
     ,(4, 'arslan',4)) as t(id, name,code)
  GROUP BY id, name;

回答2:

There are some SQL Server things in the query that are not standard SQL:

1. [] around column names - remove them; you don't need them here (otherwise you would use standard SQL quotes "")
2. r = expression - for an alias name. Change this to standard SQL expression AS r
3. ISNULL(expression, value) - Change this to standard SQL COALESCE(expression, value) or Oracle's NVL(expression, value)
4. NULLIF(expression, value) - this you can keep; Oracle supports it, too
5. values (), (), ... - replace with a SELECT FROM DUAL UNION ALL subquery

You get:

select 
  id,
  name, 
  coalesce(nullif( max(case when code in (1,2,4) then 0.70 else 0 end), 0), 1) *
  coalesce(nullif( min(case when code in (1,2) then 0 else 1 end) *
                   max(case when code in (4) then 0.20 else 0 end) , 0), 1) *
  coalesce(nullif( max(case when code in (8) then 0.75 else 0 end), 0), 1) as r
from 
(
  select 1 as id, 'ali' as name, 4 as code from dual
  union all
  select 1 as id, 'ali' as name, 8 as code from dual
  union all
  select 1 as id, 'ali' as name, 2 as code from dual
  union all
  select 2 as id, 'sunny' as name, 1 as code from dual
  union all
  select 4 as id, 'arslan' as name, 4 as code from dual
)
group by id, name;

The calculation, however, is unnecessarily complicated:

coalesce(nullif( max(case when code in (1,2,4) then 0.70 else 0 end), 0), 1)

means if there is at least one match then 0.70 else 0 which is turned to null which is turned to 1. So it is the same as

min(case when code in (1,2,4) then 0.70 else 1 end)

So if I am not mistaken, the whole calcultion becomes:

case when max(case when code in (1,2) then 1 end) = 1 
  then 0.7 else max(case when code = 4 then 0.14 else 1 end) end *
min(case when code = 8 then 0.75 else 1 end) as r

or

case when max(case when code in (1,2) then 1 end) = 1 then 0.7 
     when max(case when code = 4 then 1 end) = 1 then 0.14 
     else 1 
end *
min(case when code = 8 then 0.75 else 1 end) as r

Well, there are many ways to write this.

回答3:

The code below should give you the answer you expect;

CREATE TABLE #TestData (ID int, Name varchar(10), Code int)
INSERT INTO #TestData (ID, Name, Code)
VALUES
(1,'ali',4)
,(1,'ali',1)
,(1,'ali',8)
,(1,'ali',2)
,(2,'sunny',1)
,(4,'arslan',4)

SELECT DISTINCT
     a.id
    ,a.Name
    ,COALESCE(b.HasCode1, b.HasCode2, b.HasCode4,1) * COALESCE(b.HasCode8,1) Result

FROM (SELECT ID, Name FROM #TestData GROUP BY ID, Name) a
LEFT JOIN
(
SELECT
ID
,Name
,SUM(CASE WHEN CODE = 1 THEN 0.7 END) HasCode1
,SUM(CASE WHEN CODE = 2 THEN 0.7 END) HasCode2
,SUM(CASE WHEN CODE = 4 THEN 0.7 END) HasCode4
,SUM(CASE WHEN CODE = 8 THEN 0.75 END) HasCode8
FROM #TestData
GROUP BY
ID
,Name
) b
ON a.ID = b.ID
AND a.Name = b.Name

DROP TABLE #TestData

回答4:

If I understand what you're after (ie. for each of the cases, the id/name combination needs to have all the codes specified), then this will probably do what you're after. You may want to add some sort of trunc/floor/round function on the val column if you're after the answer to 2 decimal places, though:

with t as (select 1 id, 'ali' name, 4 code from dual union all
           select 1 id, 'ali' name, 1 code from dual union all
           select 1 id, 'ali' name, 8 code from dual union all
           select 1 id, 'ali' name, 2 code from dual union all
           select 2 id, 'ali' name, 4 code from dual union all
           select 2 id, 'ali' name, 8 code from dual union all
           select 3 id, 'bob' name, 1 code from dual union all
           select 3 id, 'bob' name, 2 code from dual union all
           select 3 id, 'bob' name, 8 code from dual),
   res as (select id,
                  name,
                  case when count(distinct case when code in (1, 2, 4) then code end) = 3 then 0.7 
                       when count(distinct case when code in (1, 2) then code end) = 2 then 0.5
                       else 1
                  end case_1_2_and_poss_4,
                  case when count(distinct case when code = 8 then code end) = 1 then 0.75 else 1 end case_8
           from   t
           group by id, name)
select id,
       name,
       case_1_2_and_poss_4 * case_8 val
from   res;

        ID NAME        VAL
---------- ---- ----------
         1 ali       0.525
         2 ali        0.75
         3 bob       0.375