150 - 200 of 210 tags for sql

Pages

I'm trying to run a query from a search form by date and I'm receiving the following error: [Teradata Database] Partial string matching requires character operands.

The code for the search is:

ElseIf OptDate.Checked = True Then
sSQL &= "DATE_SUBMIT Like ('" & txtDateRec1.Text & "%')"
End If

When Flat SQL looping logic involves complex processing of data elements from multiple tables, the loops can almost always be transformed into Set SQL with derived tables. By eliminating looping logic, the performance benefits of parallel processing can be exploited.

hi
i want to capture mismatched records from 2 tables?

for example table_a has 200 records,table_b has 400 records out of which 120 records are same from both the tables, so i want to identify and capture the 360 unmatched records+ 1(the 240 times duplicated record) into table_c

thanks in advance.

Hello,

I have a current situation with a stored procedure and could use some guidance / advice. Currently we have a stored procedure that generates dynamic SQL which is based on customer inputs. Specifically, I'm having problems with a column being referenced in the SP that is undergoing funky range scans. I will refer to this column as ABC. Here are the 3 rangescan scenarios for ABC:

1) User enters the following: ABC = 100-105

Hi ALL,
I need to calculate the number of columns of specific tables in Teradata indeed. I searched & tried but none of my solutions work. is there a way to do so. any suggestion will be appreciated.

Thank you,

The most difficult task when converting Flat SQL (cursor-based procedural code) to Set SQL involves translating complex logic and accounting for multiple data sources.  The Derived Table feature of Set SQL simplifies this process.

Is there a way in SQL to return data values in a particular color depending on some logic? Something like Green if everything is "normal", Yellow if values are slightly out of bounds and Red if values are extremely skewed.

Thanks!

Paul

Topics related to the Teradata SQL Parser: Metadata Query, Parsing, formatting, analysis of SQL script, customizable SQL formatter, SQL syntax check

Hallo,

does somebody know how many session/sqls MS Query sends over ODBC to teradata? Currently I refresh an Excel Pivot (with external Data on Teradata) with a VBA script and it takes very long time and on teradata I see a lot off sessions... I don't understand why. The second quession is: Can I optimze the request?

Thanks a very lot

Markus

PS.: That's a part off the VBA-Code:

SQLtxt = ActiveWorkbook.Sheets("SQL - Pivot").Cells(2, 1).Value

Sheets("BOOK1").Select
Range("PIV").Select

Perhaps the easiest way to transition from a flat logic mindset to a set logic mindset is to convert some Flat SQL to Set SQL, then stare at both of them until the logical equivalence of the two approaches becomes clear.

Have you ever been bewildered trying to tune complex Teradata queries that go on for pages and pages of EXPLAIN text?  This presentation goes beyond "Explain the Explain", to teach strategies and techniques for quickly deciphering the most expensive operations in your queries.  Taught by a 20+ year Teradata veteran, examples of real queries gathered from some of the largest production environments in the world are used.  This is advanced material covered at a quick pace.

Hi all,

I am trying to select a column that has a max length of 5. If the data in the column has less than 5 characters, I pad spaces to the end of it to make it 5. In SQL, the select is written:

SELECT LEFT(Column + ' ', 5) FROM Table

What would the equivalent in Teradata be?

Hi guys I am a beginner and received an email from Oracle regarding JavaOne & Oracle Develop conference 2011 to be held in Hyderabad..

Does your data warehouse contain organizational hierarchies, bills-of-material, or transportation networks? If so, you likely have a good candidate business problem for recursive SQL...

How do you determine the month-end date when building a date dimension?

One of our ETL developers recently pointed out that our date dimension (which I had populated) had incorrect values for month-end date, so I had to find a way to reliably calculate that value.

