[Implementing a Dynamic WHERE Clause HELP !]

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

 

[Implementing a Dynamic WHERE Clause HELP !]

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

[Implementing a Dynamic WHERE Clause HELP !]

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

[Implementing a Dynamic WHERE Clause HELP !]

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

[Implementing a Dynamic WHERE Clause HELP !]

yes,tried using nvl function , it wont accept it

Invalid syntax error  

[Implementing a Dynamic WHERE Clause HELP !]

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 :)

[Implementing a Dynamic WHERE Clause HELP !]

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

[NVL function in IReport ?]

hey

i got the ISNULL function working :) thanks, 

also there's one more doubt i would like to ask,I need to create a Report with Prompt Box(Parameters) 

now my Database has PC's from 5 different Locations,So how do i group them according to Location(IP Address) ? (Subreport ?)

 

example - It will ask for a prompt box (Location) - supposing i am differentiating them on basis of IP address,

so if i enter IP Range of Location A ,it should just show me data of only those PC's

 

Thanks  

[NVL function in IReport ?]

Hi,

Thanks for the Help

But i found out that ISNULL() works similary to NVL function,now the problem is i cant sucessfully implement

ISNULL function in WHERE Clause For two parameters..any help ?,it wont retrive data

 

Thanks
:)

[NVL function in IReport ?]

guys, i have passed two parameters, (example - IP,Operating_System)

now when i execute the report,if 1st parameter is left blank,how can i just print the remaining parameters (excluding the one left blank)

 

tried using NVL function in Ireport,it says not a built in function.Any other alternative's ?

 

Thanks  

[Help on Cascading Multi Select interface]

Is it possible to create a Cascading Multi Select menu using the template wizard ? , Im new to this and not so good at SQL coding

2) Also is it possible to edit a default Cascading template provided in JasperServer and link it with my report ?

 

Thanks

[ireport acting weird !! URGENT HELP PLEASE]

when i execute charts (Pie Chart),and  when i hit preview it shows me the output,and next time if i log out and come back 

and try to execute the same chart,the output shows blank ! :@ , any idea whats going on ? this is all random 

 

 

2) How do i add percentage tag to pie charts ??

3) How to add hyperlink to each part of Pie chart, corresponding to its equivalent report ?  

 

 

Thanks

[Need Help for iReport 3.7 with SQL2005 DBA, Urgent]

Im pretty new to this,but after reading few tutorials got the Database linked to ireports3.7

I have few queries

I need to prepare a chart-

1.)When i add specific data during template wizard (for eg,Bios,Computer id,Antivirus Version,Manufacturer etc etc) it is showing me valid data of few random sections(example-Bios,Comp id ), while rest is some weird value(ex-Antivirus version,manufacturer)

2.)When i execute the template,if i use blank template (the output is blank),but the same if used with say Cherry(or some other template) it is displaying the output ..help on this please? 

3)Under which section can i find the Internet Protocol (IP address) tab

 

thanks :)

cheers