How can i use SQL where clause with multiple parameters

Question

I have a TABLE with 9 fields and I created a report in Ireport with 9 parameters from this TABLE but some times users fill some of parameters , not all of them. so , as far as you know I should check all of them with each other . IF is null do this and IF is not null do that. so , please help and tell to me how can I handle it?

+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Col_1 | Col_2 | Col_3 | Col_4 | Col_5 | Col_6 | Col_7 | Col_8 | Col_9 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| x     | y     | 3o    | xT    | y1    | k32   | xY    | L     | 3q    |
| s     | u     | 4t    | xO    | y3    | j34   | xO    | K     | 2a    |
| h     | z     | 33r   | xH    | y3    | h36   | xT    | y     | 6s    |
| t     | t     | 14r   | xF    | y5    | t31   | xP    | J     | 1d    |
| m     | m     | 77w   | xS    | y7    | o30   | xJ    | O     | 0f    |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+

and my query is this

CREATE FUNCTION public.rep_staff(
    parCol1 text,
    parCol2 text,
    parCol3 text,
    parCol4 text,
    parCol5 text,
    parCol6 text,
    parCol7 text,
    parCol8 text,
    parCol9 text,
    OUT Col_1 character varying,
    OUT Col_2 character varying,
    OUT Col_3 character varying,
    OUT Col_4 character varying,
    OUT Col_5 character varying,
    OUT Col_6 character varying,
    OUT Col_7 character varying,
    OUT Col_8 character varying,
    OUT Col_9 character varying)
RETURNS SETOF record 
    LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
    parCol1_par text;
    parCol2_par text;
    parCol3_par text;
    parCol4_par text;
    parCol5_par text;
    parCol6_par text;
    parCol7_par text;
    parCol8_par text;
    parCol9_par text;