The PERIOD data type was introduced in Teradata 13.0. This follow-on article builds on the first article (Exploring Teradata 13's PERIOD Data Type) and will again make heavy use of examples, but this time we will dig into the functions that are part of this new feature set.

If you have not read the first article, consider doing that now and then returning here. The examples here will use the tables introduced in the first article and add a few more.

Problem: How do you add or subtract a "fractional second" to a TIMESTAMP column in Teradata?

The available documentation is not very clear, so let's look into it using examples.

Basic use of INTERVAL

Build, Populate, and Query Test Table

Problem: There are many cases where a database stores the previous value along with the replacement value. When an update happens, it is important to validate that (using CDC terms) the "before image" matches the most-recent "after image". If those don't match, then the chain of updates is broken.

The specific problem being solved here is where the prior value/new value pairs are stored in separate columns in the each row.

Key Prior Value New Value Date
1 A B 2011-02-05
1 B C 2011-02-06
1 C D 2011-02-07
1 D E 2011-02-08

These prior value/new value pairs generally build a chain. But how do you know when that chain is broken? We'll build an example and show the query that flags the records where the chain is broken.

I am using Teradata SQL Assistant 12. I have an sql query consisting of 26 steps - 13 create table select statements with a drop table statement before each. Sometimes, but not always, when I execute the query it will run the first 3 steps and stop. It does not error, nor does it tell me it did not execute the entire code.

I am brand new to Teradata and SQL and have a rather basic question. I am reading the book "Teach yourself SQL in 10 minutes" and am trying to import some sample data into Teradata so I can follow along with the examples in the book. The book has a number of different downloads available, but none of them specifically say Teradata.

The PERIOD data type was introduced in Teradata 13.0. This first article will use examples to dig into the concepts that are part of this new feature. The second article will use examples to examine all the functions available in version 13.0.

What is wrong with this SQL

UPDATE test.Summary1
SET test.Summary1.c1 = test.s_table.C1 and
test.Summary1.c2 = test.s_table.C2
WHERE test.Summary1.acc_nbr = test.s_table.AccessNbr ;

Hi I am pretty new to Teradata and already facing one big challenge:

I need to deconcatenate column to multiple separate columns.

My column COLUMN contain string which looks like this one
value1~+~value2~+~value3~+~value4 and I need to deconcatenate results to 4 separate columns COLUMN1 with value1, COLUMN2 with value2, ...

I'm trying to create a monthly query in BOXI running off of a Teradata database. The query will choose the first day of the 5th month back and the last day of the current month. I tried using the DATEADD function but it did not work. How can I do that in Teradata SQL? Is there an equivalent?

Hello!

We have a process whereby user queries require "approving" or "rejecting" when accessing sensitive customer data. In order to make the process run more smoothly we ask that users comment their queries to pass a note for the reviewer:

SELECT Top 100 * /* Project Projectname Analysis Q1 */
FROM databasename.tablename
;

What we are finding with ODBC connectivity is that the comments are stripped out before reaching Teradata. If we set the option Disable Parsing in the ODBC options, the comments remain in the query and this is the workaround we are following.

I'm searching for a tool or utility that will take views and create a tree structure. My task is develop a comprehensive mapping of my company's inbound and outbound processes. Since a majority of our outbounds are sourced from views an automatic method of generating a tree-like strucutre in text or xml would be a huge help.

Hello everyone,

I am new to teradata and I am trying to upload a file using fast load.
When I run the batch file I get the following error in the log file:

Not enough fields in vartext data record number: 1

In the teradata table, I have varchar (100) for all the colums, so not sure what is going on.

My script is the following:

sessions 1;
errlimit 10;
logon DWSANA/username,password;

set record vartext;
begin loading D_CFAEISDB.FTP_profile_fl01 errorfiles d_cfaeisdb.Err1,d_cfaeisdb.Err2;
RECORD 1;
DEFINE FILE=\\is-m-54lxx-fs12\DBP_PLANNING\Fast Load\FTP profile.txt;

The purpose of this series was to give you some basic queries that I use to provide me with a quick snapshot of how well tuned an EDW is from a workload analysis and database tuning perspective.

The four topics were (direct links are provided at the end of the article):

  • Part 1 - Excessive Use of String Manipulation Verbs
  • Part 2 - Analyze Secondary Index Usage
  • Part 3 - Statistics Analysis
  • Part 4 - Compression Analysis

Have you tried them yet??

I did some research and could not find a good Teradata SQL formatter. There is a 'Formatter' button in SQL assistant, but the output is a bit ugly. I wrote a small tool with lex & yacc which is able to format a Teradata query.

I will distribute this tool if anybody is interested.

Hello,

23/7/10 Urgent contract requirement ****EXCELLENT RATES******

Management of Enterprise Data Warehouse system in a highly dynamic environment. The person must be capable of applying innovative approaches to solving design and technical issues. Familiarity with a Teradata environment would be a definite plus.
*Experience with ODI or Sunopsis
*Development and data modelling, with strong Unix and SQL skills. Preferably with Teradata
*Redhat Linux hands-on experience
*Knowledge of ASG Rochade metadata management tool and Orsyp job scheduling tool is preferable. Training will be provided

Ok, so I shouldn’t even need to broach this topic, as I’m sure you have all heard it before: compress, compress, and compress.

Hello Everyone,

I have a brilliant opportunity for a Teradata specialist within a global company.

• Need to have several years of experience in DWH with strong expertise in Teradata database and Teradata Utility.
• Extensive experience in coding Teradata SQL, BTEQ, MLOAD, Fast Load and Fast Export.
• Should have expertise in requirement gathering/analysis, data modeling/design, performance tuning and development for DWH projects.
• Must have relevant experience working in a banking domain.

Time is one of the most powerful dimensions a data warehouse can support. Unfortunately it’s also one of the most problematic. Unlike OLTP environments that focus on only the most current versions of reference data, DW environments are often required to present data not only as it currently exists, but also as it previously existed. Implemented correctly, a data warehouse can support several temporal orientations, the three most common being “current,” “point-in-time,” and “periodic.” Implemented incorrectly, you will create a solution that will be impossible to maintain or support.

In Part 3 of this series, we will take a quick look at how statistics are implemented and maintained at your site.

Statistics Collection can be a complicated and very deep-dive topic, with discussions on the frequency of collection, whether to use sampled stats, automation strategies, etc. This analysis is not going to go that deep, it is a high-level look at the statistics on the tables, and I am looking for just two things:

  • Are statistics applied to the tables or missing?
  • For those that are applied, is there consistency in the application and collection process?

How could I select the values using "column name" and primary key (row) information of a table using SQL.

For example Table1 contains 3 columns (TrackID, var1 and var2) with values:

Table1
__________________
TrackID var1 var2
1 3 1.2
2 2 1
3 8 2.3

In Table2 have Attribute ("column name of Table 1") and TracID. How could I select the values from Table 1 based on input from Table 2.

Table2
________

Attrbute TrackID
var1 1
var1 2
var1 3
var2 1
var2 2
var2 3

The result will be

Result
----------
3
2
8
1.2
1
2.3

There are three SQL statements (a, b & c) below, each of which performs a left join. Can anyone please explain how the execution of these statements differs from the other?

a) select c.c_name, a.n_name
from retail.client c left join retail.area a
on c.c_nationkey = a.n_nationkey
and a.n_name is null;

