Joe
|
| Posted: 07/29/2004, 10:11 AM |
|
Does anyone know what to put in the default value of a SQL parameter to return all values if the value is Null
I have a search form,
There are three Filters
Class
Carrier
Customer
If then fill out just 2 i want all the record to return from the one not fill in with a value.
Any help would be great,
I thought it was % or * but tried both and no Luck, Thanks
|
|
|
 |
dataobjx
Posts: 181
|
| Posted: 07/29/2004, 11:43 AM |
|
Try
Select * From TableName Where Field1 IS NULL
Or, if you wanted all records beginning with a value it would be
Select * From TableName Where Field1 Like 'D%'
and so forth.
Is this what you're after?
If not, post your sql so we can put things in context.
_________________
www.DataObjx.net
www.mydigitalapps.com |
 |
|
Joe
|
| Posted: 07/29/2004, 11:56 AM |
|
Sort of, what i am trying to do is, I have a grid, That has the following fields,
Customer
Carrier
Amount
AVG. Amount
etc....
Then i have a search page. You can search by Customer, which would retunr all the carriers with that customers name on it. and I have Carriers, which would return all the Carriers with that name. On my search form, I only Fill out customer, i get nothing. i have to fill out both to get and grid result. i do not want to have this. i want either one or the other, or even both, what ever the users wants.
Here is my currnet code.
Select Customer_Name, Company,ID,SCAC,
COUNT(PRONUM) as 'Total Pros',
SUM(AMOUNT) as 'Total Amount',
AVG(AMOUNT) as 'Avg Amount',
SUM(WEIGHT) as 'Total Wgt',
SUM(PCS) as 'Total Pcs',
SUM([COST SAVE]) as 'Total Cost Savings',
SUM(case when B = 'I' then 1 else 0 end) as 'I',
SUM(case when B = 'O' then 1 else 0 end) as 'BO',
SUM(case when B = '3' then 1 else 0 end) as '3'
FROM PRODETAIL
WHERE ( [PRO DATE] >= '{PRODATE}'
AND [PRO DATE] <= '{PRODATE1}' )
AND [GROUP] = '{GROUP}'
AND ID = '{ID}'
GROUP BY Customer_Name, ID, Company, SCAC
|
|
|
|
dataobjx
Posts: 181
|
| Posted: 07/29/2004, 12:51 PM |
|
On the grid sql pane, click the + key and add
( Customer_Name LIKE '%{s_customername}%' )
PS> If you have any other filters in the sql pane, you may need to select the one above this one and change the and to an or in the listbox...
If this doesn't work, register & login at dataobjx.net and upload your code - then assign the file to DataObjx so I can have a look.
_________________
www.DataObjx.net
www.mydigitalapps.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 07/29/2004, 10:52 PM |
|
Joe,
You can change "AND" to "OR" so that any matching condition would be found, rather than require all conditions to match.
For example:
WHERE ( [PRO DATE] >= '{PRODATE}'
AND [PRO DATE] <= '{PRODATE1}' )
OR [GROUP] = '{GROUP}'
OR ID = '{ID}'
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 07/29/2004, 10:54 PM |
|
Actually, you mentioned Customer name, but your WHERE statement doesn't seem to include any text searches. If you need to specify some default values for non-text fields then try this:
WHERE ( [PRO DATE] >= '{PRODATE}'
AND [PRO DATE] <= '{PRODATE1}' )
AND ([GROUP] = {GROUP} OR {GROUP}=-1)
AND ([ID = '{ID}' OR {ID}=-1)
Then set the default values of those numeric parameters to -1.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
