Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to compare 2 columns in a table containing customer information: Zip Code and City. First example you learn in training...
In the "Left Columns" I choose Zip Code and City, in the "Right Columns" I have chosen City and Zip Code... from the same table. No data filter. (Using MS SQL Server as a database)
I get an error when trying to Analyse: "Query not executed for indicator: 'Functional Dependency' SQL query: select count(*) as nb, count(distinct a) as nbdistinct from (select distinct postalcity as a, postalcode as b from customer_tb c) t"
The query works fine for me when executed directly in db, so what's wrong in Talend?
Any ideas?
/G
Ok, will do.
I now did a "simple" test against both the Northwind and pubs databases which are shipped with MS SQL Server. I wanted to test zip and city columns with a functional dependency analysis on a database/table that I didn't create myself
I got some other error now and I just want to post it so that maybe somebody can point out if I'm doing something obviously wrong.
1. Created a connection to my local ms sql server 2000 using sa account and db type = ms sql server. (Additional JDBC params: zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8 and Url: jdbc:jtds:sqlserver://localhost:1433;DatabaseName=;zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8)
2. Create Table Functional Dependency Analysis using above created connection on database Northwind and table Customers (same test done with pubs database, table stores (columns zip city))
3. Choose City and PostalCode as "Left Columns" and PostalCode and City as "Right Columns"
4. Run the analysis
Results in an error message:
Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T
I now did a "simple" test against both the Northwind and pubs databases which are shipped with MS SQL Server. I wanted to test zip and city columns with a functional dependency analysis on a database/table that I didn't create myself
I got some other error now and I just want to post it so that maybe somebody can point out if I'm doing something obviously wrong.
1. Created a connection to my local ms sql server 2000 using sa account and db type = ms sql server. (Additional JDBC params: zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8 and Url: jdbc:jtds:sqlserver://localhost:1433;DatabaseName=;zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8)
2. Create Table Functional Dependency Analysis using above created connection on database Northwind and table Customers (same test done with pubs database, table stores (columns zip city))
3. Choose City and PostalCode as "Left Columns" and PostalCode and City as "Right Columns"
4. Run the analysis
Results in an error message:
Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T
!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:00.549
!MESSAGE 2010-03-01 14:29:00,549 INFO org.talend.dataprofiler.core.ui.editor.analysis.ColumnDependencyMasterDetailsPage - Success to save connection analysis:/PROJECT_CSC/TDQ_Data Profiling/Analyses/dGVzdA==20100301022753_0.1.ana
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.659
!MESSAGE 2010-03-01 14:29:09,643 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Problem when changing trying to set catalog "dbo" on connection. SQLException message: Could not locate entry in sysdatabases for database 'dbo'. No entry found with that name. Make sure that the name is entered correctly.
!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:09.690
!MESSAGE 2010-03-01 14:29:09,659 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "City" AS A , "PostalCode" AS B FROM "Customers" C ) T
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.737
!MESSAGE 2010-03-01 14:29:09,721 ERROR org.talend.dq.analysis.FunctionalDependencyExecutor - java.sql.SQLException: Invalid object name 'Customers'.
!STACK 0
java.sql.SQLException: Invalid object name 'Customers'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.831
!MESSAGE 2010-03-01 14:29:09,768 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "City" AS A , "PostalCode" AS B FROM "Customers" C ) T
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.940
!MESSAGE 2010-03-01 14:29:09,924 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Problem when changing trying to set catalog "dbo" on connection. SQLException message: Could not locate entry in sysdatabases for database 'dbo'. No entry found with that name. Make sure that the name is entered correctly.
!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:10.112
!MESSAGE 2010-03-01 14:29:09,956 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:10.315
!MESSAGE 2010-03-01 14:29:10,143 ERROR org.talend.dq.analysis.FunctionalDependencyExecutor - java.sql.SQLException: Invalid object name 'Customers'.
!STACK 0
java.sql.SQLException: Invalid object name 'Customers'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:10.487
!MESSAGE 2010-03-01 14:29:10,346 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T
!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:10.502
!MESSAGE 2010-03-01 14:29:10,502 INFO org.talend.dataprofiler.core.ui.action.actions.RunAnalysisAction - Analysis "test" execution code: KO: Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T. Duration: 0.88 s.
Update!
I was using TDQ version 3.2.2. I have now updated and I am using TOP version 3.2.3. And all my tests works, both using my own dbs and also microsofts. Sorry for wasting your time!
(I must make sure to use the latest versions available and not be too quick to call for help
)
/G
I was using TDQ version 3.2.2. I have now updated and I am using TOP version 3.2.3. And all my tests works, both using my own dbs and also microsofts. Sorry for wasting your time!
(I must make sure to use the latest versions available and not be too quick to call for help
)
/G