Mixing
How can i use SQL where clause with multiple parameters
2
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$;
sql postgresql
edited Jan 17 at 20:15
Alex K
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
|
show 1 more comment
2
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$;
sql postgresql
edited Jan 17 at 20:15
Alex K
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 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
Jan 2 at 7:54
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 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
Jan 2 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
Jan 2 at 8:10
|
show 1 more comment
2
2
2
1
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$;
sql postgresql
edited Jan 17 at 20:15
Alex K
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
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$;
sql postgresql
sql postgresql
edited Jan 17 at 20:15
Alex K
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
edited Jan 17 at 20:15
Alex K
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
edited Jan 17 at 20:15
Alex K
18.6k1480165
edited Jan 17 at 20:15
Alex K
18.6k1480165
edited Jan 17 at 20:15
Alex K
18.6k1480165
18.6k1480165
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
asked Jan 1 at 14:11
Javad AbediJavad Abedi
321112
321112
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 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
Jan 2 at 7:54
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 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
Jan 2 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
Jan 2 at 8:10
|
show 1 more comment
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 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
Jan 2 at 7:54
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 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
Jan 2 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
Jan 2 at 8:10
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 at 14:47
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 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
Jan 2 at 7:54
@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
Jan 2 at 7:54
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 at 7:58
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 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
Jan 2 at 8:04
@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
Jan 2 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
Jan 2 at 8:10
It looks to me like you have different requirements for your columns and need to individually adjust the condition for each column.
– Joakim Danielson
Jan 2 at 8:10
|
show 1 more comment
4 Answers
4
active
oldest
votes
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)
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
add a comment |
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 at 14:38
GMBGMB
16.8k3927
add a comment |
-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 at 14:31
Ankit MongiaAnkit Mongia
1057
I like this solution but why do you have 3 parameters toCOALESCEwhen the first and the last are the same?
– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
add a comment |
-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 at 14:35
SimonareSimonare
15k11840
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53996150%2fhow-can-i-use-sql-where-clause-with-multiple-parameters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
add a comment |
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)
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
add a comment |
3
3
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)
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
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)
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
edited Jan 1 at 14:47
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
answered Jan 1 at 14:39
Virgil IonescuVirgil Ionescu
31727
31727
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
add a comment |
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
1
1
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
thank you pal. my problem fixed. Please score to my question if is useful.
– Javad Abedi
Jan 2 at 8:34
add a comment |
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 at 14:38
GMBGMB
16.8k3927
add a comment |
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 at 14:38
GMBGMB
16.8k3927
add a comment |
0
0
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 at 14:38
GMBGMB
16.8k3927
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 at 14:38
GMBGMB
16.8k3927
answered Jan 1 at 14:38
GMBGMB
16.8k3927
answered Jan 1 at 14:38
GMBGMB
16.8k3927
answered Jan 1 at 14:38
GMBGMB
16.8k3927
16.8k3927
add a comment |
add a comment |
-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 at 14:31
Ankit MongiaAnkit Mongia
1057
I like this solution but why do you have 3 parameters toCOALESCEwhen the first and the last are the same?
– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
add a comment |
-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 at 14:31
Ankit MongiaAnkit Mongia
1057
I like this solution but why do you have 3 parameters toCOALESCEwhen the first and the last are the same?
– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
add a comment |
-1
-1
-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 at 14:31
Ankit MongiaAnkit Mongia
1057
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 at 14:31
Ankit MongiaAnkit Mongia
1057
answered Jan 1 at 14:31
Ankit MongiaAnkit Mongia
1057
answered Jan 1 at 14:31
Ankit MongiaAnkit Mongia
1057
answered Jan 1 at 14:31
Ankit MongiaAnkit Mongia
1057
1057
I like this solution but why do you have 3 parameters toCOALESCEwhen the first and the last are the same?
– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
add a comment |
I like this solution but why do you have 3 parameters toCOALESCEwhen the first and the last are the same?
– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
I like this solution but why do you have 3 parameters to
COALESCE when the first and the last are the same?– Joakim Danielson
Jan 1 at 14:38
I like this solution but why do you have 3 parameters to
COALESCE when the first and the last are the same?– Joakim Danielson
Jan 1 at 14:38
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Ankit Mongia
Jan 1 at 14:40
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.
– Joakim Danielson
Jan 1 at 14:43
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.
– Joakim Danielson
Jan 1 at 14:43
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
Hi @JoakimDanielson, yes you are correct we can omit the last parameter.
– Ankit Mongia
Jan 1 at 14:45
add a comment |
-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 at 14:35
SimonareSimonare
15k11840
add a comment |
-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 at 14:35
SimonareSimonare
15k11840
add a comment |
-1
-1
-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 at 14:35
SimonareSimonare
15k11840
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 at 14:35
SimonareSimonare
15k11840
answered Jan 1 at 14:35
SimonareSimonare
15k11840
answered Jan 1 at 14:35
SimonareSimonare
15k11840
answered Jan 1 at 14:35
SimonareSimonare
15k11840
15k11840
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53996150%2fhow-can-i-use-sql-where-clause-with-multiple-parameters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Does any of the nine columns in the table accept NULL or are they all mandatory? This will affect your query.
– Joakim Danielson
Jan 1 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
Jan 2 at 7:54
If some columns can be null then the query needs to be adjusted for that
– Joakim Danielson
Jan 2 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
Jan 2 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
Jan 2 at 8:10