[resolved] ORA-01747: invalid user.table.column, table.column, or column specific

[Anonymous]

hello,
im trying to load data to my fact table from the different dimensions .
i have create one connection to my DataBase, all the tables(dimensions and fact) are under the same connection.
But when i execute the job, i have this error: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification.
Can anyone told me whats the problem please.

[Anonymous]

Hi,
Can you show the input query used in the component? probably query is not valid, have you tried executing your query in query browser? may be you can remove unwanted prefixes inside query for each column and database table name.
Vaibhav

[Anonymous]

This is the querry , when i run i got the same error of course.
i can not execute the querry in toad for oracle 

[Anonymous]

Well Thank you so much for your reply, you gave me th idea of running the querry elsewhere, i found the problem.
I have juste change the name of the field "Date" which is i think a reserved name and it works now

[Anonymous · Accepted solution]

your solution is the best way. Avoid reserved words is the best what you can do.
In cases where you are not able to change them you can set these identifiers in double comma.
e.g. TEMPS."DATE" would also solve your problem. This solution has a backdraft: Identifiers written this way are case sensitive, all others not.

[Anonymous]

thank you for your reply
I have corrected that error but now i have another problem , when i execute the job the data loaded to my FACT table are redundant and milliolns of lines.
For exemple, the dimension "AUTEUR" contain 15 lines in my FACT table those 15 lines are repeated hundred of times, the same for all dimensions.
i think in this case the tmap component in not the best way to load data ???
The screenshots show Data in dimension "Auteur" and FACT table

[Anonymous]

Hi,
What type of joins have you implemented in tMap for source and lookup tables (Inner/Left outer joins)
If you have No joins, then it will have cross join resulting into too many records inserted into the output.
if you have left outer join then it will insert all the records
Verify your design, have proper joins for each lookup table and test again. 
Can you Pl show your tMap design?
Vaibhav

[Anonymous]

hello, thank you for your reply.
Well , i don't have any joins in my tmap because i don't understand what can i join ?
i have different dimensions and i want to load their ID in the FACT table.
Another question : in dimensionnel model should the number of lines of dimensions be the same ???

[Anonymous]

Hi,
this is the reason you have cross join and and getting many rows in target table... few points here
- you have main flow in which data is flowing...
- you need to identify the column related to respective dimension table and join with inner join and drag respective id to output 
- do same for rest of all dimension tables
- if you can't identify a join with the main flow, then you can break the process
 Learn more about tmap and its working as well as keys in the fact and dimension tables...
https://help.talend.com/search/all?query=tMap&content-lang=en
http://www.talendforge.org/tutorials/tutorial.php?idTuto=9
http://www.talendbyexample.com/talend-tmap-component-joins.html
Go through these links, this will help you to understand the concept of joins using tmap and relate it with your data.
thanks
vaibhav

[Anonymous]

thank you so much for your help.
I will see the links.
Best regards.