Dataset SQL Query

Question & Answer
,
1

i have two dataset parameters but when p1 is given data should give me result set from all related data from p1 (Where condition 1), when p2 is is given data should give me result data from p2( where condition 2). But when p1 and p2 both have entered data , result set should have combination of both(where condition 3).. my SQL Query working perfectly in MY SQL but in dataset SQL its not working as expected. Need help on the same.By query is as follows:

select col1,col2,col3,col4 from table1 where
(col1=<<$p1>> and <<$p2>>=“”) or
(<<$p1>>=“” and col2=<<$p2>>) or
(col1=<<$p1>> and col2=<<$p2>>)

2

Hi @suvarna0212 ,

What is the default value set for your parameters?

3

hi.. i am keeping as “” as they are text fields.. but when i use analyse i put default value as blank…

however i tried with default value is 0 as i have YEAR.. but then no data is displayed with year 0

4

Can you modify your query to check for NULL instead of “”? Since you will be mapping the parameter from the analysis, it would be reset to blank I am assuming.

5

i tried with below query, always showing data only when two values are populated. not giving values when one parameter is populated from text field:

select col1,col2,col3,col4 from table1 where
(col1=<<$p1>> and <<$p2>> is NULL) or
(<<$p1>> is NULL and col2=<<$p2>>) or
(col1=<<$p1>> and col2=<<$p2>>)

6

Just thinking, can we use the below approach

(col1=<<$p1>> or <<$p2>>=“”) or
(<<$p1>>=“” or col2=<<$p2>>) or
(col1=<<$p1>> and col2=<<$p2>>)

7

hi. i have tried this approach in teh first place.. but not giving the desired output..

below are my expected outputs…
p1 is 4 digit Id

p2 is Month 01,02,.. 12

when i enter p1 ID 0001 it should give me all data having ID 0001 for all months…

when i enter p2 as 04 that is april data should give me all IDs of 04 April month data

when i give p1 0001 and p2 04.. it should only give me data of ID 0001 amonth 04… the third use case not getting hit.. right now showing all data( all Ids) of 04 and plus ID 0001 for all months.. which is incorrect..

8

Hi @suvarna0212

Assuming your default values for P1 = 0000 and P2 = 00 you can try the following

WHERE ( p1 = <<$P1>> or IFNULL(<<$P1>>, ‘0000’) = ‘0000’ ) AND
( p2 = <<$P2>> or IFNULL(<<$P2>>, ‘00’) = ‘00’ )

It should work for your three cases.

Regards,
Giri

9

it is only giving data when both of fields are populated, not when one field is populated

10

In this >>> IFNULL(<<$P1>>, ‘0000’) = ‘0000’ ) >> the 0000 should be replaced with your default value
In this >>> IFNULL(<<$P2>>, ‘00’) = ‘00’ >> the 00 should be replaced with your default value

Have you done that?

11

Hi @suvarna0212,
It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you