Problem with ireport Parameters 4.0.1

Asked

Viewed 21 times

0

Good night, I’m doing a report on Ireport 4.0.1 and I’m having difficulty with some parameters. I have a billing report that requires the code here for some filters. The same are, the initial and final date range, the type of operation and the company. These are mandatory filters and they work normally. However, I am trying to add two more parameters, one that is Profile per product and one per seller, but even though these two are not mandatory parameters if I search the report leaving these two fields empty, it does not return me anything, already filling in all the parameters and returning the data to me normally. I would like to know what I need to do so that these two parameters are ignored if they are not filled in. Follow the query :

select
TO_CHAR(cab.codparc,'00000000') AS CODPARC,
par.razaosocial,
TO_CHAR(cab.codparc,'00000000')||' - '||par.razaosocial as parceiro,
par.razaosocial,
TO_CHAR(cab.dtmov,'DD/MM/YYYY') AS DATA,
TO_CHAR(CAB.dtmov,'HH:MM') AS HORA,
cab.codtipoper,
cab.nunota,
cab.numnota,
cab.codemp,
cab.tipmov,
ite.codprod,
pro.descrprod,
ite.qtdneg,
ite.codvol,
ite.vlrunit,
ven.apelido,
cid.nomecid,
pro.pesoliq,
(ite.vlrtot-ite.vlrdesc)*(case when cab.tipmov='D' then -1 else 1 end) as vlrtot
from tgfcab cab
inner join tgfite ite on (ite.nunota=cab.nunota)
inner join tgfpro pro on (ite.codprod=pro.codprod)
inner join tgfpar par on (cab.codparc=par.codparc)
inner join tgftop tpo on (cab.codtipoper=tpo.codtipoper and cab.dhtipoper=tpo.dhalter)
inner join tgfven ven on (ven.codvend = ite.codvend )
INNER JOIN TSICID CID ON (PAR.CODCID=CID.CODCID)
where cab.tipmov in ('V','D','T')
AND CAB.STATUSNOTA = 'L'
and cab.dtmov between trunc($P{Dtini}) and trunc($P{Dtfin})
and cab.codtipoper BETWEEN $P{Topini} AND $P{Topfin}
and cab.codemp= trunc($P{Empresa})
and pro.codprod = $P{Produto} or $P{Produto} is null
and ven.codvend = $P{Vendedor} or $P{Vendedor}  is null 
ORDER BY cab.DTMOV, cab.NUMNOTA
oracle ireport

1 answer

1

Missing you isolate with parentheses ( ) the parameters that will be null Test with the code below;

    select
    TO_CHAR(cab.codparc,'00000000') AS CODPARC,
    par.razaosocial,
    TO_CHAR(cab.codparc,'00000000')||' - '||par.razaosocial as parceiro,
    par.razaosocial,
    TO_CHAR(cab.dtmov,'DD/MM/YYYY') AS DATA,
    TO_CHAR(CAB.dtmov,'HH:MM') AS HORA,
    cab.codtipoper,
    cab.nunota,
    cab.numnota,
    cab.codemp,
    cab.tipmov,
    ite.codprod,
    pro.descrprod,
    ite.qtdneg,
    ite.codvol,
    ite.vlrunit,
    ven.apelido,
    cid.nomecid,
    pro.pesoliq,
    (ite.vlrtot-ite.vlrdesc)*(case when cab.tipmov='D' then -1 else 1 end) as vlrtot
    from tgfcab cab
    inner join tgfite ite on (ite.nunota=cab.nunota)
    inner join tgfpro pro on (ite.codprod=pro.codprod)
    inner join tgfpar par on (cab.codparc=par.codparc)
    inner join tgftop tpo on (cab.codtipoper=tpo.codtipoper and         cab.dhtipoper=tpo.dhalter)
    inner join tgfven ven on (ven.codvend = ite.codvend )
    INNER JOIN TSICID CID ON (PAR.CODCID=CID.CODCID)
    where cab.tipmov in ('V','D','T')
    AND CAB.STATUSNOTA = 'L'
    and cab.dtmov between trunc($P{Dtini}) and trunc($P{Dtfin})
    and cab.codtipoper BETWEEN $P{Topini} AND $P{Topfin}
    and cab.codemp= $P{Empresa}
    and (pro.codprod = $P{Produto} or $P{Produto} is null)
    and (ven.codvend = $P{Vendedor} or $P{Vendedor}  is null) 
    ORDER BY cab.DTMOV, cab.NUMNOTA

Browser other questions tagged oracle ireport

You are not signed in. Login or sign up in order to post.