All Forums Teradata Applications
smart351 4 posts Joined 03/13
27 Mar 2013
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!!

dnoeth 4628 posts Joined 11/04
27 Mar 2013

What datatype is START_Y?
If it's already a DATE: START_Y <= DATE '2012-03-26'
Dieter

Dieter

smart351 4 posts Joined 03/13
28 Mar 2013

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!

dnoeth 4628 posts Joined 11/04
28 Mar 2013

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

Dieter

smart351 4 posts Joined 03/13
28 Mar 2013

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?

dnoeth 4628 posts Joined 11/04
28 Mar 2013

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

Dieter

tomnolan 594 posts Joined 01/08
28 Mar 2013

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'
 

smart351 4 posts Joined 03/13
28 Mar 2013

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!

nhoigal 5 posts Joined 05/15
07 Mar 2016

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 :-)

dnoeth 4628 posts Joined 11/04
08 Mar 2016

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...

Dieter

nhoigal 5 posts Joined 05/15
09 Mar 2016

Hi Dieter,
 
The base query (without the case), shows a list of Merchants.
With the CASE I'm trying to find the merchant's name in another list, to see if he is currently active.
The CASE is a sort of lookup using count, with a result of TRUE if the merchant is Live at the moment, or FALSE if merchant is inactive.
 
Here is the code in Teradata without the CASE
 

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"

 

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
 

dnoeth 4628 posts Joined 11/04
09 Mar 2016

If "active" is based on "inv.load_date > (CURRENT_DATE -2)" then you already retrun only active merchants. You might remove this conditon from WHERE and use:
 

,Case when inv.load_date > CURRENT_DATE -2
      then 'TRUE' 
      else 'FALSE' 
 end as "Is_Live?"

Or maybe you need somthing like this, check if any row for a given merchant quaifies as "active"

,Case when max(inv.load_date)
           over (partition by vm.merchant_name_sf)  > CURRENT_DATE - 
      then 'TRUE' 
      else 'FALSE' 
 end as "Is_Live?"

 

Dieter

nhoigal 5 posts Joined 05/15
09 Mar 2016

Thanks, exactly what I was looking for :-)

You must sign in to leave a comment.