Skip to main content
Solved

InlineQuerier query

  • September 5, 2018
  • 4 replies
  • 70 views
A
Forum|alt.badge.img+1

Hi

I am trying to use the InlineQuerier for the first time. I am using it instead of FeatureMerger because I want to do a join using a Like statement with wildcards which is not possible with FeatureMerger.

However I can't work out why my query is not running. I have done the same query within Oracle SQL Developer and it worked, but it is not working in InlineQuerier.

The query I've tried is below but I get a "Format and Dataset Unspecified" error when I try to run it.

SELECT b."POSTCODE",a."POSTCODE_AREA"

 

FROM "OS_POSTCODE_UNITS" b

 

INNER JOIN "TABLE1" a ON REPLACE (b."POSTCODE", ' ', '') LIKE CONCAT(REPLACE (a."POSTCODE_AREA", ' ', ''), '%');

Screenshot is also included.

I believe I have not set up the InlineQuerier properly in terms of the Input and Outputs, but I am struggling to understand what I am supposed to put here. Does anyone have a step-by-step example of how to use this transformer?

Regards,

Best answer by ebygomm

I don't think SQLite which the inlinequerier uses supports CONCAT try || instead

SELECT b."POSTCODE",a."POSTCODE_AREA"
FROM "OS_POSTCODE_UNITS" b
INNER JOIN "TABLE1" a ON REPLACE (b."POSTCODE", ' ', '') LIKE REPLACE (a."POSTCODE_AREA", ' ', '')||'%'

If you could post a sample of your source datasets someone may be able to advise further.

View original
Did this help you find an answer to your question?

4 replies

ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • Best Answer
  • September 5, 2018

I don't think SQLite which the inlinequerier uses supports CONCAT try || instead

SELECT b."POSTCODE",a."POSTCODE_AREA"
FROM "OS_POSTCODE_UNITS" b
INNER JOIN "TABLE1" a ON REPLACE (b."POSTCODE", ' ', '') LIKE REPLACE (a."POSTCODE_AREA", ' ', '')||'%'

If you could post a sample of your source datasets someone may be able to advise further.

A
Forum|alt.badge.img+1
  • aquamarine
  • Author
  • September 5, 2018
ebygomm wrote:

I don't think SQLite which the inlinequerier uses supports CONCAT try || instead

SELECT b."POSTCODE",a."POSTCODE_AREA"
FROM "OS_POSTCODE_UNITS" b
INNER JOIN "TABLE1" a ON REPLACE (b."POSTCODE", ' ', '') LIKE REPLACE (a."POSTCODE_AREA", ' ', '')||'%'

If you could post a sample of your source datasets someone may be able to advise further.

Thanks @egomm

 

 I have changed the code to use the || operator instead but I still get the same error.  I have attached a screen shot of the Inputs and Outputs.  Are these set up correctly?

 

 

inlinequerier2.jpg, 

 

 

ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • September 5, 2018
aquamarine wrote:
Thanks @egomm

 

I have changed the code to use the || operator instead but I still get the same error. I have attached a screen shot of the Inputs and Outputs. Are these set up correctly?

 

 

inlinequerier2.jpg,

 

 

Ah, that Run... button has never worked for me, I just run the workspace to see if it works
A
Forum|alt.badge.img+1
  • aquamarine
  • Author
  • September 5, 2018
ebygomm wrote:
Ah, that Run... button has never worked for me, I just run the workspace to see if it works
oh you are right! it does work even though the 'Run' button failed :) Thanks!

 

 

Reply


Most helpful members this week

Community Stats

30,872
Posts
117,040
Replies
38,692
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.