9 Replies Latest reply on Aug 28, 2015 6:40 PM by rareddy

    Teiid CONVERT vs CAST

    vsathishkumaran

      Hello

       

      Wondering why TEIID uses CONVERT function instead of CAST function in the SQL's. I am trying to run the SQL statements from the plan XML in the database and I have encountered with this error. Are there are any ways that we can force TEIID to use CAST function instead of CONVERT function. I believe most databases uses CAST for typecasting as against CONVERT.

       

       

      Thanks

       

      Sathish

        • 1. Re: Teiid CONVERT vs CAST
          shawkins

          > Wondering why TEIID uses CONVERT function instead of CAST function in the SQL's.

           

          We support both in Teiid SQL.  Convert as the default internal construct came from ODBC standard functions. 

           

          > Are there are any ways that we can force TEIID to use CAST function instead of CONVERT function


          What is your scenario?  All of the built-in translators already handle translating the function to whatever is supported by the source.

          • 2. Re: Teiid CONVERT vs CAST
            vsathishkumaran

            > Wondering why TEIID uses CONVERT function instead of CAST function in the SQL's.

             

            We support both in Teiid SQL.  Convert as the default internal construct came from ODBC standard functions.

             

            >>>> Can you please advice how to tune TEIID to use CAST instead of CONVERT?

             

            What is your scenario?  All of the built-in translators already handle translating the function to whatever is supported by the source.

            >>>>We are trying out to reconstruct SQL's from plan XML to execute directly in the custom process.

             

             

            -Sathish

            • 3. Re: Teiid CONVERT vs CAST
              vsathishkumaran

              Can someone please help me here?

              • 4. Re: Teiid CONVERT vs CAST
                rareddy

                Steven already replied to you, what other help are you looking for?

                 

                If you are looking for tune Teiid to use CAST instead of CONVERT, we need to know the context what you are talking about, so please detail your question further for any responses.

                • 5. Re: Teiid CONVERT vs CAST
                  shawkins

                  You need to tell us where exactly in Teiid you are seeing a problem with cast vs convert.  If you are for example experiencing an exception, please include that.

                   

                  Otherwise I am saying Teiid user query sql supports both:

                   

                  convert(x, string)

                  cast(x as string)

                   

                  and that it's expected that pushdown sql containing the convert function will be translated to whatever is supported by the source.

                  • 6. Re: Teiid CONVERT vs CAST
                    vsathishkumaran

                    Hello

                     

                    I am trying to run the SQL manually taken from plan XML (Access nodes) and run directly in database SQL interface for SQL performance analysis. While I was doing that I noticed teiid uses CONVERT function instead of CAST. Each time we have to manually change the function while executing directly in database. Hence, if there is a way to tweak the Teiid to generate SQL with native database functions it would be helpful

                     

                    Thanks

                     

                    Sathish

                    • 7. Re: Teiid CONVERT vs CAST
                      rareddy

                      Satish,

                       

                      The SQL statement you see in the plan is *exactly* same as the one sent to the source, in your case SQL Server. The SQL statement in the plan is what sent to the translator, which is representative of the source query, however translator can further rewrite the query that gets sent to the source. This is where CONVERT gets converted to CAST.

                       

                      If you want to see what is being sent to the source, then turn on "DEBUG" log, then look for log message in "CTX_CONNECTOR" context with prefix like "Source-specific command:"

                       

                      Ramesh..

                      • 8. Re: Teiid CONVERT vs CAST
                        vsathishkumaran

                        Hi Ramesh

                         

                        Not sure how to enable DEBUG logging through Teiid Embedded. I was using SET SHOWDEBUG ON but couldn't see CTX_CONNECTOR specific logs.

                         

                         

                        Thanks

                         

                        Sathish

                        • 9. Re: Teiid CONVERT vs CAST
                          rareddy

                          How have you configured the logging? If you have not done anything, the logging currently uses "java.util.logger", so see this how to configure the contexts for it. You can change this to Log4J etc, for that see Embedded Guide - Teiid 8.12 (draft) - Project Documentation Editor

                           

                          BTW, the SET SHOW DEBUG show the DEBUG query plan, not logging that is occurring in the system. Is there a strong reason you are using embedded, instead of Teiid Server, this would have been pre-configured for you.

                           

                          Ramesh..