CODEWITHSUNDEEP
sqldatabasepostgresql
Sadun89
Sadun89

Reputation: 144

How to simplify SQL Code with IF condition. (Postgresql)

I want to simplify the below sql code without change the business logic.here I mentioned postgresql code that I want to modify. 

SELECT IF(
    (SELECT `rate` FROM `vat_rate` WHERE '2017-05-12' BETWEEN from_date AND to_date) is not null ,
    (SELECT `rate` FROM `vat_rate` WHERE '2017-05-12' BETWEEN from_date AND to_date) ,

         IF((SELECT `rate` FROM `vat_rate` WHERE from_date is null and '2017-05-12' < to_date ) is not  null,
            (SELECT `rate` FROM `vat_rate` WHERE from_date is null and '2017-05-12' < to_date ),

                IF(
                    (SELECT `rate` FROM `vat_rate` WHERE to_date is null and '2017-05-12' > from_date) is not   null,
                          (SELECT `rate` FROM `vat_rate` WHERE to_date is null and '2017-05-12' > from_date),
                          'not found'
                         )
            )
    ) as rate

Upvotes: 2

Views: 782

Answers (3)

joop
joop

Reputation: 4503

The complexity is caused by allowing NULLs in the dates. Using -infinity and (+)infinity as the default values would have avoided the tests for NULL dates. Changing the table definition:

ALTER TABLE vat_rate
        ALTER COLUMN from_date SET DEFAULT '-infinity'
        , ALTER COLUMN to_date SET DEFAULT 'infinity'
        ;

UPDATE vat_rate SET from_date = DEFAULT WHERE from_date IS NULL;
UPDATE vat_rate SET to_date = DEFAULT WHERE to_date IS NULL;

ALTER TABLE vat_rate
       ALTER COLUMN from_date SET NOT NULL
       , ALTER COLUMN to_date SET NOT NULL
        ;

could reduce your query to just:

SELECT rate AS the_reet
FROM vat_rate
WHERE '2017-05-12' BETWEEN from_date AND to_date
                ;

If you cannot change the table definition, you could fold the date values inside the query:

SELECT rate AS the_reet
FROM vat_rate 
WHERE '2017-05-12' BETWEEN COALESCE(from_date, '-infinity'::date)
                AND COALESCE(to_date, 'infinity'::date)
                ;

Avoiding the COALESCE() (this could be faster)

SELECT rate AS the_reet
FROM vat_rate 
WHERE ('2017-05-12' >=  from_date OR from_date IS NULL)
AND ( '2017-05-12' <= to_date OR to_date IS NULL)
                ;

The extreme case of no matching range could be solved by adding a union.

Upvotes: 3

user5226582
user5226582

Reputation: 1986

You're after the Coalesce() operator:

SELECT 
  COALESCE (
    (SELECT rate FROM vat_rate WHERE '2017-05-12' BETWEEN from_date AND to_date),
    (SELECT rate FROM vat_rate WHERE from_date is null and '2017-05-12' < to_date ),
    (SELECT rate FROM vat_rate WHERE to_date is null and '2017-05-12' > from_date),
    'not found'
  ) as rate

Upvotes: 2

sagi
sagi

Reputation: 40481

Why not just use CASE EXPRESSION directly on the vat_rate table instead of selecting each value from it over and over? Here is a solution that should do the same using conditional aggregation :

SELECT COALESCE(t.val_1,t.val_2,t.val_3) as calc_col
FROM (
    SELECT MAX(CASE WHEN vat_rate between  from_date AND to_date THEN rate END) as val_1,
           MAX(CASE WHEN from_date is null and '2017-05-12' < to_date THEN rate END) as val_2,
           MAX(CASE WHEN to_date is null and '2017-05-12' > from_date THEN rate END) as val_3
    FROM vat_rate) t

Upvotes: 2

Related Questions