Derived Tables

[system]

Hi Guru’s,

Is it possible to create Predefined filter from the derived tables.

Iam having few derived tables in the universe and wheever iam trying to create a predefined filter from that particular table objects iam getting the following error

"Table or View does not exist "

any idea or thoughts would be very usefull.

Many Thanks,
Kumar.

---

kumarpjp (BOB member since 2009-04-13)

[system]

HI,

Can you please paste what you are trying to use in the condition and which one is the Derived table in the construct?

Cheers.

---

BO_Stuffed (BOB member since 2008-03-29)

[system]

Hi

I have created a table named “system” as derived table

And iam using the Date object in the condition so that i can create a predefined condition which will bring Last 7 days data into the report.

i have created the same for the Table and its working fine but when i create teh same condition for the derived tables its giving error.

have attached the screen shot hope it helps.

Thanks,
kumar.
Doc11.doc (50.0 KB)

---

kumarpjp (BOB member since 2009-04-13)

[system]

You can use filters on derived table and use it in your query.
But you have to desing that filters with valid name of derived table

Here is the sample query:

SELECT
Table__3.MGR_NAME,
TO_NUMBER(TO_CHAR(Table__3.MGR_HIREDATE,‘SYYYY’)),
SCOTT.EMP.ENAME,
TO_NUMBER(TO_CHAR(SCOTT.EMP.HIREDATE,‘SYYYY’)),
Table__3.MGRSAL,
SCOTT.EMP.SAL,
SCOTT.DEPT.DNAME,
SCOTT.DEPT.LOC
FROM
(
select e.empno MgrEmpno,e.deptno Mgdeptno,e.ename Mgr_name,e.hiredate Mgr_Hiredate,
e.sal MgrSal,e.mgr Mgrno
from SCOTT.EMP e,SCOTT.EMP f
where e.empno=f.mgr
) Table__3,
SCOTT.EMP,
SCOTT.DEPT
WHERE
( SCOTT.EMP.DEPTNO=Table__3.MGDEPTNO )
AND ( SCOTT.EMP.DEPTNO=SCOTT.DEPT.DEPTNO )
AND ( SCOTT.EMP.MGR=Table__3.MGREMPNO )
AND
( Table__3.MGDEPTNO in @Prompt(‘Chose Depart Ment’,‘A’,“Manger”.‘Manger\Mgdeptno’,multi,free) )

In this code i used ,“Manger” as my derived table

---

Rajubollas (BOB member since 2009-09-04)

[system]

Try lyk this:

@Select(Manger\Month of Mgr Hiredate) BETWEEN @Select(Manger\Month of Mgr Hiredate) AND to_char(sysdate,‘MM’)

---

Rajubollas (BOB member since 2009-09-04)

[system]

Does your parent object parse fine?

Can you also replace the @select function directly with the object and try once more to parse it?

Rajubollas has anyway given you some points to check.

Please do confirm the result.

---

BO_Stuffed (BOB member since 2008-03-29)

[system]

Hi Friends,

Thansk fro your quick reply’s.

i have tried this for the Last 7 days Predefined Filter in the unv level but its not working.sorry for the pain but can you please help me in getting this done as iam due to the delivery.

So here is the scenario

Have created a Derived table Disk and in the disk iam having a Date column and its been parsed and working fine.

Now iam creating a predefined filter for Last 7 Days.

i tried with this initially

months_between(@Select(Logical Disk\Date),sysdate) between -0.25 and 0

its giving error Like “INVALID TABLE NAME”

as you suggested i tried giving the exact derived table name

months_between(@Select(Table__121\Date),sysdate) between -0.25 and 0

and iam getting the same error.

Can you let me no the excat way how can i implement this predefined condition.

Many Thanks,
Kumar

---

kumarpjp (BOB member since 2009-04-13)

[system]

HI,

Most of the things start getting wrong around the delivery dates

As I mentioned, instead of using @select function, can you try using the column name straight from the derived table.

months_between(,sysdate) between -0.25 and 0

I am not sure if this would work but give it a go.

Cheers.

