CQL SELECT clause syntax - Complex Event Processing

Hi all
I have a small question regarding the SELECT clause syntax.
In my CEP application I'm processing events which hold several properties, mostly primitives, but a few of type java.awt.Point.
I can select the Point object itself in the SELECT clause, but I don't know how to reference the x / y properties of the Point object.
e.g -
     SELECT int1, int2, string1, string2, point1     - works fine.
     SELECT int1, int2, string1, string2, point1.x     - query won't get parsed.
I did manage to access the x / y properties using a custom java method that gets the Point object and returns the x / y properties.
Can I reference the x / y properties directly in the CQL query?
Thanks,
Roy
Edited by: user8868221 on Feb 17, 2010 10:59 AM 

Hi,
What you are doing is correct. CQL currently only supports a small set of built-in types, so if your event contains a property of a type like Point, then you need to use a user-defined function to access and manipulate that property. This is discussed in the CQL documentation:
2.2.2 Handling Other Datatypes Using a User-Defined Function
Support for Java types and custom user-defined classes is coming in a future release.
Hope that helps.
Seth 

Thanks!
Must had missed it in the documentation...
Thanks again,
Roy

Related

Table Functions, Direct Database Requests, and NUMBER data types

Hello. I call a number of table functions from our BI Enterprise server, and I've elected to do so using Direct Database Requests (I believe you can also call table functions in the physical layer of the repository, but that's not what I'm doing). The problem is that whenever I return any number from the table function that is not a whole number (1.23, for example), BI assigns the INTEGER datatype to the field instead of the DOUBLE datatype, thereby rounding my number to the nearest integer. Here's a concise example:
Create these 3 database objects:
CREATE OR REPLACE TYPE my_row AS OBJECT (my_num NUMBER);
CREATE OR REPLACE TYPE my_tab AS TABLE OF my_row;
CREATE OR REPLACE FUNCTION my_table_function RETURN my_tab
PIPELINED IS
BEGIN
PIPE ROW(my_row(1.23));
END;
/
Then make this your query in your Direct Database Request:
SELECT my_num FROM table(my_table_function);
That query correctly returns "1.23" when it's called from the database. In BI, on the other hand, it returns "1" (and labels the field an INTEGER instead of DOUBLE data type). If in the Direct Database Request you change the Column Properties ->Data Format -> Decimal Places from 0 to 2, it then not surprisingly displays "1.00". I then tried changing MY_ROW.MY_NUM's datatype by explicitly specifying precision, and no luck. BI still labels this field as an INTEGER. Then I started trying to trick BI by massaging the SQL statement itself. None of the following worked:
SELECT to_number(my_num) as my_num2 FROM table(my_table_function);
SELECT my_num2 + 0.01 as my_num3 FROM (SELECT my_num - 0.01 AS my_num2 FROM table(my_table_function));
SELECT to_number(to_char(my_num)) as my_num2 FROM table(my_table_function);
SELECT to_number(substr(to_char('x'||my_num),2)) as my_num2 FROM table(my_table_function);
Now I did find a solution, but I'm surprised that I have to resort to this:
SELECT * FROM (SELECT /*+ NO_MERGE */ my_num FROM table(my_table_function));
Does anyone out there know of a better way to do this? The above is a hack in my opinion. :)
Thanks in advance for any input.
-Jim 
bump 
Try to change your definition NUMBER by NUMBER(10,2)
CREATE OR REPLACE TYPE my_row AS OBJECT (my_num NUMBER(10,2));I think that Oracle give by default a NUMBER(1) and it's an INT in OBIEE.
Edited by: gerardnico on Jun 25, 2009 11:37 PM Change 38 by 10,2 in the number 
Yeah, sure seems like that very explicit approach should work, but I had tried that already (and just now retried it just in case my memory was faulty), and it amazingly does not change the BI datatype from INTEGER to DOUBLE (or otherwise positively impact the output data). :(
-Jim 
Yes, it's really amazing.
But I got it.
CREATE OR REPLACE TYPE my_row AS OBJECT (my_num NUMBER(10,2));and in your SQL :
SELECT cast(my_num as double precision) as my_num2 FROM table(my_table_function);I have the good result and I see the numbers after the comma.
Very tricky !
Edited by: gerardnico on Jul 7, 2009 2:55 PM change number(10,2) by double precision ......... pfffff 
Awesome, that did work! Thanks for the perseverance. I knew about CAST, but not about DOUBLE PRECISION, so I probably wouldn't have gotten there on my own. :)
-Jim 
My secret : I just had a look on the obiee cast function and you have only one double ....
http://download.oracle.com/docs/cd/E12096_01/books/admintool/admintool_SQL15.html
And I don't know yet how it decide the data type .... 
and actually, i just verified that CAST solves my problem with some more straightforward types, too. either of the two below work:
SELECT cast(my_num as number(10,2)) as my_num2 FROM table(my_table_function)
SELECT cast(my_num as number) as my_num2 FROM table(my_table_function) 
Yes.
On my computer using a table as source, it had worked perfectly with a number(10,2) but I test your example :
PIPE ROW(my_row(1.23));and it worked for me only with the double precision.
Very strange ...

CQL Select Clause

Hi,
I couldn't do this in CQL:
SELECT c.*
FROM Cat as c, Traffica as t
So, I was obliged to include all columns of a table source in the select list. However, it seems that I may have exceeded the maximum elements in the select list, because I am receiving
"generic parser error. Cause: This is a generic parser error Action: This is a generic parser error"
in Eclipse.
(in my case +247 elements)
Is there such limit? For tuple event types, is there a limit on the number of properties? How do I get around this?
Regards,
Jarell
Edited by: Jarell on Mar 28, 2011 10:23 PM 
Hi Jarrell,
Yes there is such a limit which we have increased in PS5. We have also fixed the select problem you encountered in PS5. If you need a fix to this on PS3 you would need to escalate through support quoting bug numbers 9975485 & 10177196.
Thanks
andy 
Hi Jarell,
The workaround for this is to create the event type as a wrapper POJO with one attribute of type POJO1 - i.e
class WrapperEventType {
public c1 ActualEventTypePOJO;
...
}
The "ActualEventTypePOJO" can have as many attributes as one wants and these can be accessed using the Java Cartridge. 
Thanks Andy. How can we increase the default 256 limit on String attributes? We have some data which have more than 256 chars and this is the reason why we resorted to tuples.
Is it possible to include a tuple object inside a JavaBean event type? 
There are a couple of options
1) Define your event type as tuple to begin with and specify the 'length' attribute appropriately. You can set and get values into a tuple object using the EventType API.
Example -
<wlevs:event-type type-name="YourEventType">
<wlevs:properties>
<wlevs:property name="stringValue" type="char" length="1024"/>
</wlevs:properties>
</wlevs:event-type>
2) Use a wrapper class around String and use that class as the type of the property 
Hi Anand,
We are doing your first suggestion, but going back to our issue, we could not fit all columns in a select clause for 2 joined channels since they exceed the maximum limit.
For your suggestion number 2, this requires a tuple type parent event for the wrapper right? Isn't that the same scenario as the first?
Regards,
Jarell 
Ok got it working on a Javabean parent.. will let you know how it goes with the Java cartridge. Thanks. 
it works. thanks guys! 
Jarell wrote:
Hi Anand,
We are doing your first suggestion, but going back to our issue, we could not fit all columns in a select clause for 2 joined channels since they exceed the maximum limit.
For your suggestion number 2, this requires a tuple type parent event for the wrapper right? Isn't that the same scenario as the first?
Regards,
JarellJust to clarify and close. (2) specifically requires a Java event type wrapper not a tuple wrapper.
Thanks
andy

