#DateForumTypeThreadPost
227825 Sep 2013 @ 08:46 PDTConnectivityReplyNewbie: Connecting Teradata, Access Sample Databe, Creating Users.IIRC there was some accompanying stuff with info about the the existing users and databases, if this is the 32-bit windows version (not the VMWare) there's a HELP file, too. And there were...
227725 Sep 2013 @ 08:37 PDTDatabaseReplyUpdate frequency of CurrentSpool in dbc.DiskSpaceHi Rob, the "update spool space ..." must be submitted from the updatespace utility not from a SQL client. You need access to the "Database Window" to do that.   Dieter
227625 Sep 2013 @ 08:31 PDTDatabaseReplyCorrelated SubQuery needs Tuning.. INDEX and LENGTH functions causing problem... PS: I can’t use Cross join !! Well, you don't call it a Cross Join, but it is a cross join, when you explain it you'll see a PRODUCT join. No matter how you rewrite it usi...
227525 Sep 2013 @ 08:25 PDTAnalyticsReplyNeed to select Date range from Table A which does not fall under Date range of Table BHi Mohan, try this SELECT * FROM tableA AS A WHERE NOT EXISTS ( SELECT * FROM tableB AS B WHERE A.PK=B.PK AND (B.Start_Date, B.End_Date) OVERLAPS (A.Start_Date, a.End_Date) ...
227421 Sep 2013 @ 06:02 PDTDatabaseReplyI'm trying to automate a process in Teradata, if the count is greater than 5 then do not execute the below statementWhich tool do you use to submit it? In BTEQ and (SQL Assistant 13.10+) you can use basic conditional logic: select count (*) from tableX having count(*) > 5; -- no row returned when count(...
227320 Sep 2013 @ 02:07 PDTDatabaseReplyGet Most Current DateThe easiest way is to add a RANK: select ca8172.* ,ord.order_due_dt as ORD_DUE_DT ,ord.cust_reason_txt as RSN_TXT ,ord.order_reason_cd as ORD_RSN_CD from ORDER_ACTION ord, ca8172_Ac...
227220 Sep 2013 @ 10:46 PDTDatabaseReplyVolatile Table In View ??#1: Those MAX/MIN queries can usually be rewritten using RANK/ROW_NUMBER: SELECT * FROM _person QUALIFY RANK() OVER (PARTITION BY person_id, person_key ORDER BY date_time DESC...
227120 Sep 2013 @ 10:34 PDTDatabaseReplyUpdate frequency of CurrentSpool in dbc.DiskSpaceThe spool is released, i.e. not using space, but it's reducing the user's available spool because the size is not correct. In fact there's another source for wrong CurrentSpace, Orphan...
227020 Sep 2013 @ 07:33 PDTDatabaseReplyPlease help me on BT/ET and ROLLBACK/ABORT  You should commit a transaction only when you're shure you don't need a rollback :-) There's an ET before the ROLLBACK.   Dieter
226920 Sep 2013 @ 06:48 PDTDatabaseReplyUpdate frequency of CurrentSpool in dbc.DiskSpaceHi Rob, It's not "real time" but "whenever necccessary". When you explain your query you'll notice: explain sel * from dbc.DiskSpace; *** Help information return...
226820 Sep 2013 @ 06:46 PDTDatabaseReplyHow can I load special character A^ ie A cap in teradata?Do you mean 'Â'? That's a valid characer in Teradata's LATIN character set, "0xC2, LATIN CAPITAL LETTER A WITH CIRCUMFLEX". What extra character? In UNICODE t...
226719 Sep 2013 @ 11:36 PDTDatabaseReplyHelp calculating an average Start Time?I'm afraid you can't do an average on a TIME :-( But it's not that complicated :-) TIME '00:00:00' + CAST(AVG(EXTRACT(HOUR FROM starttime) * 3600 + EX...
226619 Sep 2013 @ 11:28 PDTGeneralReplyCREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWSDid you submit it using SQL Assistant?  SQLA displays an activitycount (which is ment to be used for DML statements) for DDL statements, too. It's usually meaningless and in your cas...
226519 Sep 2013 @ 09:49 PDTDatabaseReplyCreate table as select counts vs select counts different?Probably: The select returned duplicate rows and your table is created as SET table, do a SHOW TABLE MKTNG_PGM_NBR; If you actually need the duplicates you can override it:CREATE MULTISET TAB...
226419 Sep 2013 @ 07:34 PDTDatabaseReplyImitating procedural language using SQL  Hi Abin, you can probably do that as OLAP functions allow access to the "previous" record, e.g. min(Action) over (partition by customer order by entry_date ...
226319 Sep 2013 @ 06:19 PDTGeneralReplyReplace blank('') as NULL in Stored ProcedureHi Selvi, if "Parameter" is NULL or '' then return NULL rows else do the LIKE comparison? WHERE (Parameter LIKE '%'|| TRIM(Column1) || '%') OR (NULLIF(Para...
226219 Sep 2013 @ 04:41 PDTGeneralReplyReplace blank('') as NULL in Stored ProcedureWhen Column1 is NULL your calculation is: WHERE NULL LIKE NULL and this is NOT TRUE. What do you want as result when one or both are NULLs?   Dieter
226119 Sep 2013 @ 03:44 PDTGeneralReplyHow would you access the table when simultanous loading is going on If both columns have a datatype DATE you don't have to do anything, the FORMAT is only for display purposes and CASTs from and to CHAR.   Otherwise could you please do what i already wro...
226019 Sep 2013 @ 03:20 PDTGeneralReplyGetting "x" in Teradata SQL Assistant but x in BTEQHi Amit, strange, there's an option to double quote strings in SQLA, but this is only for exporting data. Can you add a CHAR_LENGTH(cntnt_desc) to see if this is returned by the server or add...
225919 Sep 2013 @ 12:16 PDTGeneralReplyGetting "x" in Teradata SQL Assistant but x in BTEQHi Amit, what's the query you submit?   Dieter
225819 Sep 2013 @ 12:14 PDTDatabaseReplyUse of CTE within stored procedures SQL Data Manipulation Language   Chapter 1: The SELECT Statement WITH [RECURSIVE] Request Modifier Rules and Restrictions for the WITH and WITH RECURSIVE Request ...
225718 Sep 2013 @ 01:43 PDTDatabaseReplyOLAP not allowed in SubqueryWell, the 2nd QUALIFY is definitely not part of the subquery :-)   Dieter
225618 Sep 2013 @ 12:47 PDTDatabaseReplyselect failed 3504 selected non-aggregate values must be part of the associated groupIt depends on the expected result set. You might have to put all other columns in your select list in a GROUP BY 1,3,4,5,6,7,8,9 or you might need a MAX(b.Effective_Dt) OVER (PARTITION BY whatever...
225518 Sep 2013 @ 10:40 PDTGeneralReplyReplace blank('') as NULL in Stored ProcedureYou can use the basic SQL NULLIF(parameter, '')   Dieter
225418 Sep 2013 @ 04:00 PDTData ModelingReplyHistory maintenanceHi Raja, a SCD Type 2with TIMESTAMPs  is similar to uni-temporal using TRANSACTIONTIME. But bi-temporal consumes more diskspace because you need an addtitional PERIOD for the VALIDTIME :-) ...

Pages