---

BO_Stuffed (BOB member since 2008-03-29)

[system]

months_between(@Select(Table__121\Date),sysdate) between -0.25 and 0

That’s never going to work is it? @Select should be used for objects.
Once your derived table is created it should be treated like any other table in the universe.
You need to specify what database you are using… but since you say that you have this same syntax working with a table (not derived) you should be able to use that syntax.

---

dessa (BOB member since 2004-01-29)

[MarkP]

Looking at the syntax used, I’d suspect very strongly that it’s Oracle, dessa.

kumar, back to basics.
Can you view the table values from the derived table?
Have you tested that the derived table objects work in a query on their own?

In English (not code), what do you want the filter to do?

[system]

Hi Friends,

I tried using the Column name from the derived table but no luck.

As Dessa suggested i can use the syntax from other tables but the reason why i can’t do like that is because this tables are not joined to each other.
like iam having four set of tables Hourly,daily,weekly,detail all with same table names only a slight change.

i tried to manage the H,D,W tables by joinningthem and creating the condition etc.

but now the last set of table are crating a for me coz i dont have any common column to join them.

Any thought to make done will be appreciated.

Thanks,
Kumar.

---

kumarpjp (BOB member since 2009-04-13)

[system]

@Mark P

Looking at the syntax used, I’d suspect very strongly that it’s Oracle, dessa.

kumar, back to basics.
Can you view the table values from the derived table?
Have you tested that the derived table objects work in a query on their own?

In English (not code), what do you want the filter to do?

Hi Mark,

i can able to view the Derived table values and i can also able to run reports on that table all is fine but when iam trying to create a Predefined filter its not allowing me to do so …

Thanks,
Kuamr.

---

kumarpjp (BOB member since 2009-04-13)

[system]

The problem sounds more funadamental that you first stated. As Mark suggests, give the full description of the problem and what you want the filter to do in plain English.

At this moment it looks like you should be utilising Aggregate Aware …

---

dessa (BOB member since 2004-01-29)

[system]

Hi Dessa,

I will try to keep the issue more clear .

I have created a derived table “DISK” and this table is a independent and not joined with any other table.

Now i want to create a predefined filter in this tables named Last 7 days ,
so when the user run a query he will be getting last 7 days data into the report.

i have created this Filter for other DB Tables in the schema and they are working fine now when i tried creating this for the derived table DISK and its poping up with an error mesage “TABLE OR VIEW DOES N’T EXIST”

Thanks,
Kumar.

---

kumarpjp (BOB member since 2009-04-13)

[MarkP]

@kumarpjp

Hi Dessa,

I will try to keep the issue more clear .

I have created a derived table “DISK” and this table is a independent and not joined with any other table.

Now i want to create a predefined filter in this tables named Last 7 days ,
so when the user run a query he will be getting last 7 days data into the report.

i have created this Filter for other DB Tables in the schema and they are working fine now when i tried creating this for the derived table DISK and its poping up with an error mesage “TABLE OR VIEW DOES N’T EXIST”

Thanks,
Kumar.

What is the syntax in your predefined filter?

Please post the EXACT SQL.

[system]

Hi Mark,

Iam using this syntax for the predefined filters

months_between(@Select(System\Date), sysdate) between -0.25 and 0

when i use this syntax for the Tables its working fine but when i use the same syntax for the derived table it not working.

Derived table syntax:-

months_between@Select(Logical Disk\Date), sysdate)between -0.25 and 0

Logical Disk is the derived table.

Many Thanks,
Kumar.

---

kumarpjp (BOB member since 2009-04-13)

[MarkP]

What is the SQL statement behind Logical Disk\Date

Logical Disk is NOT a derived table, it is a class.
Date is an object and should be renamed to something more meaningful. What date is it?

[system]

Yeah Logical disk is the class and Date is an object in that table.

The date is the object which consists the day to day dates (5/10/2009 12:00:00 AM)

so iam using the date object to Bring the Last 7 days data in the filter.

Thanks,
Kumar

---

kumarpjp (BOB member since 2009-04-13)