Metadata repository queries for PostgreSQL problem

[_AnonymousUser]

Hi,
I am currently working on a project with uses PostgreSQL. I set up a connection in the metadata repository which works flawlessly. The query was the following:
"select \"dwh\".\"dim_category\".\"category_tk\",\"dwh\".\"dim_category\".\"category_name\",
\"dwh\".\"dim_category\".\"sub_category_name\"
from \"dwh\".\"dim_category\""
Note: I am using a database called test which has a schema called dwh.
I then went on to create a query in the repository which just worked fine in my main job.
I then went on and exported all the connection parameters as context group. I set up a new query then (enabling context mode) and it executed fine within the metadata query setup dialog. I then added this query as part of a PostgresqlInput component to my main job. The metadata query was added like this:
select "context.postgresql_dwh_Schema"."dim_category"."category_tk","context.postgresql_dwh_Schema"."dim_category"."category_name","context.postgresql_dwh_Schema"."dim_category"."sub_category_name"
from "context.postgresql_dwh_Schema"."dim_category"
I clicked on the Guess Schema button and got an error. I manually corrected the query for testing to this:
"select \"dim_category\".\"category_tk\"
from "+context.postgresql_dwh1_Schema+".\"dim_category\""
... and it worked.
I'd appreciate if someone could point out why when using the metadata query the whole query string with the variables doesn't get inserted properly.
I tried the same approach with MySQL and this just works fine.
Thanks,
Diddy

[Anonymous]

Forgot to mention:
OS: Ubuntu 11.10
TOS: 5.0.1

[Anonymous]

Hi Pedro,
Many thanks for your reply! I did a very basic test now (not using context variables and references to the metadata repository at all) and the problem still persists:
I have the schema name defined in the component settings.
If I write a query and don't mention the schema in front of the table name I get an error:

If I include the schema name in the query all works fine:

Can you please let me know what I am doing wrong?
Thanks,
Diddy

[Anonymous]

Hi Diddy
The reason is because you left "Table Name" empty...
You can follow Tutorials and find some scenarios in document.
Regards,
Pedro

[Anonymous]

I am afraid, but this didn't solve the problem:

BTW: What if I write a query that joins several tables ... I cannot define a table then in the component settings, or?

[Anonymous]

I had problems before with the metadata. So I wanted to see if it was really related to the metadata or not. So I decided to test the component without metadata reference. I'd prefer to understand first if it is a problem related to the component itself or the metadata.
As you can see from the screenshot, all settings are on "Built-In". I am still not understanding why this is not working. As my tests show, the component doesn't seem to use the Schema value provided in the settings in the query. I have the Schema defined as "dwh". The query only runs successfully if I reference the table like this: dwh.dim_category. If I just use dim_category, I get an error. This sounds like a bug to me.
Any hints would be appreciated.

[Anonymous]

Hi
Now back to the first post in this topic.

"context.postgresql_dwh_Schema"."dim_category"."category_tk","context.postgresql_dwh_Schema"."dim_category"."category_name","context.postgresql_dwh_Schema"."dim_category"."sub_category_name"
from "context.postgresql_dwh_Schema"."dim_category"

This query is wrong. Because context.postgresql_dwh_Schema is a variable. You need to use +context.postgresql_dwh_Schema+ to link it with the query String.

"select \"dim_category\".\"category_tk\"
from "+context.postgresql_dwh1_Schema+".\"dim_category\""

You use +context.postgresql_dwh1_Schema+ in this query. So it is correct.
This is not a bug.
The correct query should be...

"SELECT 
 \"Db column\", 
  \"Db column\"
FROM \"DBName\"."+context.SchemaName+".\"TableName\""

Regards,
Pedro

[Anonymous]

Hi Pedro,
This query:

"context.postgresql_dwh_Schema"."dim_category"."category_tk","context.postgresql_dwh_Schema"."dim_category"."category_name","context.postgresql_dwh_Schema"."dim_category"."sub_category_name"
from "context.postgresql_dwh_Schema"."dim_category"

Was the one I used from the metadata repository. I created this query with the SQL builder using the visual tool (I didn't write it by hand just to see how TOC would use the variables). It worked there in the repository, because I could preview the results.
TOC inserted the query this way into the job component. It looked wrong to me, so I also tried to change the query similar to what you outlined below and got it working. The problem then is that the query is built-in again, not a repository query.
Note: At some point I change the context group name from postgresql_dwh1 to postgresql_dwh (and all variable names as well). Sorry for the confusion ... but well spotted!

"select \"dim_category\".\"category_tk\"
from "+context.postgresql_dwh1_Schema+".\"dim_category\""

I am happy to prepare some examples which outline the issues, because I guess now it is getting a bit difficult to follow. Is there a way to upload the files here?
Thanks,
Diddy

[Anonymous]

I put together the examples. I zipped up the project folder, the DDL and the raw data:
https://docs.google.com/open?id=0B-yuO-Oixq4RYWMxYTRjZmUtNWZiMS00ZGQ4LTlhYWQtNWNhYTEzNTU1MWMy
Please read the notes in each job which should help to understand the problem.