SQL results in Studio 14.2 don't match results from SQL Assistant 13.10

Teradata Studio
Enthusiast

SQL results in Studio 14.2 don't match results from SQL Assistant 13.10

When I execute the code below in Teradata Studio 14.2, I get 0 rows returned.  When I execute it in SQL Assistant 13.10, I get 129 rows, which is correct.  The connections for both applications point to the same destination.  The database is version 13.10.

What have I missed in my setup of Studio?

    SELECT P. DbName sourceDB, P. TblName sourcetbl, DatabaseName referenceDB , TVMName referencetbl, TableKind AS "Type"
    FROM
    dbc . TVM T,
    dbc. dbase D,
    ( Select trim ( Databasename) DbName, trim( Tablename) TblName from dbc. tables where databasename in ( 'FPDW_core') ) P
    WHERE D .DatabaseId = T. DatabaseId
    AND CreateText LIKE '%"' || P. DbName || '"."' || P. TblName || '"%' ( NOT CS)
    AND D. databasenameI= 'FPDW_BVAL'
   
    UNION
   
    SELECT P .DbName , P. TblName, DatabaseName, TVMName, TableKind AS "Type"
    FROM
    dbc . TextTbl X,
    dbc. dbase D,
    dbc. TVM T,
    ( Select trim ( Databasename) DbName, trim( Tablename) TblName from dbc. tables where databasename in ( 'FPDW_core') ) P
    WHERE X .TextType = 'C'
    AND X. TextString LIKE '%"' || P. DbName || '"."' || P. TblName || '"%' ( NOT CS)
    AND X. DatabaseId= D .DatabaseId
    AND X. TextId= T. TVMId
    AND D. databasenameI= 'FPDW_BVAL'
   
    UNION
   
    SELECT P .DbName , P. TblName, ChildDB, ChildTable, 'T'
    FROM
    dbc. RI_Distinct_Children,
    ( Select trim ( Databasename) DbName, trim( Tablename) TblName from dbc. tables where databasename in ( 'FPDW_core') ) P
    WHERE ParentDB = P. DbName
    AND ParentTable= P .TblName
;   
3 REPLIES
Senior Apprentice

Re: SQL results in Studio 14.2 don't match results from SQL Assistant 13.10

Studio's uses ANSI sessions as default while SQLA uses the system's default (and this is usually Teradata mode):

In ANSI mode any string literal is case specific, so  "databasename in ( 'FPDW_core')" might be the cause.

You set the transaction mode in the connection string: TMODE=ANSI/TERA/DEFAULT

Dieter

Teradata Employee

Re: SQL results in Studio 14.2 don't match results from SQL Assistant 13.10

Studio will default the TMODE to the recommended value of ANSI. When in ANSI mode, the character comparisons are always CASESPECIFIC. You may also get truncation of trailing non-blank characters which may cause errors. To change the connection profile to TERA mode, right click on the connection profile and choose the Properties option, then Teradata JDBC Connection Properties. In the list of JDBC Connection Properties, choose Add... and change the TMODE from ANSI to TERA. Click OK and reconnection the profile.

Enthusiast

Re: SQL results in Studio 14.2 don't match results from SQL Assistant 13.10

Sorry for the deelay getting back to you, but I thought that I set myself up to be notified if I received responses.

I got the TMODE reset, and it looks good now.  I knew it was something simple that I simply hadn't touched before.

Thanks, both of you.

Robin