[3706] Syntax error with LIMIT

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

[3706] Syntax error with LIMIT

I'm getting an error executing the following sql.  The inner select works fine, but when I try to use the limit keyword, I get a syntax error.  Removing the LIMIT keyword is not an option.  Is there something in my inner select that would cause LIMIT not to work?

SELECT count(*) AS "COUNT" FROM

(select  case when ip.active_ind is null or ip.active_ind = 'N' then 'false' else 'true' end active

, ip.base_case_amt base_case_amount, cg.itm_ctgry_desc category

, im.itm_ctgry_cd category_code , co.cmdty_desc commodity

, im.inv_itm_nb cs_item_code , im.itm_id customer_item_code

, cu.customer_number  , d.dept_desc department , im.itm_desc description

, case when im.orgnc_cd is null or im.orgnc_cd = 'N' then 'false' else 'true' end organic

, im.shp_pck_ct pack, im.vndr_num_of_tiers_on_a_plt * im.vndr_num_of_cases_on_a_tier pallet_quantity

, case when im.prvt_lbl_id is null or im.prvt_lbl_id  = 'N' then 'false' else 'true' end private_label

, case when im.seas_and_hldy_ind is null or im.seas_and_hldy_ind  = 'N' then 'false' else 'true' end seasonal

, case when im.shpr_ind is null or im.shpr_ind  = 'N' then 'false' else 'true' end shipper  

, im.itm_size size , im.upc_itm upc , im.itm_uom unit_of_measure

, CAST(CAST(im.updt_ts AS CHAR(19)) AS TIMESTAMP(0)) AS update_timestamp

, v.vend_nm vendor, im.whse_nb warehouse_number, ip.base_unit_amt unit_cost_amount  

, ip.reg_rtl_price regular_retail_amount

from  mystore.lu_mil_item im

join  mystore.lu_item_category cg on im.itm_ctgry_cd = cg.itm_ctgry_cd  

join  mystore.lu_vendor v on im.vndr_prty_id = v.vndr_prty_id  

join   mystore.str_itm_pricing ip on  im.itm_id = ip.itm_id    

join  (select distinct * from ( select cus.dw_cust_id , regexp_replace(trim(both ' ' from cast(case when cus.chn_nb = 99 then cus.deca_po_nb else cus.base_cust_nb end as varchar(12))), '[^0-9]', '', 1, 0, 'i')  customer_number

from  mystore.LU_CUSTOMER cus where cus.INACTIVE_IND = 0) cu1    

where cu1.customer_number is not null and cu1.customer_number <> '' and cu1.customer_number <> '0') cu

on ip.dw_cust_id = cu.dw_cust_id  

join  mystore.lu_item_category_ms_ctgry ic  on im.itm_ctgry_cd = ic.cns_ctgry_sk  

join  mystore.lu_mystore_ctgry mc  on ic.ctgry_sk = mc.ctgry_sk  

join  mystore.lu_ms_ctgry_ms_cmdty cco  on ic.ctgry_sk = cco.ctgry_sk

join  mystore.lu_mystore_cmdty co  on cco.cmdty_sk = co.cmdty_sk

join  mystore.lu_ms_cmdty_ms_dept cod  on cod.cmdty_sk = cco.cmdty_sk

join  mystore.lu_mystore_dept d  on d.dept_sk = cod.dept_sk    

where im.byng_sts_cd <> 'Z'  and ip.active_ind = 'Y'

and im.dlt_ind <> 2  

and im.end_efctv_dt is null and im.upc_itm is not null) AS "T1" LIMIT 1


Accepted Solutions
Junior Contributor

Re: [3706] Syntax error with LIMIT

Any software submitting SQL should know about different dialects, LIMIT is no Standard, see this wikipedia link.

 

As it's OS you might try to contact developers (or modify source)

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: [3706] Syntax error with LIMIT

There's no LIMIT in Teradata, you can use TOP instead.

But as your query returns a single row anyway, there's no reason to add LIMIT :)

 

 

Re: [3706] Syntax error with LIMIT

I don't have the option to change it.  My select is being wrapped that way by Logstash.

Junior Contributor

Re: [3706] Syntax error with LIMIT

Any software submitting SQL should know about different dialects, LIMIT is no Standard, see this wikipedia link.

 

As it's OS you might try to contact developers (or modify source)