How can I generate all permutation from the variables selected from different tables using SQL

For Example

In Table1 with Var1 has values:
Var1
1
2
3

In Table2 with Var2 has values:
Var2
4
5

In Table3 with Var3 has values:
Var3
7
8
9

Starting from TD 13.10, Teradata DIPUDT script creates the SQLRestrictedWords_TBF function and the SQLRestrictedWords view in the SYSLIB database that allow users and client products to query for Teradata SQL Restricted words.

there are two tables table1 and table2

table1 ---columns

C1 c2 c3 c4 c5

where c3 and c4 are datebegin and dateend

table2----columns

d1 d2 c5

where d1 and d2 are dategin and date end

now i need to join table1 and table2 based on c5

Hi, I'm new to Teradata but am in the process of converting all of my SQL to run on my new Teradata App db....

I have a SQL snippet loop query that runs on SQL Server but it will not run as is on Teradata. I think I need to do something with cursor but am unsure. Can someone tell me how to convert this statement to run on Teradata? Thanks in advance.

CD1:

UPDATE NFAA_TABLES.NFAA_REGIONCD
SET LoopSpan = 'A'
from NFAA_TABLES.NFAA_REGIONCD nf1
WHERE LoopSpan is null
and exists
(
select *
from NFAA_TABLES.NFAA_REGIONCD nf2
WHERE nf2.LoopSpan = 'A'

In Part 1 of this series, we looked at the Excessive use of String Manipulation Verbs in a query. Here is that link in case you missed it:

Business Intelligence Architect
Position Type: Full-Time, Permanent
Company Name: HSGI Inc.
Location: Atlanta, GA
Primary Skills: BI, Architect, Data Modeling, Data Analysis, SQL, Data Warehouse, Data Mart, Dashboards

Urgent Job Opportunity!
Only Serious Candidates Need to Apply! We can set up interviews for you immediately!
Please reply with ALL of the following information:
• An updated copy of your resume in a Word (.doc) Document
• Current and expected compensation
• A brief introduction type paragraph outlining your skills and experience relative to the Job Description

While organizations store temporal data that is in effect over a period of time, efficiently querying that data to provide business insights can be a challenge.

Hi every body,

I am working some very big tables (about 50 billions records). The table contains all transactions captured in any point of time (timestamp is used to store the transaction). It has some columns, including a varchar(250) contain the free text comment (but contain some codes in these free text). I have use Primary Partition Index based on the Rang_n Date extract from this timestamp, and defined some secondary indexes as well as join index on this big table. However, there is a request to do the following:

Actifact Corp is looking for a Teradata developer for a immediate contract position with our client in Orlando, FL. The contract is initially for 6 months but may be extended depending on candidate performance and client work.

Data Warehouses often contain data in effect for a time period, denoted by Start and End dates within a row. With time-period data comes a set of frequently asked business questions. For example, of home, auto, and life insurance policies, for what ranges of time have my customers 1) had zero policies active, or 2) at least one active, or 3) multiple active? Solutions to business questions of this type are not obvious using SQL. However, this session will show how to solve this class of problems efficiently with SQL instead of using stored procedures or exporting to another tool. See unique ways to apply correlated subqueries and the Ordered Analytical Functions SUM(), ROW_NUMBER(), and MIN() to this class of problems.

Hey,

Anyone know how to create a table using a view (Structure & Data)?

Also, is there anyway to retrieve the DDL for the tables or views we have access to?

Thanks,

-Ayyad

This book provides reference information about BTEQ (Basic Teradata Query), a general-purpose, command-based report and load utility tool. BTEQ provides the ability to submit SQL queries to a Teradata Database in interactive and batch user modes, then produce formatted results.