Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

subquery with 3 schemas is not returning data

RaulOcana
Level 4
Level 4

Hi guys,

I have built this query to join 3 schemas, but when executing is not returning any data, if I use only one sub-query it does work, but adding a second schema to join, it fails.

var query = xtk.queryDef.create(

  <queryDef schema="search:airport" operation="select">

  <where>

      <condition expr="@id_city" setOperator="IN">

                  <subQuery schema="search:city">

                  <select>

                  <node expr="[@id_city]"/>

                  </select>

                  </subQuery>

       </condition>

       <condition expr="@id_country" setOperator="IN">

                  <subQuery schema="search:country">

                  <select>

                  <node expr="[@id_country]"/>

                  </select>

                  </subQuery>

       </condition>

       <condition expr={"@iata_code='" + iata_code + "'"}/>

    </where>

  </queryDef>

);

query.SelectAll(false);

result = query.ExecuteQuery();

Regards,

Raúl

1 Accepted Solution
RaulOcana
Correct answer by
Level 4
Level 4

I ended out using a SQL expression and it worked.

var sql = "select ai.iId_aeropuerto, ai.iId_ciudad, ai.iId_pais, ai.sCodigo_iata, ai." + name + ", ci.sCodigo_iata, ci." + name + ", co.iId_region, co.sCodigo_iata, co." + name + " from SearchAirport ai join SearchCity ci on ai.iId_ciudad = ci.iId_ciudad join SearchCountry co on ci.iId_pais = co.iId_pais where ai.sCodigo_iata = '" + iataCode + "'";

var format = "resSQLResult, @idAirport:string, @idCity:string, @idCountry:string, @iataAirport:string, @nameAirport:string, @iataCity:string, @nameCity:string, @idRegion:string, @iataCountry:string, @nameCountry:string";

var sqlRes = sqlSelect(format, sql);

View solution in original post

2 Replies
wodnicki
Community Advisor
Community Advisor

Hi,

Does it work if you use setOperator="EXISTS"? You can reference the outer query with its xpath in the exists, e.g. expr="@id_country = [airport/@id_country]".

Thanks,

-Jon

RaulOcana
Correct answer by
Level 4
Level 4

I ended out using a SQL expression and it worked.

var sql = "select ai.iId_aeropuerto, ai.iId_ciudad, ai.iId_pais, ai.sCodigo_iata, ai." + name + ", ci.sCodigo_iata, ci." + name + ", co.iId_region, co.sCodigo_iata, co." + name + " from SearchAirport ai join SearchCity ci on ai.iId_ciudad = ci.iId_ciudad join SearchCountry co on ci.iId_pais = co.iId_pais where ai.sCodigo_iata = '" + iataCode + "'";

var format = "resSQLResult, @idAirport:string, @idCity:string, @idCountry:string, @iataAirport:string, @nameAirport:string, @iataCity:string, @nameCity:string, @idRegion:string, @iataCountry:string, @nameCountry:string";

var sqlRes = sqlSelect(format, sql);

View solution in original post