How to use one Assign action to create multiple context variables

Hello, everyone.
I read some tips from Oracle documentation that said:
Avoid creating many OSB context variables that are used just once within another XQuery
Context variables created using an Assign action are converted to XmlBeans and then reverted to the native XQuery format for the next XQuery. Multiple "Assign" actions can be collapsed into a single Assign action using a FLWOR expression. Intermediate values can be created using "let" statements. Avoiding redundant context variable creation eliminates overheads associated with internal data format conversions. This benefit has to be balanced against visibility of the code and reuse of the variables.
Oracle® Fusion Middleware Performance and Tuning Guide
+11g Release 1 (11.1.1)+
Part Number E10108-03
But I don't know how to do that. Can you show me ?
Thank in advance
Edited by: Doubt_Man on Aug 17, 2011 3:30 PM 
Please refer -
http://www.w3schools.com/xquery/xquery_flwor.asp
http://www.xquery.com/tutorials/flwor/
You can write simple XQueries (using FLWOR) for replacing multiple assign actions with one assign action. Remember this use case may not be fit for all the requirements but in cases where many intermediate variables are being defined, this approach is very useful.
Regards,
Anuj 
Thank for your attention, Mr. Anuj
I just want to ask for using that approach in assign action. Now, I can do it.
Big thank to you.
Regards
Cuong Pham 
I cannot get a returned variables 
I cannot get a returned variables 
I cannot get a returned variables with this Xquery expression for Assign action
let $docId := $body/por:checkStatus/documentId/text()
let $depId := $body/por:checkStatus/departmentId/text()
let $srvId := $body/por:checkStatus/serviceId/text()
return ($docId, $depId, $srvId)Plz help me to correct it. Thank in advance
Edited by: Doubt_Man on Aug 18, 2011 9:38 AM 
May I know what do you want to return? Do you want to return a concatenation of all three variables - docId, depId, srvId? If yes use fn:concat() XQuery function.
Regards,
Anuj 
if you return sequences, you can declare the return type of your xquery as xs:double*
(notice the asterix at the end)
but I have the impression that in the assign action only the first element will be assigned to the context variable
(correct me if I am wrong)
So in fact you might indeed have to transform the sequence into a element()*, or concatenate it into a CVS string - for instance using string-join
http://www.xqueryfunctions.com/xq/fn_string-join.html 
Dear Misters :)
I want to set value for three context variables ($docId, $depId, and $srvId) for using later. I think my return statement in xquery snippet is wrong, can you fix it for me ?
Thanks

