InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comMon, 12 May 2008 18:34:12 GMT20http://www.teradata.com/teradataforum/Topic11411-1-1.aspxI have created a table like this, CREATE MULTISET TABLE SADM_UTST_ETLBASE_INDIA.datecheck ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( dat DATE FORMAT 'YYYY-MM-DD') PRIMARY INDEX ( dat );when i am trying to insert statement insert into datecheck values('2004-02-03'),date values getting inserted in the format 02/03/2007,i dont know why its happening like this...Any help would be appreciated...thanks in advanceMon, 12 May 2008 00:40:58 GMTsarvan135http://www.teradata.com/teradataforum/Topic11381-1-1.aspxHi,If anyone can help me in conversion of query to teradata1.isnull(ACC_DET.user_name,' ') as usr2. isnull(MAX(isnull(ACCESS_DATE,'')),' ') as max_access 3.datediff(day, access_date , (select max(access_date) from cess_details )) < 90Thu, 08 May 2008 04:45:28 GMTRahulGhttp://www.teradata.com/teradataforum/Topic11369-1-1.aspxI want to load data from a flat file into empty table. If the field in the file is NULL, the NULLIF will help me to give default value in the table.However when the field in the file is having spaces or its zero lenght string, How can i handle this to assign Default values.Are there anyways to perform some validations on such incoming fields in FASTLOAD SCRIPT.Thanks,AmitTue, 06 May 2008 01:05:18 GMTamitaryanshttp://www.teradata.com/teradataforum/Topic11366-1-1.aspxWe are using Teradata ODBC and trying to set up a job using SQL Studio Manager and each time the job is ran, the error would come up stating "The LAN message RequestNo field is invalid". Any advice on the fix for this issue?Mon, 05 May 2008 13:47:19 GMTking_tdhttp://www.teradata.com/teradataforum/Topic11344-1-1.aspxHi All,Can we use CASE statement in a fastload script. I want to check for zero length string. Is it possible to use some thing like thisCASE(LENGTH(:input_string)=0) Then 'UNKNOWN' Else :input_stringPlease advise.Thanks,AmitFri, 02 May 2008 02:11:06 GMTamitaryanshttp://www.teradata.com/teradataforum/Topic11353-1-1.aspxHey guys, I was just wondering, what software do you primarily use in Business Intelligence/Datawarehousing, and are their any significant cutbacks in any proposed projectsSat, 03 May 2008 16:39:26 GMTjohn_smhttp://www.teradata.com/teradataforum/Topic9014-1-1.aspxIs any one in this forum using Metadata Services? We are looking to work with it but wanted to get some info on those that are using it and their experience? what they use it for and how they use it. Any info will help.Wed, 26 Sep 2007 12:30:08 GMTebohonhttp://www.teradata.com/teradataforum/Topic11332-1-1.aspxHow can I, in a select statement, strip "garbage" characters from a column in my result set? For example, we have a table that was loaded by another application, and some of the values contain what looks like a carriage return character.Wed, 30 Apr 2008 13:36:06 GMTJustMehttp://www.teradata.com/teradataforum/Topic11326-1-1.aspxHi, I am trying to extract data from multiple tables,joining in the source qualifier, and inserting into the target table.Using Tpump external loader.I get the following error after the source rows are read.... "error outputing the row to output file" the session logs shows broken pipe error.... Tried is staged option and looked at the row mentioned in the loader log but cannot find anything wrong with that row. could the problem be with uniqueness?The target table has row_wid as primary key. Any help is appreciated. Thanks in advance.Tue, 29 Apr 2008 21:59:06 GMTdattahttp://www.teradata.com/teradataforum/Topic11088-1-1.aspxIs there any difference in performance when -(1) We collect stats on Column(2) We collect stats on IndexSay table...COLLECT STATS ON INDEX A (ACCNO);COLLECT STATS IN COLUMN A (ACCNO);A is a table with UPI as ACCNO.ThanksFri, 28 Mar 2008 17:06:07 GMTgururajhttp://www.teradata.com/teradataforum/Topic11095-1-1.aspxHi everyone, I am using Tpump Upsert job for loading a Teradata Target Table. I am getting the following error :**** 00:02:44 UTY6630 Options in effect for following TPump Import(s): . Tenacity: 4 hour limit to successfully connect load sessions. . Max Sessions: 16 session(s). . Min Sessions: 12 session(s). . Checkpoint: 0 minute(s). . Errlimit: 1 rejected record(s). . Restart Mode: SIMPLE. . Serialization: OFF. . Packing: 1 Statements per Request. . StartUp Rate: UNLIMITED Statements per Minute.**** 00:02:45 UTY8001 RDBMS failure in Packing Test: 3817, The positional parameter list has too many values.Can you tell me anything about this error or if there is problem with options of TPump ImportAny Help is appreciatedThanks,VineetMon, 31 Mar 2008 03:17:19 GMTvineethttp://www.teradata.com/teradataforum/Topic10892-1-1.aspxAn existing ETL job at my site is coded with an exclusive lock applied to delete rows from a table.I presume this was coded this way to ensure no one accessed an empty table between the delete and insert.There are 1200 rows in table. The delete and re-insert of current rows (again 1200) is a subsecond operation,however occasionally another user or app. is running a long running query with an access lock on this table. Since this causes a block for the job trying to get the exclusive lock I get paged in the middle of the night to resolve.Has anyone heard of a legitimate reason for coding this way?Our ETL lead is reluctant to change because the world may come to an end. Any Ideas of how to convince him would be appreciated.TBobTue, 11 Mar 2008 21:09:15 GMTTBobhttp://www.teradata.com/teradataforum/Topic11249-1-1.aspxI would like to know if there is a way to automatically grant a role to a child. If UserA owns UserB, UserC, and UserD, I would like to be able to grant role_basic_rights to UserA and have that right automatically granted to UserB, UserC, UserD and any future users that will be owned by UserA.Thu, 17 Apr 2008 10:56:51 GMTJustMehttp://www.teradata.com/teradataforum/Topic11286-1-1.aspxAs per the subject, I'm trying to run a Teradata macro from SAS using explicit SQL pass through. The macro parameter is a date, but the SAS date format and the Teradata date format differ, and I haven't found a way of converting the date parameter.Here is an excerpt of the SQL I'm trying to run: insert into mySAStable select * from connection to teradata ( exec mySchema.myMacro (&dateParameter) );I have found the function SASDATEFMT, but I can't figure out how to use it to convert a parameter and then use the result.Similarly, I've found the SAS sample code to "Convert a value in the form of MMYY to a SAS date", which works great, but can't figure out how to use it in the context above.Will appreciate any help and/or pointers.Regards,PeterWed, 23 Apr 2008 00:26:09 GMTpjroutledgehttp://www.teradata.com/teradataforum/Topic11220-1-1.aspxHi Everyone,I'm going to work on an ETL project -where source is oracle ,target is teradata and ETL tool to be used is informatica.There are two levels -one is load into staging(staging is also teradata) and second is loading into target tables.I query the oracle source tables and load into staging area.Which of the approach is good -1.create a one to one mapping to do this or 2.Use any of the tools offered by Teradata -like Mload,Tpump,etc in informatica and do it.Please tell me the Pros and Cons of these two approaches.I've been told to use the first method(one to one mappings).Please advice on the second level as well ( from staging to target) whether to use one to one mapping or teradata tools.I'm really afraid because there is an automatic preimary index getting created in Teradata tables and this lead to rejection of records in some cases.Please clarify.Thank you all in advance.-Thanks JeromeMon, 14 Apr 2008 16:46:33 GMTjeromehttp://www.teradata.com/teradataforum/Topic11104-1-1.aspxWe've a SET table defined with a UPI that has 9.3 Million rows on a 1400 AMP, V2R6 Teradata system. Please look at the numbers below. The AMP that has the MAX rows has 3.47 times more rows than the AMP with the MIN rows. This does not make sense to me given the fact that we've a UPI and a high volume of rows in the table for an even distribution.xTimesGreaterMaxAvg - 1.56xTimesGreaterMaxMin - 3.47Mx - 1393664Avrg - 890657.07Mn - 401920CurPerm - 1282546176Can someone please shed some light on this? Is there something we can look at to improve data distribution? Any help would be greatly appreciated.PS: The table also has a PPI defined on a date field which is part of the UPI (if this matters).Thanks,Sayee.Mon, 31 Mar 2008 19:08:41 GMTSayeeRamPrasadhttp://www.teradata.com/teradataforum/Topic11139-1-1.aspxHi All,Can any one tell me why stored procedure saved in Perm space, but Macro is not saved in perm space...As both are doing the same job why they r not saved in the same place....Any clue?Regards,RavindraFri, 04 Apr 2008 05:31:11 GMTravindrareddyhttp://www.teradata.com/teradataforum/Topic11141-1-1.aspxCan anyone expain the diff b/w Secondary Index and Hash IndexAlso In which scenario we will go for Hash Index?...Fri, 04 Apr 2008 08:47:46 GMTravindrareddyhttp://www.teradata.com/teradataforum/Topic11131-1-1.aspxHi all,I've a big problem with a "simple" select.I've the following table:ct toutput(mese_cd integer,field1_cd varchar(10), )UNIQUE PRIMARY INDEX XPKDT_OUTPUT ( MESE_CD , field1_cd , )PARTITION BY RANGE_N(MESE_CD BETWEEN 200401 AND 200412 EACH 1 ,200501 AND 200512 EACH 1 ,200601 AND 200612 EACH 1 ,200701 AND 200712 EACH 1 ,200801 AND 200812 EACH 1 );mese_cd is a field containing YYYYMM. The table contains about 500,000,000 recs.I need to verify if in that table exists data for a specific mese_cd.The query I need is something like:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = 200803SAMPLE 1This query uses partitions and is very fast (about 15 secs, 1/2 if the partition is empty).My problem is that the value 200803 is contained into a volatile table. I tried to modify it in this way:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = (SELECT min(mese_cd) FROM temporary_volatile_table)SAMPLE 1but it oirginates a full table scan on the toutput table (partitioning is not used).Also a simple test:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = (SELECT 200803)SAMPLE 1produces a full table scan..I tried also to create a working table partitioned like the toutput table, containing only the field mese_cd and only a row. I put these two tables in join but partitions are not used yet.Is there any easy suggestion to solve my problem? I've only to check if on the condition filed1_cd there are data available in a specific mese_cd.Any help will be apreciated :)Thu, 03 Apr 2008 07:16:23 GMTAlethesnakehttp://www.teradata.com/teradataforum/Topic11096-1-1.aspxI need to remove the Year_Id in the where clause and include it in the CASE stmt for performance reasons. I'm merging alot of code into 1 statement and this is my first go at this.However, I'm selecting a sum aggregate and the Year Id is a constant variable.I receive the 3504 syntax error: Selected non-aggregate values must be part of the associated group.Any idea how I could get around this? SELECT (CASE WHEN Year_id = 2008 THEN (CAST((COALESCE(sum(f_basic_premium * F_exch_rate),0)) AS NUMERIC(18,6))) END),-- INTO :v_PremYTD FROM fat_bse_po_risk_detail a JOIN trt_lu_trans_subtype b ON a.Tr_sub_type_id = b.Tr_sub_type_id JOIN tit_lu_day c ON a.cur_trn_dt = c.cur_trn_dt JOIN POt_lu_policy d ON a.Policy_id = d.Policy_id WHERE Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR') AND Po_corp_unit_id in ('GEI', 'GNI', 'GED');-- AND Year_id = 2008Mon, 31 Mar 2008 06:37:28 GMTmarcmchttp://www.teradata.com/teradataforum/Topic11054-1-1.aspxHi,How pass string like 'va', 'vb', 'vc' as one input?Say the code like thiscreate macro macro_name (input_name input_type) as ( select columns from table_x where table_x.column_y in (:inputname) group by 1,2 order by 1,2;)execute macro_name (how-to-pass);Wed, 26 Mar 2008 16:34:04 GMTgeorgexuhttp://www.teradata.com/teradataforum/Topic11045-1-1.aspxCan a variable be used inside a case statement like so:SELECT (CASE WHEN Year_id = :v_Year_id THEN 1 END)FROM ......Wed, 26 Mar 2008 11:40:16 GMTmarcmchttp://www.teradata.com/teradataforum/Topic11083-1-1.aspxSELECT count(distinct a.claim_ref_id)INTO :v_RepClaimsTWFROM fat_bse_cl_pay_cre_trans aJOIN prt_lu_product bON (a.product_id = b.product_id)WHERE (a.Reported_date_id between '2007/12/02' AND '2007/12/08'AND b.Pr_Group_id not in ('1')) -- if executed down to here 0 is the countGROUP BY a.claim_ref_idHAVING (CAST(sum((a.F_cl_total_reserve * a.f_exch_rate)) AS NUMERIC(18,2))) <> 0.00; -- if executed down to here the count is blank. If records exist then you get a valid count, if there are no claims to group on the count is blank. I understand why but my code says if SQLCODE <> 0 THEN....leave procedure.Why does SQLCODE become 1 if the code executed okay?or can this be wrapped up somehow to return 0(zero)?Fri, 28 Mar 2008 10:36:42 GMTmarcmchttp://www.teradata.com/teradataforum/Topic11061-1-1.aspxI was previously using Teradata Version 6 and we used to fastload data into tables with fields, decimal(18,4). Now we have shifted to Teradata Version 12 and requirement is that the tables should have decimal(19,4) fields. I get the following error when I try to fastload data into a decimal field(19,4), "DECIMAL TYPE- record too short".However when I try to fastload with decimal(18,4) in Teradata Version 12, it runs just fine. So could someone please help me with this.Thu, 27 Mar 2008 01:59:18 GMTRakesh nchttp://www.teradata.com/teradataforum/Topic10860-1-1.aspxI am trying to reduce this Query's runtime by 50% or more!I've tried a number of things...INSERT INTO MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 (policy_id, policy_desc, claim_ver_id, claim_code_desc, region, currency, product_type,estrec_code, riskno, match_est, match_recest, pay_est, rec_est) SELECT a15.policy_id, a15.policy_desc, a12.claim_ref_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, SUBSTR(a13.Pr_type_id,2,3), Ca_est_rec_id, TRIM(SUBSTR(Claim_line_desc,23,2)), 'N', 'N', CAST(SUM(CAST(F_CL_EST_PAY AS NUMERIC(16,2))) AS NUMERIC(16,2)), CAST(SUM(CAST(F_CL_EST_RECOVER AS NUMERIC(16,2))) AS NUMERIC(16,2))FROM MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 a11 JOIN cav_lu_claim_reference a12 ON (a11.claim_ref_id = a12.claim_ref_id)JOIN prt_lu_product a13 ON (a11.Product_id = a13.Product_id)JOIN POt_lu_policy a15 ON (a11.Policy_id = a15.Policy_id)WHERE CT_TYPE_ID <> '90'GROUP BY a12.claim_ref_id, Ca_est_rec_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, TRIM(SUBSTR(Claim_line_desc,23,2)), a15.policy_id, a15.policy_desc, SUBSTR(a13.Pr_type_id,2,3);MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 = 2906701 Rowscav_lu_claim_reference = 97622 Rowsprt_lu_product = 474 RowsPOt_lu_policy = 3506973All tables have up to date stats, there are no product joins. I have dropped stats completely and the performance reduced by 10%. There are no other recommended stats from diagnostic helpstats. I don't know what to do next. Any ideas? I rarely use indices. Explanation 1) First, we lock a distinct CQA_data_t."pseudo table" for read on a RowHash to prevent global deadlock for CQA_data_t.PRt_lu_Product. 2) Next, we lock a distinct CQA_data_t."pseudo table" for read on a RowHash to prevent global deadlock for CQA_data_t.POT_LU_POLICY. 3) We lock a distinct CQA_DATA_T."pseudo table" for read on a RowHash to prevent global deadlock for CQA_DATA_T.CAV_LU_CLAIM_REFERENCE. 4) We lock a distinct MARC_MCGUCKIAN."pseudo table" for read on a RowHash to prevent global deadlock for MARC_MCGUCKIAN.a11. 5) We lock a distinct MARC_MCGUCKIAN."pseudo table" for write on a RowHash to prevent global deadlock for MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1. 6) We lock CQA_data_t.PRt_lu_Product for read, we lock CQA_data_t.POT_LU_POLICY for read, we lock CQA_DATA_T.CAV_LU_CLAIM_REFERENCE for read, we lock MARC_MCGUCKIAN.a11 for read, and we lock MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 for write. 7) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from CQA_data_t.PRt_lu_Product by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is duplicated on all AMPs. The size of Spool 4 is estimated with high confidence to be 3,318 rows. The estimated time for this step is 0.01 seconds. 2) We do an all-AMPs RETRIEVE step from MARC_MCGUCKIAN.a11 by way of an all-rows scan with a condition of ( "(MARC_MCGUCKIAN.a11.Ct_type_id <> '90') AND (NOT (MARC_MCGUCKIAN.a11.Claim_ref_id IS NULL ))") into Spool 5 (all_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 5 is estimated with high confidence to be 2,807,964 rows. The estimated time for this step is 19.68 seconds. 8) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to Spool 5 (Last Use) by way of an all-rows scan. Spool 4 and Spool 5 are joined using a single partition hash join, with a join condition of ("Product_id = Product_id"). The result goes into Spool 6 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 17.23 seconds. 9) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from CQA_DATA_T.CAV_LU_CLAIM_REFERENCE by way of an all-rows scan with no residual conditions into Spool 7 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 7 by row hash. The size of Spool 7 is estimated with high confidence to be 683,354 rows. The estimated time for this step is 2.17 seconds. 2) We do an all-AMPs JOIN step from CQA_data_t.POT_LU_POLICY by way of a RowHash match scan with no residual conditions, which is joined to Spool 6 (Last Use) by way of a RowHash match scan. CQA_data_t.POT_LU_POLICY and Spool 6 are joined using a merge join, with a join condition of ("Policy_id = CQA_data_t.POT_LU_POLICY.Policy_id"). The input table CQA_data_t.POT_LU_POLICY will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 8 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 8 by row hash. The result spool file will not be cached in memory. The size of Spool 8 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 25.17 seconds. 10) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a RowHash match scan, which is joined to Spool 8 (Last Use) by way of a RowHash match scan. Spool 7 and Spool 8 are joined using a merge join, with a join condition of ("Claim_ref_id = claim_ref_id"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 5.00 seconds. 11) We do a single-AMP SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed locally, then placed in Spool 9. The aggregate spool file will not be cached in memory. The size of Spool 9 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 19.21 seconds. 12) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The result spool file will not be cached in memory. The size of Spool 1 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 18.04 seconds. 13) We do an all-AMPs MERGE into MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 from Spool 1 (Last Use). 14) We spoil the parser's dictionary cache for the table. 15) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement 1. Mon, 10 Mar 2008 08:30:45 GMTmarcmchttp://www.teradata.com/teradataforum/Topic10811-1-1.aspxI have a requirement to provide a way for C and Fortran applications to query and to load data into Teradata. This should work on Windows, Unix and Linux.I have heard of 'Teradata Preprocessor2 for Embedded SQL' that seems to require a C library from Teradata and can be used to execute embedded SQLs in several languages including C.Has anyone used this? If so, where can i find this C library?How easy is this to use? Does this have any advantages over using ODBC connection in a C program? Is this more platform-independent than using ODBC?Wed, 05 Mar 2008 14:25:54 GMTradshrihttp://www.teradata.com/teradataforum/Topic10988-1-1.aspxHi,I wrote a SQL in Teradata and the report needs to be in the pipe delimited format.I was told that I need to add another select with all the column names seperated with pipes (||) to get that.Can't really find any info.Can someone help me with this?ThanksRajivWed, 19 Mar 2008 19:06:10 GMTRajivTxhttp://www.teradata.com/teradataforum/Topic10983-1-1.aspxHi - I am looking for some EDW Metrics or sample Dashboards or presentations which can show the value EDW brings to the organization. I appreciate if you have something available to share with meThanks in advanceRamWed, 19 Mar 2008 06:50:59 GMTSam_989898http://www.teradata.com/teradataforum/Topic10940-1-1.aspxIs there any mechanism in teradata to know exactly which tables are lockedat a particular instance of time and the types of lock being imposed on the tablesFri, 14 Mar 2008 05:03:55 GMTupendra.mishrahttp://www.teradata.com/teradataforum/Topic10952-1-1.aspxHi,Is there any DBC tables that I can use to find out what's the Total Row Length of a row in the TERADATA table?ThanksFri, 14 Mar 2008 19:34:24 GMTEDW ETLhttp://www.teradata.com/teradataforum/Topic7520-1-1.aspxCan i write a Select statement in Mload? IF not how do i assign a value from the table for Null value in the file. Thank you.Fri, 18 May 2007 15:15:02 GMTRao gahntahttp://www.teradata.com/teradataforum/Topic9367-1-1.aspxAnybody who knows how to do a pass through statement from SAS to Teradata?Tue, 23 Oct 2007 23:41:21 GMTOblakhttp://www.teradata.com/teradataforum/Topic10800-1-1.aspxI need help in optimizing the query below:Query is about identifying UK of deletes in a large table (70+ GB) using the pre-load-image and post-load-image. Here is the query we have written but this consume a ton of CPU.The two tables are joined on the UK in the query below:Need urgent help for performance improvement or any other alternatives, immediate responses would be greatly appreciated.LOCK TABLE pre_load_image FOR access LOCK TABLE post_load_image FOR access SELECT 'sap_clnt_id=' || COALESCE( A.sap_clnt_id , '' ) || '~co_cd=' || COALESCE( A.co_cd , '' ) || '~fin_doc_nbr=' || COALESCE( A.fin_doc_nbr , '' ) || '~fscl_yr_nbr=' || COALESCE( A.fscl_yr_nbr , '' ) || '~fin_doc_line_nbr=' || COALESCE( A.fin_doc_line_nbr , '' ) FROM pre_load_image A WHERE NOT EXISTS ( SELECT sap_clnt_id , co_cd , fin_doc_nbr , fscl_yr_nbr FROM post_load_image B WHERE A.sap_clnt_id = B.sap_clnt_id AND A.co_cd = B.co_cd AND a.fin_doc_nbr = b.fin_doc_nbr AND A.fscl_yr_nbr = b.fscl_yr_nbr AND A.fin_doc_line_nbr = b.fin_doc_line_nbr ) ; Thanks in advance!_SreeWed, 05 Mar 2008 02:00:44 GMTchakri400http://www.teradata.com/teradataforum/Topic9885-1-1.aspxHi I want to write a query to get results for only last week, i.e Date between (current_date -7) and current_Date. when i write above syntex it is not working for me. Can anyone help me?Thank in advance.-NitinWed, 28 Nov 2007 10:24:08 GMTnitinajmerihttp://www.teradata.com/teradataforum/Topic10769-1-1.aspxIn teradata, the numbers cannot be greater then (18,3). I have some data in my Oracle source tables which has a size of DECIMAL(22,7). So if i push this data to Teradata Target table, then what will happen to this data,the teradata loader would neglect this data or truncate data of this size ?Thanks in advance,Mon, 03 Mar 2008 04:13:23 GMTvineethttp://www.teradata.com/teradataforum/Topic10211-1-1.aspxWhen Microstrategy runs a report schedule on Teradata, does it hit the database anywhere else apart from the specific data warehouse tables being queried? In other words are any syatem tables maintained for the duration of the report schedule?We are trying to run Microstrategy report schedules as triggered events. I have the mechanics working but need a facility within which I can poll when one schedule is running & when it is not running before kicking off the next schedule in a parameters list. We have to do this in a sequence and cannot execute all report schedules at the same time due to dependancy and business priority.If any other business is doing something like this can they please let us know how you do it or any alternative solutions you may have found. We had thought of querying the Meta Data or counting a number of files in the Microstrategy application directories but this has proved to add complexity and reduce reliablility.Our platform consists of:Linux redhat Data Warehouse Load Control program(this is where i kick off my report schedulesTeradata Data Warehouse.Microstrategy.Microstrategy Meta Data is on MS SQLServer Express.Wed, 09 Jan 2008 05:06:50 GMTmarcmchttp://www.teradata.com/teradataforum/Topic10715-1-1.aspxWhat is a HOT AMPWed, 27 Feb 2008 04:22:58 GMTppurnimahttp://www.teradata.com/teradataforum/Topic10705-1-1.aspxHi There,can anybody let me know how to convert a number into corresponding hexadecimal number in TD.Any further suggestion will heartly welcome.UmeshTue, 26 Feb 2008 08:46:31 GMTUShttp://www.teradata.com/teradataforum/Topic10604-1-1.aspxHi,FROM_TZ function in Oracle converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.In oracle select from_tz(TIMESTAMP '2008-02-15 01:50:42', '-7:00')from dual;will return 15-FEB-08 01:50:42.000000000 AM GMT -07.00I want to achieve same in TD. I have tried following in TDselect cast('2005-09-11 13:20:53'||'+03:00' as timestamp with time zone format 'YYYY-MM-DDBHH:MI:SSDS(F)Z')2005-09-11 13:20:53.000000+03:00But i want same like in oracle.Any suggestion will welcome.UmeshFri, 15 Feb 2008 07:23:57 GMTUShttp://www.teradata.com/teradataforum/Topic10627-1-1.aspxHi, I am trying to use notify exit with TPUMP. I have compiled my TPUMP notify “C” with cc under Linux. My question is 1. When this Notify exit is called?In the TPUMP reference doc, the event ‘Interim Run statistics’ is called when Tpump is about to update the Monitor interface table or Tpump successfully completed a checkpoint or an Import has just completed successfully. But when I set checkpoint=0, every minute (I,e time difference between checkpoint end and checkpoint begin) the stat information is written into a file. The time difference is approximately 1 minute even when you set checkpoint =1 or checkpoint =2 or checkpoint =3 or checkpoint =4 or checkpoint =5 …. As per TPUMP reference doc, It should call the Notify exit every 5 minutes when checkpoint=5. Please clarify my doubt.Thanks in advance.Tue, 19 Feb 2008 01:45:38 GMTRakesh nc