vendredi 24 juin 2016

Using SQL query gives error message

I'm trying to create a report with iReport by using below query which runs fine on SSRS reports. I'm new to JasperReports. How can I declare variables inside the report query? Here is my SQL query:

 Declare @SDate as Date
    Declare @EDate as Date
    Declare @JNumber as Char(8)


    set @SDate = case When @StartDate > '1/1/2000' Then @StartDate Else GETDATE() End
    set @EDate = case When @EndDate > '1/1/2000' Then @EndDate Else GETDATE() End
    set @JNumber = ISNULL(@JobNumber, '')

    select Distinct
    j.JobNumber, j.CustomerRep, j.JobStatus, j.StatusDate, a.Answer14 'Ship Time' ,j.LastShippedDate, Cast(m.ExpectedDate as Date) 'ShipDate',
    m.CustomerCode, RTrim(m.CustLongName) , RTrim(m.[Description]) 'JobName', RTrim(m.PONumber) 'PONumber', m.ProductCode, m.QuantityOrdered, a1.answer8'Pages'

     from JobExtra j (NOLOCK)
    Join JobMaster m (nolock) on m.JobNumber = j.JobNumber

where (@JNumber != '' AND @JNumber = j.JobNumber)
    And @SDate <= Cast(ISNULL(ce.sStartDate, m.StartDate) As Date)
  And @EDate >= Cast(ISNULL(ce.sStartDate, m.StartDate) As Date)


order by Case When @Sort = 'Ship Date' Then m.ExpectedDate When @Sort = 'Start Date' Then m.StartDate Else j.JobNumber End
, j.JobNumber, ce.sEndDate, ce.sEndTime, ce.sStartDate

Aucun commentaire:

Enregistrer un commentaire