SQL Replace not working in Query JSON with SQL

Queries and Resources
1

Hi,
I have a Query JSON with SQL that should compare two different Resources:

select * from {{ Routen.data }} where {{ Routen.data.product_route }} not in @({{ Angebotstypen.data.szVariantenMatchCode }})

But the Routen.data.product_route looks like 7561.5226.101 and the Angebotstypen.data.szVariantenMatchCode like 7550-5226-101. How can I compare just the numbers without the delimiters? I tried to use SQL REPLACE for both of them but it did not work. Thanks for your help.

Best,
Thomas

2

Try
select * from {{ Routen.data }} where {{ Routen.data.product_route }} != ANY({{ Angebotstypen.data.szVariantenMatchCode }})

3

In addition to trying the != ANY clause, I think you need to split and join both sets to get the comparison you are looking for:

{{Routen.data.product_route.split(".").join()}}

{{Angebotstypen.data.szVariantenMatchCode.split("-").join()}}
4

Hey @thim-jbm! Happy to help here. @ScottR @pyrrho Thanks for the assist!

Not 100% sure what your data looks like, but tried to replicate it. A few syntax changes and some split/join action and got it to work!

For these 2 data sets

dataSet1
dataSet11698×342 30.4 KB

dataSet2
dataSet21394×276 16.7 KB

Was able to grab the 3 items from dataSet1 which were not in dataSet2 with this query

success
success2000×870 60.3 KB

  • You should just be able to reference the object property in the where clause (ie: product_route instead of Routen.data.product_route).

  • The use of NOT IN is fine here.

  • I just chose to modify 1 of the datasets to match the other (remove the '-' and add a '.') but you could remove the delimiters in both.

Let me know if this works for you!

1 Like