Skip to Content

Below code works fine in SQL but how do i use the same to create a derived table in BO Universe.

DECLARE @StartDate datetime='2017-11-01'

DECLARE @EndDate datetime=SYSDATETIME()

;WITHdaysAS

(

SELECTDATEADD(DAY, n,DATEADD(DAY,DATEDIFF(DAY, 0, @StartDate), 0))as d

FROM (SELECTTOP (DATEDIFF(DAY, @StartDate, @EndDate)+ 1)

n =ROW_NUMBER()OVER (ORDERBY [object_id])- 1

FROMsys.all_objectsORDERBY [object_id] )AS n

)

selectdays.d,count(t.EXPECTEDDATE)

FROMdaysLEFTOUTERJOIN DEPOSIT as t

ON t.EXPECTEDDATE >=days.d AND t.EXPECTEDDATE <DATEADD(DAY, 1,days.d)

GROUPBYdays.d

ORDERBYdays.d;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 06, 2018 at 05:02 PM

    Hi Jaya,

    You can't use Declare or With, in a Derived table in Universe. Try to define n and days as tables in the inner queries, something like this.

    select days.d, count(t.EXPECTEDDATE)
    FROM

    --next 12 lines (include table n) is the SQL for table days
    (
    SELECT DATEADD (DAY, n.rn, DATEADD(DAY,DATEDIFF(DAY, 0, CONVERT(datetime, '2017-11-01')), 0)) as 'd'
    FROM

    --next 5 lines (Italicized) is the SQL for table n

    (
    SELECT TOP (DATEDIFF(DAY, CONVERT(datetime, '2017-11-01'), Sysdatetime())+ 1), ROW_NUMBER() OVER (ORDERBY [object_id]) - 1 as 'rn'
    FROM sys.all_objects ORDER BY [object_id]
    ) n


    ) days LEFT OUTER JOIN DEPOSIT t

    ON (t.EXPECTEDDATE >= days.d AND t.EXPECTEDDATE < DATEADD(DAY, 1,days.d)) --(in this line we're joining tables days and t)

    GROUP BY days.d
    ORDER BY days.d;

    Try that and let us know if that works.

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded