How to do if in sql - Postgres

Question

How to do if in sql - Postgres

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I need to do an if in postgres example.

SELECT
log."data" AS log_data,
log."tipomovimento" AS log_tipomovimento,
     log."nomefornecedor" AS log_nomefornecedor,
     log."nomeproduto" AS log_nomeproduto
FROM
"public"."log" log
WHERE
     if(log."nomefornecedor" != null) {
 log."data" BETWEEN '2018-01-01' and '2018-06-30'
     and log."tipomovimento" IN ('S') and
    "nomefornecedor" = 'x';
}else{
log."data" BETWEEN '2018-01-01' and '2018-06-30'
     and log."tipomovimento" IN ('S') 
}

If the provider is not null it will filter vendor, if it is null it will not filter vendor in the where. Anyone have any ideas?

java postgresql ireport

asked by anonymous 27.06.2018 / 16:27

2 answers

2

No need for IF, use OR:

where log."data" BETWEEN '2018-01-01' and '2018-06-30'
and log."tipomovimento" IN ('S') 
and (log."nomefornecedor" = null OR "nomefornecedor" = 'x')

27.06.2018 / 16:43

How to write CSV file data without deleting existing data MySQL table with performance problem (Very slow)

score 1 · Accepted Answer

As it is in the question, you do not have to use if or case. The answer from @ Lisângelo Berti solves your problem.

You do not need to check if the value of a column is null, and then apply a filter. The filter is to filter just the value of that column.

The correct usage would look like this: The user informs the parameter to filter as null. If it is null, it returns all the names if the filter does not apply to that column.

Example:

WHERE log."data" BETWEEN '2018-01-01' and '2018-06-30' 
and log."tipomovimento" IN ('S')
and 
(case when [parametro] is null then 
    1=1
else
    log."nomefornecedor" = [parametro];
end)