Teradata Syntax Trouble

Teradata Applications

Teradata Syntax Trouble

Hello!  I am using Teradata release 13.00.01.03. I am having a terrible time with some syntax. I was hoping someone could help?

Select Field 1, Field2, Field3

from EDWDEV_VIEW.Table1

Where  CURRENCY= 'EUR'  AND

EXTRACT(YEAR from (EFF_OUT_Y))=9999 AND

CAST(START_Y (DATE,FORMAT 'YYYY-MM-DD')) AS DATE) <= CAST('2013-03-26' (DATE,FORMAT 'YYYY-MM-DD')) AS DATE)

My problem is with the syntax on the last line. I want to find where START_Y is less than or equal to the date provided ('2013-03-26'). When I run the above, it will return results that are NOT less than or equal to.

Any thoughts??

Thank you!!

12 REPLIES
Senior Apprentice

Re: Teradata Syntax Trouble

What datatype is START_Y?

If it's already a DATE: START_Y <= DATE '2012-03-26'

Dieter

Re: Teradata Syntax Trouble

Oh thank hyou for your response! I didn't get an email so I didn't think I had one. :)

START_Y is DATE. It displays as 2012-03-26. However, the syntax above (START_Y <= DATE '2012-03-26') has been tried and fails. :( It works in SQL Assistant, but I am querying from IBM BPMv8 tool directly against the Teradata database through a JDBC connection and that syntax does not work. From what I have found in my research/testing, syntax between SQL Assistant against a Teradata DB and syntax directly toward the Teradata DB (BTEQ) does not match. Below is an example of it not matching:

This code works in SQL Assistant:

Select EDWC076_RATE_TYPE_N

From EDWDEV_VIEW.EDWC075_USD_EXCH_RATE_VW

Where EDWC076_RATE_TYPE_N like 'TREND%' and YEAR(EDWC075_EFF_OUT_Y)=2013

However if you run it directly against Teradata (BTEQ), it gives me that Connection Closed Error. So I researched and found that the correct syntax had to be changed to:

  Select EDWC076_RATE_TYPE_N

  From EDWDEV_VIEW.EDWC075_USD_EXCH_RATE_VWWhere EDWC076_RATE_TYPE_N like 'TREND%' and EXTRACT(YEAR from (EDWC075_EFF_OUT_Y))=2013

Notice the difference between using YEAR() in SQL Assistant and having to use EXTRACT(YEAR from ()) when going against the DB using BTEQ.

Thank you!

Senior Apprentice

Re: Teradata Syntax Trouble

YEAR is an ODBC function which doesn't exist in StandardSQL/Teradata, but the ODBC driver replaces it with correct syntax.

This is always causing confusion, because it's ony working in SQLA and only for DML statements, when you try to put in in a view it will fail. In the ODBC manual you'll find a list of those ODBC functions, e.g. LENGTH, LTRIM, MONTH, etc.

This odd behaviour can be switched off in the ODBC options, just check the "Disable Parsing".

The DATE '2012-03-26' is the only syntax which is supposed to work in any place anytime, you should check if it's actually failing. Is there an error message or what?

Dieter

Re: Teradata Syntax Trouble

There is. Here is my full code which works:

Select RATE_TYPE_N, CURRENCY_ALPHA_C, USD_EXCH_RATE_START_Y, USD_EXCHANGE_RATE_Q, EXPIRE_Y, EFF_OUT_Y

from TERAVIEW.EXCH_RATE_VW

Where  CURRENCY_ALPHA_C = 'EUR'  AND

RATE_TYPE_N like 'TREND%' and

EXTRACT(YEAR from (EFF_OUT_Y))=9999

As soon as I add the last line I need:

Select RATE_TYPE_N, CURRENCY_ALPHA_C, USD_EXCH_RATE_START_Y, USD_EXCHANGE_RATE_Q, EXPIRE_Y, EFF_OUT_Y

from TERAVIEW.EXCH_RATE_VW

Where  CURRENCY_ALPHA_C = 'EUR'  AND

RATE_TYPE_N like 'TREND%' and

EXTRACT(YEAR from (EFF_OUT_Y))=9999 AND

CAST(EXCH_RATE_START_Y as date FORMAT 'YYYY-MM-DD') < CAST('2013-03-26' as date FORMAT 'YYYY-MM-DD')

I receive this error:  DSRA9110E: Connection is closed.

 I did just ask our Admin whom setup the jdbc connection if there was some kind of BTEQ Mode configuration that could be shut off. So perhaps I need to ask him to Disable Parsing? That should resolve?

Senior Apprentice

Re: Teradata Syntax Trouble

There's no BTEQ mode :-)

Using Teradata's own JDBC tool "Teradata Studio" everything's ok, seems to be an issue with your tool.

You might try writing a java date literal instead of DATE '2013-02-26', maybe this works: {d '2013-03-26'}

I'm not a Java guy, if it's still not working you should post to the Connectivity forum :-)

Dieter

Teradata Employee

Re: Teradata Syntax Trouble

Dieter is correct that the Teradata JDBC Driver does not have a "BTEQ mode".

What is the data type of the EXCH_RATE_START_Y column?

If the EXCH_RATE_START_Y column is a DATE already, then there is no need to CAST it to a DATE, and your query's last line should be:

EXCH_RATE_START_Y < DATE '2013-03-26'

Re: Teradata Syntax Trouble

Thank you Dieter. I am trying to get our admin that setup the JDBC to look as well. It's just bizarre.

Tomnolan, it is date. And even with the query you and Dieter suggested, I still get that wacky error. But I agree it's not a Teradata SYntax issue... it's something more connectivity related. BUmmer.

Thank you guys!

Enthusiast

Re: Teradata Syntax Trouble

Hi all,

I would appriciate your help with the following code:

SELECT vm.merchant_sf_id, vm.country_id,  vm.merchant_name_sf,
vm.resp_employee_id, vm.main_category,
vm.company_legal_name,
emp.complete_name as "Owner_Name"
,(Case when (count (vm.merchant_name_sf)in (select merchant_name from sandbox.IL_inventory where inv.load_date > CURRENT_DATE -2 )) >= 1 then 'TRUE' else 'FALSE' end) as "Is_Live?"
FROM dwh_base_sec_view.v_merchants_sf vm

join dwh_base_sec_view.v_employees as emp on emp.employee_id = vm.resp_employee_id
inner join sandbox.IL_inventory as inv on inv.merchant_name = vm.merchant_name_sf and inv.load_date > (CURRENT_DATE -2)
where vm.country_id = 109

I get an error on the case line, for the operator >=

Any ideas?

Thanks :-)

Senior Apprentice

Re: Teradata Syntax Trouble

Well, that's simply no valid SQL, I don't get what you're trying to do :-)

Show the working query without that CASE and explain what you're trying to do...