In a search query if field is blank show all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

In a search query if field is blank show all

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-02-25 : 05:24:40

I have created a search query which, I have to thank visakh16 for all his help on. When I first created I used the OR operator to select the fields but this was not what I wanted and changed it to the AND operator. By doing this I never recieve any results when a field is left blank. So what I think I need is an if a field is blank it will ignore it from the query. I will paste code below any help would be gratefully appreciated.

ps. all the variables are on the bottom line, I have manually filled in the variables apart from the position and my division swhich are blank.

SELECT m.*
FROM
(SELECT distinct installation.id,installation.name, installation.company_id, installation.installationidentifier, installation.permitidentifier, installation.mainactivitytypecode, installation.countrycode,person.city,person.zipcode, person.address1,installation.mainactivitytypecodelookup,installation.person,installation.account,installation.eperidentification,installation.latitude,installation.longitude,installation.archive,installation.fuel_id
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier

GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.year1 between 2008 AND 2012)p
WHERE (m.city = 'london') and (m.Fuel_ID = '1') and (ad.allowanceDistributed between '100000' AND '200000') and (p.position between '' AND '') and (t.myDivision between '' AND '')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:22:31

Here is a starter

WHERE (m.city = @city or @city = '')
and (m.Fuel_ID = @fuel or @fuel = '')
and (ad.allowanceDistributed between @distfrom AND @distto)
and (p.position between @p1 AND @p2 OR p1 = '' )
and (t.myDivision between @d1 AND @d2 OR @d1 = '')

E 12°55'05.63"
N 56°04'39.26"

souLTower
Starting Member

39 Posts

Posted - 2009-03-03 : 09:27:55

This is a situation where a case statement can be used in the where clause.

Where m.Fuel_ID = CASE WHEN @fuel = '' THEN m.Fuel_ID else @fuel end

God Bless

Ats
Starting Member

32 Posts

Posted - 2009-03-04 : 07:19:02

I have used the stor procedure and it creates the procdure but when I run the procedure I get the following error Msg 8115, Level 16, State 2, Procedure Searchccm, Line 21
Arithmetic overflow error converting expression to data type int.

CREATE PROCEDURE Searchccm
(
@city nvarchar(255) = null,
@fuel int = null,
@distfrom int = null,
@distto int = null,
@p1 int = null,
@p2 int = null,
@d1 int = null,
@d2 int = null
)

as

SET NOCOUNT ON

SELECT m.*
FROM
(SELECT distinct company.company_id, person.city, installation.fuel_id
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.year1 between 2008 AND 2012)p
WHERE (m.city = @city or @city = '') and (m.Fuel_ID = @fuel or @fuel = '') and (ad.allowanceDistributed between @distfrom AND @distto) and (p.position between @p1 AND @p2 OR @p1 = '' ) and (t.myDivision between @d1 AND @d2 OR @d1 = '')

souLTower
Starting Member

39 Posts

Posted - 2009-03-04 : 07:22:55

It sounds like your input variables need to be tested and converted to an appropriate numeric amount.

God Bless

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:14:07

check if all expressions contain integer fields dont have any other fields with non numeric values

Ats
Starting Member

32 Posts

Posted - 2009-03-04 : 11:47:14

When it runs the stored procedure it uses this script

USE [ccm]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Searchccm]
@city = N'london'

SELECT 'Return Value' = @return_value

GO

which gives the error

Msg 8115, Level 16, State 2, Procedure Searchccm, Line 21
Arithmetic overflow error converting expression to data type int.

Ats
Starting Member

32 Posts

Posted - 2009-03-04 : 12:13:27

even when I take out the stored procedures and just use the query I still get the data type error

SELECT m.*
FROM
(SELECT distinct company.company_id, person.city, installation.fuel_id
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.year1 between 2008 AND 2012)p
WHERE (m.city = 'london' ) and (m.Fuel_ID = '3') and (ad.allowanceDistributed between '0' AND '200000') and (p.position between '0' AND '200000') and (t.myDivision between '0' AND '100')

Ats
Starting Member

32 Posts

Posted - 2009-03-04 : 12:59:57

I have got it working with no errors, but whenever I run the query I get no results even if I leave the search criteria blank which should bring up all rows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Searchccm4]
(
@city nvarchar(255) = null,
@fuel int = null,
@distfrom int = null,
@distto int = null,
@p1 int = null,
@p2 int = null,
@d1 int = null,
@d2 int = null
)

as

SET NOCOUNT ON

SELECT m.*
FROM
(SELECT distinct installation.id,installation.name, installation.company_id, installation.installationidentifier, installation.permitidentifier, installation.mainactivitytypecode, installation.countrycode,person.city,person.zipcode, person.address1,installation.mainactivitytypecodelookup,installation.person,installation.account,installation.eperidentification,installation.latitude,installation.longitude,installation.archive,installation.fuel_id
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier

GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.year1 between 2008 AND 2012)p
WHERE (m.city = @city or @city = '') and (m.Fuel_ID = @fuel or @fuel = '') and (ad.allowanceDistributed between @distfrom AND @distto) and (p.position between @p1 AND @p2 OR @p1 = '' ) and (t.myDivision between @d1 AND @d2 OR @d1 = '')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:04:42

are fields like b.SurrenderedCers,a.cer,compliance.allowanceDistributed,compliance.verifiedEmissions,... used in expressions contain only numeric data?>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources