BusinessObjects Board

Select statement in BQ

other Business Query
#1

I am trying to get a query running and using Business Query to help get output so I can move onto other parts.

T
Knowing that I need to create a script to find the following :

Folder name = Business Reports
Subfolders that exists look like this: 2014_01_01; 2014_02_01; 2014_03_01…

< need to get all subfolders that are greater than 100 days as of today >

select * from CI_INFOOBJECTS Where CHILDREN(“SI_NAME=“Folder Hierarchy”,“SI_KIND=Folder”) and SI_CREATION_TIME < '”
& Year(DateAdd(“d”, -100, Date)) & “.”
& Right(“00” & Month(DateAdd(“d”, -100, Date)), 2) & “.”
& Right(“00” & Day(DateAdd(“d”, -100, Date)), 2)
& “’ order by SI_CREATION_TS”

… I am not getting the return I am expecting(getting error) but know that I should see the 2014.01.01 as one of the results !?

Something is not right with the query and been looking at it for a long time. Another pair of eyes would be great if anyone can help please

My DB is DB2
:?

ColumbiaIT (BOB member since 2011-06-15)

#2

You may be putting too much into the query to figure what is going wrong.
Try doing a simple part first like…

select * from CI_INFOOBJECTS Where CHILDREN(“SI_NAME
=‘Folder Hierarchy’”,“SI_KIND=‘Folder’”) order by SI_CREATION_TS

If this works than add on more of the SQL to it until you come up with the correct syntax.

BOETL2012 (BOB member since 2012-08-27)