Skip to Content
0
Jul 27, 2019 at 09:30 AM

Flexible search IN operator with UNION error?

312 Views

Hi Experts,

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

 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?

 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