CODEWITHSUNDEEP
oracle-databaseplsql
CompanyDroneFromSector7G
CompanyDroneFromSector7G

Reputation: 4517

SQL Server T-SQL to Oracle PL/SQL conversion

I'm having trouble writing a query in PL/SQL. I know exactly how I would do it in SQL Server T-SQL, I'm just not as good with Oracle.

It has to be a string of SQL, not a procedure.

The following is the kind of query I need. It isn't the actual query but seeing a translation for this should give me what I need.

You can see that the first query is a subset of the second, and if it makes up more than 5% of the base data then results are returned.

With this in mind, if there is a better way to write it, I'd be interested in that as well!

DECLARE @x int
SELECT @x = COUNT(*) * 20
FROM table1
WHERE table1.a=1
AND table1.b=2
AND table1.c=3

DECLARE @y int
SELECT @y = COUNT(*)
FROM table1
WHERE table1.a=1
AND table1.b=2

If @x > @y
    SELECT *
    FROM table1
    WHERE table1.a=1
    AND table1.b=2
    AND table1.c=3

Thanks for all help!

Upvotes: 0

Views: 261

Answers (2)

APC
APC

Reputation: 146199

Use sub-queries (what you mauy know as common table expressions) to get the two values:

with x_data as (SELECT  COUNT(*) * 20 x
        FROM table1
        WHERE table1.a=1
        AND table1.b=2
        AND table1.c=3 )
, y_data as (SELECT COUNT(*) y
         FROM table1
         WHERE table1.a=1
         AND table1.b=2
    SELECT table1.*
    FROM table1
          , y_data
          , x_data
    WHERE x > y
    and table1.a=1
    AND table1.b=2
    AND table1.c=3

I assume in real life the values are passed as variables or parameters, rather than being hardcoded as in your example.

Upvotes: 1

heuristican
heuristican

Reputation: 294

This can help.

DECLARE 
  rate NUMBER(2,1);
  output SYS_REFCURSOR;
BEGIN

SELECT SUM(CASE WHEN c=3 THEN 1 ELSE 0 END ) / COUNT(*)
INTO rate
FROM table1
WHERE table1.a=1
      AND table1.b=2;

  IF (rate > 0.5) THEN
    OPEN output FOR
      SELECT *
      FROM table1
      WHERE table1.a=1
            AND table1.b=2
            AND table1.c=3
  END IF;
END;

Upvotes: 1

Related Questions