Link to home
Start Free TrialLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

asked on

SQL Help

Let's say I have a field that contains numbers...

I want to build a query that will return the records that contain a particular number in that field... if no particular number is provided then return all records..

Kind of like "pick the state that you want sales information for else you'll get all state sales information"
Avatar of Aneesh
Aneesh
Flag of Canada image

select * from yourTable where yourNumberColumn = ISNULL(@PassedValue, yourNumberColumn  )

if @PassedValue is NULL it will retun all the non-NULL values from your table
Hi,

given that your column is named ColNumber and your parameter @GivenNumber, you can do the following:

select *
from yourTable
where (ColNumber = @GivenNumber or @GivenNumber is null)

If you don't pass it in, it is null and all rows will be returned. Otherwise, the match will be made. If this is not what you want, please let me know.


Best regards,

Wim
Avatar of Sheldon Livingston

ASKER

Some of the records are null... thus a problem.

I either need the specific records where @GivenNumber is, say, 25 or everything... nulls et al.
SELECT  *
FROM    [YourTable]
WHERE @number IS NULL OR ([YourColumn] IS NOT NULL AND [YourColumn] = @number)

Open in new window

Hi,

the query I posted should give you the desired result. If you enter any non null number, it will search for the records that match in the "ColNumber = @GivenNumber" part. If you don't provide a number, the part "or @GivenNumber is null" will give true for _any_ record and thus return them all, regardless of the content of your column. If that isn't sufficient, please let me know.


Greetings
pourfard... this does the same as the others... it either returns null records or the number provided.
Sorry Wim... it doesn't give me the correct info.

Pretend there are 3 records and ColNumber contains Null, 1, 2 and 3.

If I put in a 2 I want only 1 record.  If I provide nothing I want 4 records.

Using your method I get 1 record when I provide nothing... I get the Null record.
Perhaps the issue is that this isn't the only parameter?

So the query could be likened to:

Select Male or Female that is X years old.

Providing no age and Female should return all Females... even if there isn't an age set for said Female.
-- Create a temp table
DECLARE @Temp TABLE (Numbers INT NULL) 
INSERT INTO @Temp VALUES (1)
INSERT INTO @Temp VALUES (2)
INSERT INTO @Temp VALUES (3)
INSERT INTO @Temp VALUES (NULL)
INSERT INTO @Temp VALUES (4)

DECLARE @Number INT 
SET @Number = 1

SELECT * FROM @Temp
WHERE @number IS NULL OR (Numbers = @number)

--RESULT:
---------------
--1

SET @Number = NULL

SELECT * FROM @Temp
WHERE @number IS NULL OR (Numbers = @number)

--RESULT:
---------------
--1
--2
--3
--NULL
--4

Open in new window

Not sure why pourfard's query doesn't work. You might try coalesce -
select * from table where numberfield = coalesce(@numberpassed,numberfield);
That doesn't seem to work either if any of the number fields are null. Try this -
select * from table where coalesce(numberfield,999999) = coalesce(@numberpassed,numberfield,999999);
Where 999999 is a number that cannot exist in the numberfield.
Here's the code:
SELECT 
  "TJOB" AS "Job",
  "TEXTRA" AS "Extra",
  "TPHASE" AS "Cost code", 
  "PDESC" AS "Description",
  "TCAT" AS "Category",
  "TRANTYP" AS "Transaction Type",
  "TRANDAT" AS "Transaction Date",
  "TACTDAT" AS "Accounting Date",
  "TDESC" AS "Description",
  "TINV" AS "Invoice",
  "TUNITS" AS "Units",
  "TUNTCST" AS "Unit Cost"

FROM 
  "CURRENT_JCT_RECORD_1"

LEFT OUTER JOIN
  "MASTER_JCM_RECORD_3" ON "TJOB" = "PJOB" AND "TEXTRA" = "PEXTRA" AND "TPHASE" = "PHASE"

WHERE 
  "TJOB" = ?
AND
  ("TEXTRA" = ?)
AND
  "TRANTYP" IN ('AP cost','Cash receipts','JC cost','PR cost')
AND
  (Month("TACTDAT") = Month(?) AND Year("TACTDAT") = Year(?))

ORDER BY
  "TPHASE"

Open in new window


Line 24 is where I want to select by individual TEXTRA or everything (including nulls).
It appears that Pervasive 10.0 doesn't dig coalesce(@numberpassed,numberfield)
It appears that Pervasive 10.0 doesn't dig coalesce(@numberpassed,numberfield)
It is a shame you did not indicate that this was not a MS SQL Server question or we could have redirected you to a more appropriate topic area such as: https://www.experts-exchange.com/Database/Miscellaneous/
Not all that familiar with Pervasive SQL, but you might try -
WHERE ... ISNULL(TEXTRA,999999) = ISNULL(@GivenNumber,ISNULL(TEXTRA,999999));
ASKER CERTIFIED SOLUTION
Avatar of Sheldon Livingston
Sheldon Livingston
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Figured out myself