| Author |
Message |
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
   Topic: How to get a null value from table to a parameter
Posted: 05 Jul 2011 at 11:28pm |
i have a table 'Subject'with following fields and values ID Name 1 Science 2 Maths 3 NULL But when i populate the 'Name' field in parameter ,i am not getting the 'NULL' (third record).but it is required for filtering. Any help asap is appreciated . Thank You.
|
|
bbbbb
|
 IP Logged |
|
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
Posted: 06 Jul 2011 at 3:24am |
you can leave a blank or enter null you have to use something else
does null retrieve all your empty records or is that for all values?
|
|
sharona
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 3:25am |
do you mean that you have NULL values in a field and you are trying to make a dynamic parmater that allows for users to select one item to fiter on?
Are you allowing users to select more than 1 option int he paramter (set to allow multiple values)?
edit: sorry sharona, posting at the same time as you 
Edited by DBlank - 06 Jul 2011 at 3:40am
|
|
IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 06 Jul 2011 at 3:32am |
Thank u FYI. i want the third record should populate on the lov as NULL. i think the sql query for populating the lov, is omittimg the third record as dbnull.is there any way to populate it. 
|
|
bbbbb
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 4:16am |
I will assume that the NULL vlaue is not text but rather an empty/null value.
Assuming you want the user to select NULL in the lov and have it only return these empty rows and the lov is a dynamic list built from the table.
you could use a union command to add the text of NULL to the lov and then use teh command as your lov source
|
|
IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 06 Jul 2011 at 4:20am |
|
Thank u .you are absolutely right. iwill try the way,what u said and keep on inform u..
|
|
bbbbb
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 4:25am |
you may want to use 'None' instead of 'Null' as this makes more sense to an end user. The verbiage does not matter.
Assuming you use 'None you select statement will end up looking something like this:
(isnull({table.name}) and {?parameter}='None')
or
({?parameter}={table.name})
|
|
IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 09 Jul 2011 at 7:04pm |
i'm using selct ID,Name from Subject; as sql statement in the command.And i selcted that command as source in the dynamic parameter. But it is not displaing the third Row name. can you please send me the sql for the command.
|
|
bbbbb
|
|
IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 09 Jul 2011 at 8:57pm |
I created a command using this statement SELECT 'None' as NAME FROM SUBJECT UNION SELECT DISTINCT NAME FROM SUBJECT and i created a dynamic parameter using this command.The LOV is displaying NOne as an item and i added the formula what you have give n in the previous post in select expert. but when i try to generate the report, it is going to an endless loop.Any solution,pls help
|
|
bbbbb
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Jul 2011 at 3:32am |
check your joins fromt he command to the table(s)
also if you chaneg the 'None' to ' None' the lead space with move that
value to the top of your alpha list but you will need to alter the select statement to use the ' None' as well
|
|
IP Logged |
|
|
|
