This discussion is archived
5 Replies Latest reply: Dec 3, 2013 6:47 AM by JaniceM RSS

sorting on time column is giving error in 3.1 endeca

user549949 Newbie
Currently Being Moderated

Hi All,

 

When I am doing sorting on event time column I am getting below error:

 

An error occurred while executing query: exception encountered while executing external function 'internal:navigation-query', caused by error endeca-err:MDEX0001 : Invalid input : Error in EQL Query: - In statement "_$Records_1385576435829": In ORDER BY clause: ORDER BY clauses may refer only to attributes defined in the same statement; "EVENT_TIMEYEAR" is not

I am not sure what this error is.What should be done to remove this or should I remove sorting on this column?

 

 

Thanks,

Amrit

  • 1. Re: sorting on time column is giving error in 3.1 endeca
    JM-Oracle Explorer
    Currently Being Moderated

    Amrit,

    Please see this doc about Order By.

     

    In it, in the section "Expression sorting", it is stated:

    "The expressions in the ORDER BY clause will only be able to refer to attributes of the local statement, except through lookup expressions, as shown in these simple statements..."

    Also, see the examples in this topic.

     

    Hope this helps!

     

    Julia

  • 2. Re: sorting on time column is giving error in 3.1 endeca
    user549949 Newbie
    Currently Being Moderated

    Hi Julia,

     

    Thanks a lot for the reply.

     

    Result table gets the data from below view:

     

    Define Events_rt AS

    SELECT ARB("EVENT_TYPE") AS "EVENT_TYPE",

           ARB("BUSINESS_STEP") AS "BUSINESS_STEP",

           ARB("DISPOSITION") AS "DISPOSITION",

           ARB("ACTION") AS "ACTION",

           ARB("BUSINESS_LOCATION") AS "BUSINESS_LOCATION",

           ARB(LOCATION) AS "LOCATION",

           ARB(LOCATION_DESCRIPTION) AS "LOCATION_DESCRIPTION",

           ARB(LOCATION_TYPE) AS "LOCATION_TYPE",

           ARB("EVENT_TIME") AS "EVENT_TIME",

           ARB("EVENT_ATTRIBUTES") as  "EVENT_ATTRIBUTES",

           ARB("TOP_LEVEL_EPC_CODE") AS "TOP_LEVEL_EPC_CODE",

    ARB("RECORD_TIME") AS "RECORD_TIME",

           Count(EVENT_ID) AS "CODE_COUNTS"

    FROM OPSM

    GROUP BY EVENT_ID

     

     

    all other columns are not erroring out except for event time and record time.Does this view has any issue.

     

    Thanks,

    Amrit

  • 3. Re: sorting on time column is giving error in 3.1 endeca
    JM-Oracle Explorer
    Currently Being Moderated

    Amrit, it is possibly the changed treamment of ARB.

     

    What kind of error are you getting for those columns that are erroring?

     

    Please see this topic from the Endeca Server 7.6.x Migration Guide which highlights the differences in EQL (Endeca Query Language), that took place in this release:

    http://docs.oracle.com/cd/E40521_01/server.760/es_migration/toc.htm#Endeca%20Query%20Language%20changes

     

    You will see the following: (quoting from the linked documentation):

    For EQL 7.6.x, the ARB,COUNT, andCOUNTDISTINCT functions have been updated to work with multi-assign attributes.You will also see that "In 7.6, an implicit ARB is no longer added. That is, a SELECT of non-aggregate attributes is no longer allowed".

     

    Also, see the following doc, about the ARB function: http://docs.oracle.com/cd/E40521_01/server.760/es_eql/toc.htm#ARB

    In this doc, it is stated:

    ARB works as follows:

    • For a single-assign attribute,ARBfirst discards all NULL values and then selects an arbitrary but consistent value from the remaining non-NULL values. If the attribute has no non-NULL values, then NULL is returned.
    • For a multi-assign attribute,ARBlooks at all of the rows in the group (including those with empty sets) and selects the set value from one of the rows. In other words, empty sets and non-empty sets are treated equally. This means that because the selection is arbitrary, the returned set value could be an empty set. TheARBreturn type is the same as its argument type: if attribute x is anmdex:long-set, then so is ARB(x). If the attribute has no non-NULL values, then the empty set is returned.
  • 4. Re: sorting on time column is giving error in 3.1 endeca
    user549949 Newbie
    Currently Being Moderated

    Hi Julia,

     

    is it possible to remove the sort form these two columns.These columns are there in results table.Is it possible to remove sorting from some columns in results table.

     

    Thanks,

    Amrit

  • 5. Re: sorting on time column is giving error in 3.1 endeca
    JaniceM Journeyer
    Currently Being Moderated

    The Results Table automatically allows users to click the column heading to sort by the column. There is no way to disable this type of sorting, as it is built into the component.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points