Implementing a Dynamic WHERE Clause HELP !

[xerocool]

I’ve been having a problem with using COALESCE/ISNULL. Everything seems to work fine, expect when a field has a NULL value. If the record has a NULL value the record is not returned.

If the value of both parameter is 1 , it returns valid data , but if any one of them in 0 , its showing me Blank Page

 

Here is the part of code - (where clause)

WHERE
     Computer.Computer_Idn = BoundAdapter.Computer_Idn
     and Computer.Computer_Idn = Operating_System.Computer_Idn
     and Computer.Computer_Idn = Memory.Computer_Idn
     and Computer.Computer_Idn = Processor.Computer_Idn
     and Computer.Computer_Idn = Landesk.Computer_Idn
  ------>   and Operating_System.OSType = ISNULL($P{Os_List},Operating_System.OSType)
     and  Land.ClientConfigurationName = ISNULL($P{Location}, Land.ClientConfigurationName) <---------

Tried the same with COALESCE Function , but same error,also if i execute only one of the above command its working , but i need both

 

Thanks :)

Post Edited by xerocool at 03/15/2010 10:22

Post Edited by xerocool at 03/15/2010 10:24

[slow]

are you sure that the two clauses

 ------>   and Operating_System.OSType = ISNULL($P{Os_List},Operating_System.OSType)
     and  Land.ClientConfigurationName = ISNULL($P{Location}, Land.ClientConfigurationName) <---------

are in the "AND" configuration and not in OR?
if the clauses work fine separately and there's no result with both them seems to be clauses that exlude each other...

 

do you create the report programmatically or by the only IReport program?

 

[xerocool]

yes its using 'AND'

and i have used the Ireport wizard to create the following report , and some parts of SQL wherever needed , Im new to SQL and still in learning process

 

Can you suggest me a solution,for selective choosing for parameters ?

Thanks :)

[thangalin]

Have you tried using NVL?

 

http://www.techonthenet.com/oracle/functions/nvl.php

[xerocool]

yes,tried using nvl function , it wont accept it

Invalid syntax error  

[slow]

when you write " If the record has a NULL value the record is not returned." do you refer to the database record?

can you be more clear when you write about parameter (values 1/ 0 ?) and the record data (values something/null ?) ?

it seems to be only a malformed query issue...

 

[xerocool]

yes i am referring to the database records , parameter values 0/1 as in, if between the two parameters (Os_List,Location)

if one of them is kept blank,the output is blank , but if both are entered i get correct result  

i went through few articles on Dynamic Where clause,but none of the syntax worked properly

" SELECT Cus_Name,
Cus_City,
Cus_Country
FROM @Customers
WHERE (Cus_Name = @Cus_Name OR @Cus_Name IS NULL) AND
(Cus_City = @Cus_City OR @Cus_City IS NULL) AND
(Cus_Country = @Cus_Country OR @Cus_Country IS NULL) " 
 

Following is the entire code 

 

Thanks

Code:

SELECT     Computer."Computer_Idn" AS Computer_Computer_Idn,     Computer."LoginName" AS Computer_LoginName,     BoundAdapter."Computer_Idn" AS BoundAdapter_Computer_Idn,     Operating_System."Computer_Idn" AS Operating_System_Computer_Idn,     Operating_System."OSType" AS Operating_System_OSType,     Processor."Computer_Idn" AS Processor_Computer_Idn,     Processor."Type" AS Processor_Type,     Processor."MaxSpeed" AS Processor_MaxSpeed,     Memory."Computer_Idn" AS Memory_Computer_Idn,     Memory."BytesTotal" AS Memory_BytesTotal,     BoundAdapter."PrimaryDNS" AS BoundAdapter_PrimaryDNS,     BoundAdapter."IPAddress" AS BoundAdapter_IPAddress,     Land."Computer_Idn" AS Land_Computer_Idn,     Land."ClientConfigurationName" AS Land_ClientConfigurationNameFROM     "dbo"."Computer" Computer INNER JOIN "dbo"."Operating_System" Operating_System ON Computer."Computer_Idn" = Operating_System."Computer_Idn"     INNER JOIN "dbo"."Land" Land ON Computer."Computer_Idn" = Land."Computer_Idn",     "dbo"."BoundAdapter" BoundAdapter,     "dbo"."Processor" Processor,     "dbo"."Memory" MemoryWHERE     Computer.Computer_Idn = BoundAdapter.Computer_Idn     and Computer.Computer_Idn = Operating_System.Computer_Idn     and Computer.Computer_Idn = Memory.Computer_Idn     and Computer.Computer_Idn = Processor.Computer_Idn     and Computer.Computer_Idn = Land.Computer_Idn     and Operating_System.OSType = ISNULL ($P{Os_List},Operating_System.OSType)     and Land.ClientConfigurationName = ISNULL($P{Location},Land.ClientConfigurationName)ORDER BY     BoundAdapter."IPAddress" ASC

[slow]

are you sure that when you set a parameter 'blank' the value that arrives to query is NULL?

try to know with more details what happen in this case.

I think that arrives an empty string "" or the string "null", so the query return no result...
"" or "null" is different from NULL...

 

 

[xerocool]

oh ! , i have kept the default parameter value for each parameter as " "  

what should be the default parameter value ?

Post Edited by xerocool at 03/17/2010 06:59

[slow]

if you expect a NULL value in the query, try to remove the " " default value, set as default value "null" (without quotes!!) as String parameter, and set in the query the function:   .... = nvl($P{parameter},something)

i tryed it and it works fine.

if you create the report programmatically (wit java...) you can avoid to create the "where" parts of query where the NULL patameter appears.

Post Edited by slow at 03/17/2010 08:47

[xerocool]

hey thanks for the help, but i have been trying nvl function in ireport and im getting this error

ERROR : SQL Problems : 'nvl' is not a recognized built- in function

also as for the Default Parameter Value = null

ERROR : nvarchar value to data type bit error 

Only Oracle supports NVL function right ? Im using MYSQL  .. correct me if i am wrong

Thanks  :)

 

Post Edited by xerocool at 03/17/2010 09:26

[slow]

try ifnull(expr1, expr2)

in my test, using 'nvl' with Oracle and the null default parameter value, it works!

 

[xerocool]

agreed it works on oracle , but both nvl and IFNULL are giving me the same error :(

"not a built in function" .So only option i have right now is ISNULL which is not giving me correct output