Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. al1024

    reate table and insert data into Teradata database from MS SQL Server

    Why don't you use JOIN clause. This is Teradata, not O**e or My***. JOIN is efficient ! SELECT ptn.patient_name , ptn.Patient_age , ptn.Patient_weight , SUM(CASE WHEN drug_name = "Warfarin" THEN 1 ELSE 0 END) AS CountWarfarin , SUM(CASE WHEN drug_name = "Heprin" THEN 1...
  2. al1024

    create 1000 tables using script

    When you need a table containing range of integers, you can use SYS_CALENDAR.CALENDAR.DAY_OF_CALENDAR... SELECT cal.day_of_calendar AS myvalue FROM sys_calendar.calendar AS cal WHERE myvalue BETWEEN 1 AND 1000
  3. al1024

    split a record by date span

    You could use calendar view : select bat.batch_id , cal.calendar_date as start_date , case when cal.calendar_date + 30 >= bat.end_date then bat.end_date else cal.calendar_date + 30 end as end_date from batch as bat inner join...
  4. al1024

    Not able to run query using teradata

    Could you give this error message ?
  5. al1024

    why is teradata preferred over oracle by some companies?

    Surely performance is a really good reason to change... And the cost of administration.
  6. al1024

    Select Statement help...

    If you want select duplicate rows on (ACCOUNT_NO, SQN_NO), you could use this request : select * from ACCOUNT_TABLE where (ACCOUNT_NO, SQN_NO) in ( select ACCOUNT_NO , SQN_NO from ACCOUNT_TABLE group by ACCOUNT_NO...
  7. al1024

    Encode and Decode when logon in BTEQ

    First, password doesn't appear in log file. It's secure on this side. Second, you can use an external file to store your logon command and call it with .RUN FILE logon_file It won't be very difficult to create a temporary uncrypted logon_file that will be erased on next bteq. Something like that...
  8. al1024

    Exporting Report In BTEQ

    Just add an empty title to your result column. SELECT (COUNTRY_CD) || ... ||(CONTINENT_NAME) (TITLE '') FROM ...
  9. al1024

    SQL for Parsing a String Delimited with Periods

    You can use "derived columns"... : select 'ABC.DEF.GHI.JKL.MNO' as MYCOLUMN , substring(MYCOLUMN from 1 for (position('.' in MYCOLUMN) (named POSDOT1)) - 1) as FIELD1 , substring((substring(MYCOLUMN from POSDOT1 + 1) (named SUB1)) from 1 for (position('.' in SUB1) (named POSDOT2)) - 1) as...
  10. al1024

    help in update select statement.

    Try this : UPDATE bi_stg_1.tl_compare FROM ( SELECT COUNT(*) AS cnt FROM diff_x ) AS x , ( SELECT COUNT(*) AS cnt FROM diff_y ) AS y SET count1 = x.cnt , count2 = y.cnt WHERE database_name = 'stg' AND...
  11. al1024

    Derived Column with Rank and Qualify

    I'm confused... I forgot to give a name to the derived table. Only add "as XXX" between ")" and "ORDER" and it will be fixed !
  12. al1024

    Derived Column with Rank and Qualify

    Try this : SELECT 'Total ' AS Row_One , Name , Column2_Sum , Column3_Sum , Column4_Sum , Total_Sum , RANK(Total_Sum) Total_Sum_Rank FROM ( SELECT Name , SUM (Column2) AS Column2_Sum , SUM (Column3) AS Column3_Sum , SUM (Column4) AS Column4_Sum , (Column2_Sum +...
  13. al1024

    joining two tables

    I answered this question on Teradata Discussion Forums a few days ago. This answer could help other users on this forum... select T1.EMP_ID , T1.SPL_EVENT_DATE , T1.NAME , T1.ADRESS , T2.REVISED_MONTHLY_SALARY from TABLE_1 as T1 inner join TABLE_2 as T2 on T1.EMP_ID = T2.EMP_ID where exists (...
  14. al1024

    Need to read date field from table in BTEQ on UNIX

    you need two bteq scripts inside your Unix shell script : # Prepare the tmp.sh script and get the run_date value bteq << first .logon ?? .export report file ='tmp.sh' SELECT 'export run_date=' || (run_date (format '??')) FROM control_table ; .export reset .quit 0 first # Execute the tmp.sh...
  15. al1024

    Searching Teradata via regular expressions.

    You have two wildcards : % and _ _ replaces a single char. So you can test your format with : expr like '___-____'
  16. al1024

    Can any date function solve this problem?

    To get the date of last day of month, you can try this expression : add_months(EVNT_PROC_DT + 1 - extract(day from EVNT_PROC_DT), 1) - 1
  17. al1024

    Keep Max value from a unqiue fields

    Perhaps did you need something else in your record, such as an ORDER_ID for example. Try this : select A.NAME , A.ORDER_ID , A.ORDER_QTY from MyTable as A where exists ( select 1 from MyTable as B where A.NAME = B.NAME having A.ORDER_QTY = max(B.ORDER_QTY) ) ;
  18. al1024

    Keep Max value from a unqiue fields

    select NAME , max(ORDER_QTY) from MyTable group by NAME
  19. al1024

    Help with CHARACTERS

    ...or Interval value, converting it to an exact numeric value. FULL JOIN is a susbstitute for the FULL OUTER JOIN ANSI SQL-99 clause. ( select A.*, B.* from A FULL OUTER JOIN B on A.ID=B.ID ) is something equivalent to ( select A.*, B.* from A LEFT OUTER JOIN B on A.ID=B.ID union select A.*...
  20. al1024

    import (mload/bteq) with duplicate keys)

    Test the integrity of your data before inserting : INSERT INTO doubles (id, columns, batch_no) SELECT id, columns, $BATCHNO FROM source INNER JOIN target ON target.id = source.id ; INSERT INTO target (id, columns) SELECT id, columns FROM source WHERE NOT EXISTS (...

Part and Inventory Search

Back
Top