Does Flexible Search support sub-query in FROM clause

[former_member1364266]

Hi folks, It is possible to join included query? I wrote simplified example, I think idea is clear. Any advice would be appreciate. Thanks.

 SELECT x.code, y.code
 FROM ({{ SELECT {p.code} FROM Product AS p}}) AS x
 LEFT JOIN ({{SELECT {p1.code} FROM Product AS p1}}) AS y
 ON x.code = y.code

[Former Member · Accepted solution]

Hi Vova,

This one works for me

 SELECT x.code, y.code FROM
 ({{
     SELECT {p.code} AS code FROM
     {
         Product AS p
     }
 }}) x
 LEFT JOIN
 ({{
     SELECT {p.code} AS code FROM
     {
         Product AS p
     }
 }}) y
 ON x.code = y.code

[former_member1364266 · Accepted solution]

Thanks a lot I it works, I need to be more accurate with quotes in Hybris :)

[Former Member · Accepted solution]

I'm getting "no types registered yet" error for some reason using this method.. any ideas why?

[Former Member · Accepted solution]

Details of failed query >> https://answers.sap.com/questions/12764566/getting-no-types-registered-yet-with-sub-query-in.html

thanks!

[Former Member]

Yes, the subqueries are allowed in Flexible search queries. eg

select p_code from jobs where TypePkString = (select pk from composedtypes where InternalCodeLowerCase = 'TestCatalogversionSyncJob') and p_sourceversion = (select cv.PK from catalogversions as cv JOIN catalogs as cat on cv.p_catalog = cat.PK where cv.p_version = 'Staged' and cat.p_id = 'testProductCatalog')

[Former Member]

You are correct that subqueries are allowed but your example is pure SQL and not Flexible Search

[Former Member]

Yes for example:

 select count({code}),{C:id},{CV:version} from {Media as c}, {CatalogVersion as CV}, {Catalog as C} where {catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND ({C:id}='china' and {CV:version} like '%Test%') and {pk} IN ({{
 select {target} from {Product2MediaRelation}
 }})

[former_member1364266]

Thanks for your example.

[former_member1364266]

I am trying to create a report, for this I need to write query with sub-query in from clause like it supports native SQL for example:

  SELECT
    p.ProductID,
    p.Name AS ProductName,
    p.ProductSubcategoryID AS SubcategoryID,
    ps.Name AS SubcategoryName
  FROM
    Production.Product p INNER JOIN
    (
      SELECT ProductSubcategoryID, Name
      FROM Production.ProductSubcategory
      WHERE Name LIKE '%bikes%'
    ) AS ps
    ON p.ProductSubcategoryID = ps.ProductSubcategoryID;

Does Flexible Search support sub-query in FROM clause?

[Former Member]

What exactly are you trying to do?