[SQL] Where uit statische query overslaan

[Plaagje]

Het probleem:
We maken gebruik van iReport om rapporten te maken. iReport krijgt vanuit ons programma zijn parameters, deze parameters worden in de query verwerkt.

Het nadeel van iReport is dat je de query niet kan aanpassen (dus geen 'IF' statement om iets wel of niet op te nemen in de query).

Nu kunnen gebruikers vanuit het programma de waarde van de parameters opgeven. Stel dat ze een medewerker moeten selecteren dan kunnen ze 1, meerdere (random) of alle selecteren.De medewerker in de tabel kan ook nog eens NULL zijn.

Normaal zou je om alle uren van alle medewerkers te selecteren de volgende query uitvoeren:

code:

SELECT * FROM UREN u 
LEFT OUTER JOIN medewerker m ON m.medewerker_id= u.medewerker_id

Echter is dat voor mij geen optie aangezien de query ook een WHERE gedeelte moet hebben. Nu kan je het volgende toevoegen:

code:

WHERE medewerker_id IN ()

Echter heeft dit 2 nadelen:
1: IN neemt NULL niet mee
2: Deze selectie moet ook worden gemaakt bij grote tabellen, en om dan 50.000 id's mee te sturen is ook niet handig.


Wat is dus eigenlijk zoek een manier om een gedeelte van de WHERE over te slaan. Echter dat is me nog niet gelukt. Een CASE statement werkt ook niet aangezien je hiermee niet de hele WHERE kan overslaan.

Het liefste zou ik het volgende willen: (maar dat werkt niet)

code:

 CASE WHEN @Variable <> '<ALL>' THEN
WHERE medewerker_id IN () END

Met behulp van de MSDN forums heb ik wel iets gemaakt dat goed werkt, alleen kom ik hierbij maar tussen de 5.000 en 10.000 records per seconde (terwijl als ik mijn user defined functie uit de query haal ik rond de 100.000 p/s zit)

Hieronder de gebruikte user defined functions:
De functie wordt op de volgende manier aangeroepen:

code:

WHERE
Coalesce(uur.medewerker_id, -1) = dbo.CheckIfAll(uur.medewerker_id, '<ALL>')

code:

CREATE FUNCTION [dbo].[CheckIfAll]
(@Field int, @Variable varchar(5000))
RETURNS int
WITH schemabinding
AS
BEGIN
RETURN 
           (CASE WHEN @Variable <> '<ALL>' THEN
                CASE WHEN @Field  IN (select items from dbo.split(@Variable,',')) THEN @Field
                     ELSE NULL
                END                 
           ELSE 
            (CASE WHEN COALESCE(@Field,-1) = -1 THEN -1
                  ELSE @Field
             END)
           END)
END
GO

en de split functie

code:

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))   
WITH schemabinding
as     
begin     
  declare @idx int     
  declare @slice varchar(8000)     
    
  select @idx = 1     
    if len(@String)<1 or @String is null  return     
    
  while @idx!= 0     
  begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
      set @slice = left(@String,@idx - 1)     
    else     
      set @slice = @String     
    
    if(len(@slice)>0)
      insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
  end 
return     
end
GO

Dat bovenstaande functies niet netjes zijn weet ik ook wel Maar door de tekortkomingen van iReport heb ik het nog niet anders werkend gekregen...

[Razr]

Ik snap niet helemaal goed wat je wilt, maar is dit niet iets wat je zoekt:

code:

DECLARE @all AS bit;
SET @all = 0;

SELECT * FROM [Northwind].[dbo].[Categories] WHERE @all=1 OR CategoryID IN (1,2);

Wanneer je @all op true zet, wordt in de WHERE-clausule alleen naar de linkerkant van de OR-operator gekeken, dus wordt alles geretourneerd. Wanneer deze niet true is, wordt naar de rechterkant gekeken en hier kun je dan het filteren toepassen.

[Plaagje]

Ik vond het ook lastig om uit te leggen omdat het probleem eigenlijk alleen maar met iReports voorkomt.

De query die jij aandraagt werkt inderdaad om het het gedeelte uit de query over te slaan wanneer een @all true is. Hartelijk dank daarvoor .

Ik had verder nog 1 probleem mee maar dat is ook al opgelost. in de IN statement moet INT,INT staan, echter krijg ik een enkele string mee. Dit heb ik opgelost door de bovenstaande Split functie te gebruiken:

code:

  ('<ALL>' = '1,2' OR
    uur.medewerker_id IN (select items from dbo.split('1,2',',')))

Hierdoor heb ik een snelheidswinst gehaald van een factor 15 ofzo, dus dat moet het voor de komende tijd maar even mee doen.

[NMe]

Waar hoort mijn topic?

SEA>>PRG