#DateForumTypeThreadPost
95306 Apr 2011 @ 01:36 PDTAnalyticsReplySELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.A huge CASE just looks ugly, but it's probably the most efficient way, so i'd stick with it for much more than 10 categories. I once wrote a batch which created a nested case with a few thousand v...
95206 Apr 2011 @ 01:21 PDTUDAReplyDifference in outputThe first select within a set operation determines a column's data type. The "NULL (VARCHAR(2000))" results in a LATIN character set: SELECT TYPE (NULL (VARCHAR(2000))), TYPE('' (VARCHAR(2000))...
95101 Apr 2011 @ 06:57 PDTAnalyticsReplySELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.Looks like you want the max of three columns. Your query is overly complex, you don't need any Scalar Subquery or Left Join to get the result set: SELECT id, CASE WHEN category_1 > cate...
95001 Apr 2011 @ 06:30 PDTGeneralReplyError code 3180 ?Hi Didier, also from the Messages manual: Remedy: Contact your Support Representative Open an incident Dieter
94930 Mar 2011 @ 11:06 PDTDatabaseReplycounter that resets at each new customer account recordselect acc_no, row_number() over (partition by acc_no order by ??) from tab Dieter
94830 Mar 2011 @ 10:54 PDTDatabaseReplyUSI vs Primary key to enforce uniquenessOops, you're right, you better don't resubmit that query. If you didn't do it already you should open an incident, this is a severe problem. Dieter
94728 Mar 2011 @ 11:53 PDTDatabaseReplyUSI vs Primary key to enforce uniquenessDo you actually need the USI? Do you access it in WHERE? If it's only for the logical uniqueness, it's hopefully already checked in your ETL process: Inserting 50 million rows with just a sing...
94628 Mar 2011 @ 11:44 PDTToolsReplyFastexport data DisplayWhy do you need that? Is it so hard to find out where's the start and the end of a file? ;-) The only way i know is to add another select: select top 1 'START OF FILE' from dbc.dbcinfoV; y...
94528 Mar 2011 @ 11:32 PDTDatabaseReplyTeradata Table Naming The maximum length of a name was 18 according to old Standard SQL, so 30 was much more than that. The system tables are already prepared for VARCHAR(128), so a future release will change this limi...
94428 Mar 2011 @ 11:25 PDTDatabaseReplyTPT - TIMESTAMP FORMAT - century of '19' instead of '20'Reminds me of "year 2k" :-) There's only a global setting (dbscontrol global field 14: CenturyBreak), but no session local setting. Why don't you simply concat '20' to the timestamp and chang...
94328 Mar 2011 @ 08:30 PDTUDAReplyColstats privileges???You just have to concat a "collect stats" string and submit it using dbc.SsyExecSQL like: REPLACE PROCEDURE collectstats (IN DatabaseName VARCHAR(30), IN TableName VARCHAR(30), IN ColumnList VARCH...
94228 Mar 2011 @ 07:55 PDTDatabaseReplyteradata update syntaxWhat means "is not working"? Could you please post the query you tried? Dieter
94128 Mar 2011 @ 07:52 PDTGeneralReplyError Code 3785Hi Annie, your first where-condition is wrong: prodvm.balance_adjustment.adjustment_reason_code = ('B', 'C') The parser expects: prodvm.balance_adjustment.adjustment_reason_code = ANY ('B', '...
94026 Mar 2011 @ 09:13 PDTUDAReplyColstats privileges???No in TD12, in TD13 there's a new STATISTICS right. Of course you can easily encapsulate the COLLECT STATS in a Stored Procedure using Dynamic SQL. Then developers don't need specific rights. ...
93923 Mar 2011 @ 02:11 PDTDatabaseReplyConverting a TIMESTAMP(6) to a TIMESTAMP(0) for InsertThere's no easy way to truncate a TimeStamp :-( Using an intermediate string: CAST(SUBSTRING(CAST(x AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0)) or CAST(CAST(x AS DATE) AS TIMESTAMP(0)) ...
93823 Mar 2011 @ 02:00 PDTDatabaseReplyCan't use count with in a view...There are two ways to rewrite your query using Standard SQL, although both will result in a slightly different output. #1: SELECT A1.CUSTOMER_BAN AS "BAN" ,A1.CREATOR_USERNM AS "USERNM" ,COUN...
93723 Mar 2011 @ 10:30 PDTToolsReplyERROR : **** 19:13:12 UTY0805 RDBMS failure, 5407: Invalid operation on an ANSI Datetime or Interval Hi Anil, AAA is an ODBC setting, it doesn't matter in MLoad. MLoad only uses CLI, but never ODBC, that's why i asked. If you don't set FORMAT it defaults to binary FASTLOAD format, but you nee...
93623 Mar 2011 @ 10:22 PDTUDAReplyFormatting decimal fields while exportingWhat about -0.23000? -.23: TRIM(TRAILING '0' FROM col (FORMAT '-(4).9(8)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END -0.23? TRIM(TRAILING '0' FROM col (FORMAT '-(3)9.9(9)')) || CASE...
93523 Mar 2011 @ 12:20 PDTToolsReplyERROR : **** 19:13:12 UTY0805 RDBMS failure, 5407: Invalid operation on an ANSI Datetime or Interval Hi Anil, some questions: - MLoad using ODBC connection? - is the input file binary data or readable text? - fixed or variable text? - which FORMAT and DATEFORM setting is used in MLoad? Dieter
93423 Mar 2011 @ 12:13 PDTDatabaseReplyIndex selectionIn your case PPI access is always faster than VOSI. But performance might degrade for joins and where conditions without the partitioning column if that column is not also part of the PI. You...
93322 Mar 2011 @ 11:59 PDTDatabaseReplyLooking for View Definition in DBC tableRequestText is the original source code "as is" including comments: SHOW SELECT * FROM VIEW When a view is created then this source code is parsed, object names are resolved and stored in Crea...
93222 Mar 2011 @ 06:49 PDTDatabaseReplyIndex selectionOf course it access will be faster when you partition the tables instead of a VOSI. But you must be carefull when accessing the 2nd table without the date column, this will be much slower than b...
93122 Mar 2011 @ 06:45 PDTDatabaseReplyHash Index and PPIHi Kishore_1, maybe you misread the manuals: Of course you can create a Hash Index on a partitioned table, you can't partition a HI. I really don't understand the meaning of the second post.....
93022 Mar 2011 @ 06:40 PDTDatabaseReplyqueryHi srivalli, select a.acc_no, a.balance + b.cumulative_sum from table_a as a join (select acc_no, sum(amt) over (partition acc_no order by datecol rows unbounded preceding) as cumul...
92918 Mar 2011 @ 11:43 PDTDatabaseReplyConvert DB2 date to Teradata date.I'm not shure why your query fails, but when you write the date in the correct way it works as expected: SELECT * FROM tmp_db2_dt WHERE CAST(db2_dt AS DATE FORMAT 'ddMMMyyyy') < date '2000-01-...

Pages