rdfs:range and inference

Hi
If we have "p rdfs:range aClass" OWLPrime does not seem to infer that p is a property. Therefore p has to be defined explicitly as a property before using it in a SEM_MATCH query. Is that how OWLPrime should work?
Regards Christian
Edited by: chrd00 on Sep 23, 2011 4:25 AM 
Hi Christian,
It is a good idea to explicitly type your properties. In this case, p could be an owl object property, datatype property, annotation property, rdf:property etc.
RDFSAXIOMS component does include the following RDFS axiomatic triple.
rdfs:range rdfs:domain rdf:Property .
If you do want to see p type rdf:Property then you can include component RDFSAXIOMS on top of OWLPRIME with your create_entailment call.
Alternatively, you can simply insert the above axiomatic triple into your graph and inference engine will take care of the rest.
Thanks,
Zhe Wu

Analytic Functions

Hello , can you tell me how do I create and introduce an analytic function in the mapping canvas?
Is there any particular operator for this?
I know the code I want to use but I don't know how to because I am doing everything for first time. Thank you very much. 
Use an Expression operator. You will need to configure the code generation and default operating mode to be set based only.
Cheers
David 
Hello, I have a few queries about analytic functions, because I am using them for first time.
1. What data type should I define to the arguments? The data type is for the column as a name or for the ιτσ ψ its contents?
2. I need to get only the new column, so what do I have to write in the RETURN line in my function?
My code is this: SELECT COLUMN, COUNT(*) OVER (PARTITION BY COLUMN) ALIAS
FROM TABLE;
RETURN NULL ;
3. As far as Expression Editor is concerned, I am not sure what I should write in the Expression Builder. The new field created will be stored in the target table.
Your help would be essential. Thank you. 
Check this forum thread where an analytic function is used, simply use the aggregator;
How to Use LISTAGG function # OWB 11.2.0.3 (11gR2)
Cheers
David 
I' ll go crazy!
Thanks. 
...but for analytic functions without a GROUP BY, just use the expression operator like this ....
https://blogs.oracle.com/warehousebuilder/resource/images/owb_analytic_sql.jpg
Cheers
David 
Ok , thank you very much. I think I' ll use the aggregator.
But what about the data type of the arguments and then RETURN line in analytic function?
I don't know anything about analytic functions. 
The attribute's expression uses the analytic function, for example COUNT(*) OVER (PARTITION BY COLUMN)
The attribute's datatype is dependent on the function and input datatype, mostly numeric/numbers. You should just define expressions referencing the functions in OWB, you shouldn't need to wrapper in any function - for this case.
Cheers
David

Categories

Resources