Skip to Content

subselect in flexible search query

select * from ( {{select {ao.pk},{cpf.numbervalue} "account" from {ConfiguredProductFeature as cpf join orderentry as oe on {cpf.orderentry}={oe.pk} join order as ao on {oe.order}={ao.pk}} where {qualifier} = 'account_number'}} ) acc

The above query is running fine but if i change it to

select {acc.pk} from ( {{select {ao.pk},{cpf.numbervalue} "account" from {ConfiguredProductFeature as cpf join orderentry as oe on {cpf.orderentry}={oe.pk} join order as ao on {oe.order}={ao.pk}} where {qualifier} = 'account_number'}} ) acc

I also tried but no luck

select {acc.pk} from ( {{select {ao.pk},{cpf.numbervalue} "account" from {ConfiguredProductFeature as cpf join orderentry as oe on {cpf.orderentry}={oe.pk} join order as ao on {oe.order}={ao.pk}} where {qualifier} = 'account_number'}} ) as acc

This gives me exception: cannot find (visible) type for alias acc within [].

The documentation says we can do it like this: https://wiki.hybris.com/display/release5/FlexibleSearch+Samples#FlexibleSearchSamples-SubselectoverSeveralTypes under "Reporting query with subselect"

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Dec 30, 2018 at 01:50 AM

    The only problem in your FS Query is the use of {} with acc.pk i.e. the correct query is:

     select acc.pk from ( {{select {ao.pk},{cpf.numbervalue} "account" from {ConfiguredProductFeature as cpf join orderentry as oe on {cpf.orderentry}={oe.pk} join order as ao on {oe.order}={ao.pk}} where {qualifier} = 'account_number'}} ) acc
    

    If you do not have the itemtype, ConfiguredProductFeature to test it at this point of time, you can replicate the issue with following examples:

    Correct

     SELECT INNERTABLE.PK, INNERTABLE.CatCode FROM
     (
        {{
           SELECT {p:PK} AS PK, {c:code} AS CatCode FROM
           {
              Product as p JOIN CategoryProductRelation as rel
              ON {p:PK} = {rel:target}
              JOIN Category AS c
              ON {rel:source} = {c:PK}
           }
        }}
     ) INNERTABLE
    

    Incorrect

     SELECT {INNERTABLE.PK}, INNERTABLE.CatCode FROM
     (
        {{
           SELECT {p:PK} AS PK, {c:code} AS CatCode FROM
           {
              Product as p JOIN CategoryProductRelation as rel
              ON {p:PK} = {rel:target}
              JOIN Category AS c
              ON {rel:source} = {c:PK}
           }
        }}
     ) INNERTABLE
    

    By executing the incorrect one, you will get following error message:
    Exception message: cannot find (visible) type for alias INNERTABLE within []

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 26, 2015 at 07:41 AM
    -1

    Curly brackets are parsed by the FlexibleSearch implementation and mapped to the real table name. Since "acc" is just an alias and not an item-type FlexibleSearch simply does not know what to do with it.

    Here's a very basic example how to alias the result of a subquery (in my case: "x"): SELECT x.PK FROM ({{ SELECT {product:PK} AS PK, 'test' AS test FROM {Product AS product} }}) x

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.