BEGIN
    parCol1_par = parCol1 ;
    parCol2_par = parCol2 ;
    parCol3_par = parCol3 ;
    parCol4_par = parCol4 ;
    parCol5_par = parCol5 ;
    parCol6_par = parCol6 ;
    parCol7_par = parCol7 ;
    parCol8_par = parCol8 ;
    parCol9_par = parCol9 ;

    IF ( parCol1_par is NOT  null AND 
             parCol2_par is NOT  null AND 
             parCol3_par is NOT  null AND 
             parCol4_par is NOT  null AND 
             parCol5_par is NOT  null AND 
             parCol6_par is NOT  null AND 
             parCol7_par is NOT  null AND 
             parCol8_par is NOT  null AND 
             parCol9_par is NOT null)
        THEN
            RETURN QUERY
            SELECT
                *
            FROM
                "public"."TABLE" T
            WHERE T.parCol1 = parCol1_par AND
                  T.parCol2 = parCol2_par AND
                  T.parCol3 = parCol3_par AND
                  T.parCol4 = parCol4_par AND
                  T.parCol5 = parCol5_par AND
                  T.parCol6 = parCol6_par AND
                  T.parCol7 = parCol7_par AND
                  T.parCol8 = parCol8_par AND
                  T.parCol9 = parCol9_par;

        ELSE IF ( parCol1_par is null AND 
             parCol2_par is NOT  null AND 
             parCol3_par is NOT  null AND 
             parCol4_par is NOT  null AND 
             parCol5_par is NOT  null AND 
             parCol6_par is NOT  null AND 
             parCol7_par is NOT  null AND 
             parCol8_par is NOT  null AND 
             parCol9_par is NOT null)
        THEN
            RETURN QUERY
            SELECT
                *
            FROM
                "public"."TABLE" T
            WHERE T.parCol2 = parCol2_par AND
                  T.parCol3 = parCol3_par AND
                  T.parCol4 = parCol4_par AND
                  T.parCol5 = parCol5_par AND
                  T.parCol6 = parCol6_par AND
                  T.parCol7 = parCol7_par AND
                  T.parCol8 = parCol8_par AND
                  T.parCol9 = parCol9_par;

        ELSE IF (
                  .
                  .
                  .
                  .
                  .
                  .
                  .
        ELSE IF (
        ELSE IF (
        ELSE IF (

        END IF;
END

$BODY$;

Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query. — Joakim Danielson
– Joakim Danielson, Commented Jan 1, 2019 at 14:47
@Joakim Danielson in my TABLE the answer is not all columns but in my parameter the answer is yes actually I want create a form ... users can fill parameter or not , if they didn't fill parameter so select all of that column — Javad Abedi
– Javad Abedi, Commented Jan 2, 2019 at 7:54
If some columns can be null then the query needs to be adjusted for that — Joakim Danielson
– Joakim Danielson, Commented Jan 2, 2019 at 7:58
@Joakim Danielson imagine we have a parameter about Gender(Men/Women) so in our Column in Table we have Men or Women so if Users select Men , query should filter on men and if Users select Women , query should filter on Women but if users don't select any one query must show all of them(Men and Women) so I should create a function with this aspects but I have 9 column with 9 parameters so how match 'IF' I need for that !! :( — Javad Abedi
– Javad Abedi, Commented Jan 2, 2019 at 8:04
It looks to me like you have different requirements for your columns and need to individually adjust the condition for each column. — Joakim Danielson
– Joakim Danielson, Commented Jan 2, 2019 at 8:10

Virgil Ionescu · Accepted Answer · 2019-01-01 14:47:33Z

3

You can use database specific NVL functions (NVL in ORACLE, IFNULL in MYSQL, COALESCE in POSTGRESQL, etc) combined wit column in tha table.

Example:

Let's supose parCol1_par is NULL.

Condition will be (in POSTGRESQL):

COALESCE(parCol1_par,T.parCol1)=T.parCol1

So, the entire interogation could be

SELECT *
FROM
"public"."TABLE" T
WHERE T.parCol1 = COALESCE(parCol1_par, T.parCol1)
AND T.parCol2 = COALESCE(parCol2_par, T.parCol2) 
AND T.parCol3 = COALESCE(parCol3_par, T.parCol3) 
AND T.parCol4 = COALESCE(parCol4_par, T.parCol4) 
AND T.parCol5 = COALESCE(parCol5_par, T.parCol5) 
AND T.parCol6 = COALESCE(parCol6_par, T.parCol6) 
AND T.parCol7 = COALESCE(parCol7_par, T.parCol7) 
AND T.parCol8 = COALESCE(parCol8_par, T.parCol8) 
AND T.parCol9 = COALESCE(parCol9_par, T.parCol9)

edited Jan 1, 2019 at 14:47

answered Jan 1, 2019 at 14:39

Virgil Ionescu

3371 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Javad Abedi Over a year ago

thank you pal. my problem fixed. Please score to my question if is useful.

GMB · Accepted Answer · 2019-01-01 14:38:33Z

0

This could be written as a single query, like :

SELECT
 *
FROM
    "public"."TABLE" T
WHERE 
    (parCol1_par IS NULL OR T.parCol1 = parCol1_par)
    AND (parCol2_par IS NULL OR T.parCol2 = parCol2_par)
          ...

Another option is to use COALESCE, which produces a shorter syntax, although maybe less efficient :

SELECT
 *
FROM
    "public"."TABLE" T
WHERE 
    T.parCol1 = COALESCE(parCol1_par, T.parCol1) 
    AND T.parCol2 = COALESCE(parCol2_par, T.parCol2)
          ...

answered Jan 1, 2019 at 14:38

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Ankit Mongia · Accepted Answer · 2019-01-01 14:31:54Z

-1

i went through your requirement and i though that you can use COALESCE function instead of so many if else block. it is same as NVL in oracle. Below i am writing a sample query which you can use in your function.

COALESCE(parcol1_par,t.parcol1,parcol1_par) -- if parcol1_par is null it will compare with original value of parcol1 column, if it is not null then it will compare with parcol1_par value.

SELECT
    *
FROM
    "public"."TABLE" t
WHERE
    t.parcol1 = COALESCE(parcol1_par,t.parcol1,parcol1_par)
    AND t.parcol2 = COALESCE(parcol2_par,t.parcol2,parcol2_par)
    AND t.parcol3 = COALESCE(parcol3_par,t.parcol3,parcol3_par)
    AND t.parcol4 = COALESCE(parcol4_par,t.parcol4,parcol4_par)
    AND t.parcol5 = COALESCE(parcol5_par,t.parcol5,parcol5_par)
    AND t.parcol6 = COALESCE(parcol6_par,t.parcol6,parcol6_par)
    AND t.parcol7 = COALESCE(parcol7_par,t.parcol7,parcol7_par)
    AND t.parcol8 = COALESCE(parcol8_par,t.parcol8,parcol8_par)
    AND t.parcol9 = COALESCE(parcol9_par,t.parcol9,parcol9_par);

answered Jan 1, 2019 at 14:31

Ankit Mongia

2102 silver badges13 bronze badges

4 Comments

Joakim Danielson Over a year ago

I like this solution but why do you have 3 parameters to COALESCE when the first and the last are the same?

Ankit Mongia Over a year ago

Hi @JoakimDanielson , in the first parameter it is checking it that in null or not, if it is null then it taking parameter2 and if it is not null then it is taking parameter 3. if do not give parameter 3 then it will take by default null as value.

Joakim Danielson Over a year ago

But the first and the third are the same, so the last one is redundant. When the first is null then the last will also be null.

Ankit Mongia Over a year ago

Hi @JoakimDanielson, yes you are correct we can omit the last parameter.

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-01 14:35:50Z

-1

you can use COALESCE to give default value to your column if it is null,

COALESCE(parcol2, '')

or even you can use it inside if

IF COALESCE(parCol1_par, parCol2_par , parCol3_par , parCol4_par , 
            parCol5_par , parCol6_par , parCol7_par , parCol8_par ,
            parCol9_par ) IS NOT NULL THEN
....
END;

The above IF returns null if all the columns are null and returns value if any/some/all of it is not null.

answered Jan 1, 2019 at 14:35

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

How can i use SQL where clause with multiple parameters

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related