subselect in flexible search query

[former_member1002389]

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-SubselectoverS... under "Reporting query with subselect"

[former_member620692]

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 []

[former_member704091]

arvind-kumar-avinash that was helpful. I could create a inner query of my own using your sample.

Thanks you.

[Former Member]

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

[former_member1002389]

I did the same Martin. If you will change it x.test instead of x.PK then it will be failed.

[former_member1002389]

I ran below query that got failed.

select acc.account from ( {{select {ao.pk},{cpf.numbervalue} as "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