CODEWITHSUNDEEP
sap-commerce-cloudflexible-search
Raj Raichand
Raj Raichand

Reputation: 97

How to create flexible search query with UNION & IN operator?

After executing below query i am getting UNION of all products from both catalogs "catalogA" & "catalogB" Query :

SELECT uniontable.PK, uniontable.creationtime AS TIMECREATED FROM
    (
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
       }}
       UNION ALL
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
       }}
    ) uniontable ORDERR BY TIMECREATED

I need products Only from CatalogA from UNION query so i did a subquery as below but it throws error. Any help would be appreciated?

Query 2 :

SELECT uniontable.PK, uniontable.creationtime AS TIMECREATED FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {cv:version}='Staged' AND {cat:id}='catalogA' IN 
    (
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
       }}
       UNION ALL
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
       }}
    ) uniontable ORDERR BY TIMECREATED

Upvotes: 0

Views: 1723

Answers (1)

HybrisHelp
HybrisHelp

Reputation: 5810

You just need to filter the temporary table with WHERE uniontable.catalog = 'catalogA'

Like

SELECT * FROM
(
   {{
      SELECT {p:pk},{p:mode},{p:brandno},{creationtime} AS creationtime, {cat:id} AS catalog 
      FROM {    Product AS p 
                JOIN catalogversion AS cv ON {p:catalogversion}={cv:pk} 
                JOIN catalog AS cat ON {p:catalog}={cat:pk}
            }
      WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
   }}
   UNION ALL
   {{
      SELECT {p:pk},{p:mode},{p:brandno},{creationtime} AS creationtime, {cat:id} AS catalog  
      FROM {    Product AS p 
                JOIN catalogversion AS cv ON {p:catalogversion}={cv:pk} 
                JOIN catalog AS cat ON {p:catalog}={cat:pk}
            }
      WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
   }}
) as uniontable WHERE uniontable.catalog = 'catalogA' ORDERR BY uniontable.creationtime

Upvotes: 0

Related Questions