0 - 42 of 42 tags for #sql

Hi,
 
I have the following SQL function and want to recreate it Teradata. Is this possible? Any suggestions welcome.
The function calcualtes the working days, excluding weekends and business holidays.
Thanks in advance,
Chris

select calendar_date as DATEO from sys_calendar.CALENDAR
where calendar_date between ?  and cast(? as date)+31
order by calendar_date

=Year(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value))) & "-" & iif(Month(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value)))>9,Month(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value))), "0" & Month(DateAdd("m", -5, DateValue(

Hi,
I wanted to know under what all circumstances/scenarious can we receive Error 2616. I am facing this issue while running a select query which is joining columns from multiple views.
Thank you.
Regards
Abdulaziz Shaikh

I am using SQL Server and for the security of my database i want to know when should i apply these methods on my database:

  1. Log Shipping
  2. Database Mirroring
  3. Transaction Replication
  4. point-in-time

I get the Error 2616 (Numeric overflow occurred duing computation) when I run the code below.  I developed 2 separate queries that each run.  When I put them in one query and use a UNION I get the error message.  1 side of the union returns 274 records and the other side returns 277 records.  I'm using Teradata SQL Assistant when I get this message.  We a

CREATE VOLATILE TABLE MYTBL  
 (
  PERIOD_START DATE NOT NULL,
  PERIOD_END DATE NOT NULL,
  EMPID INT NOT NULL,
  LOCID INT NOT NULL,
  LOCNME VARCHAR(50) NOT NULL,
  LOC_VST_DT DATE NOT NULL
 ) ON COMMIT PRESERVE ROWS;

INSERT INTO MYTBL VALUES ('2015-01-01', '2015-01-31', 12345, 98765, 'PLACE1', '2015-01-10');

Is there a way in SQL Server to find SPIDs and other informations that are causing blocking on SQL Server instance?

Hi, i am trying to query all the data from my table in batches, e.g i have 10000 rows that i want to collect by 10 callls to db, each one will bring 1000 rows. so i will end up with 10000 rows, without duplications and row missing. i dont have any uniqe index or column in this table. 

I'm trying to add a field from a different table to a derived query.  This is the first time I have tried to do this.  The query below was working unitl I added the bolded information below.  The error message is as follows:
SELECT Failed.  3707: Syntax error, expected something like ')' between 'soh' and '.'

How can I restore a database from .mdf file? I only have .mdf backup file.

Hello All. 
We have two tables as below. Both the tables have 1 column COLLECT_DATE_TIME Timestamp(1).
1. DB_DEV.ABCD
2. DB_TEST.ABCD
 

CREATE SANDBOX.SC2_VDGB  (
  MAIN_ID VARCHAR(12)  
, PLAN_NBR VARCHAR(9)  
, MEMBER_NAME VARCHAR(64)  
, CASE_NBR VARCHAR(9)  
...
, X8 VARCHAR(1)  
, XB VARCHAR(1)  
, XC VARCHAR(1)  
, XD VARCHAR(1)  
, DAHS_FACILITY_PROV_NBR VARCHAR(12)  
, DAHS_FACILITY_NAME VARCHAR(64)  
, PRODUCT VARCHAR(2) 
) 
PRIMARY INDEX (MAIN_ID);

Is there method in teradata to find number of occurence of a specific character in a string?
Like if string value =  ' Teradata is Relational Database'
I need to know how many time letter 'a' is present in the above string.
Do we have any inbuilt functions in Teradata or will Regular expression help me on this?
 

Hi ,
I want to merge following multiple insert - select statements into a single insert-select statement without using UNION/UNION ALL. Please suggest.
 
INSERT INTO Fact
SELECT DATE AS LOAD_DT
, key_id
, 200 AS S_id
, device_id
, seg_id
, insight_id
, 5040 AS m_id

I inherited the code below and am trying to add 2 new fields to the results (bolded below).  The code I'm using to join is also bolded below.  I'm sure it is something I'm dong that is causing me to get the following error message when I run this sql.  The error message says: Select failed 3810 Column / Paramete rfs_rv.pft.paymt_mdia_proc_sys_cde does not

I'm trying to INSERT INTO a table or CREATE TABLE AS from a SQL statement using the WITH form of a derived table.  Is this possible?

Hi,
I have customers transaction table visiting certain properties over several years. The dataset contains customerid, property_name, visited_date and has over 50million records.
I would like to aggregate the customer information into properties related as shown below

I have 3 prompts in my report: (1) Group Number, (2) State, and (3) Branches. The user selects the group number (1) and the prompt for State (2) is updated with all of the states the group does business in. Then the user selects either "null" or one of the states from the state prompt (2).

Hi!
I have created a SQL-statement that is in a string.
Ex:
'insert into MetadataTable.TestTable ' !!
'sel ''' !! trim(substr(Col.databasename,1,3)) !! ''' as Environment, ''' !!

trim(T1322.Id) !! ''' as Id, ''' !!

trim(T1324.RId) !! ''' as RId, ''' !!

SELECT  oute.*

FROM PDP_TMP.EV_SBSCRP_BTS_USG oute

WHERE  

CASE WHEN oute.PROD_TYPE= EVO

THEN oute.TotalDataCount  =  SELECT  MAX (inne.TotalDataCount)

                                                FROM PDP_TMP.EV_SBSCRP_BTS_USG inne

Hi,
I have a table with parent and child hierarchy. I need to frame an query to update an attribute in this table with respect to the value of its parent.
Parent Child Status 
A          B       Y
A          C       X
B          D       NULL
D          E       NULL
C           F       NULL

Hi All, 

I have a table as following 

Col1 col2 
A B 
B C 
C D 
P Q 
Q R 
R S 
X Y 
Y Z 
Z F 

Required output: 

Col1 Col2 Col3 
A B D 
B C D 
C D D 
P Q S 
Q R S 
R S S 
X Y F 
Y Z F 
Z F F 

drop table retail.distance;

 

drop table  retail.distance1; 

create table retail.distance

 (

 source_Place varchar(20),

 destination_Place varchar(20),

 distance  integer

 );

 

 insert into retail.distance values ('Pune', 'Mumbai',100);

Hi ,
I want to create a test cursor and loop through the result set performing specific computations.
What have I tried till now is something like this 
 

Hi ,
We have two tables with below data in given date intervals  and the output required is as single report  with its individual valid date intervals. Can anyone help on the query after joining Table A and Table B

Table A:

Cust_id   Currency   Start_date      End_date

1             USD         2014-01-01   9999-12-31

I have a table AAA  which has 3 columns as Month_No,Year_No and Profit.
I want to select next 15 months profit data along with month and year from table AAA.
Can anyone help me to form the teradata Query for this?
Example:
Table Name:AAA
Month_No      Year_No    Profit
2                     2011       10

Hi All,

Hi All,
I am bit stuck with a case for getting the exact sql. The scenario is we have to pick the price from Table1 with respect to Table2 and create an output. The price should be picked from the latest date and if we have duplicate price for same date then exact match must be considered first.

Hi all,
I need to know the syntax for calculating the day number of the year for a date in teradata SQL.

I have a table tab1 with structure as-

    columnName | datatype
          col1 | VARCHAR  
          col2 | VARCHAR
          col3 | VARCHAR
          col4 | VARCHAR
          col5 | VARCHAR

 and sample data as

Hi,
I need some help with some code below; I'm relatively new to Teradata SQL Assistant (vs: 13) - sample data below:
Customer   Number      Date         Time           Seq         Agent            Phone         Update Date            Last Call             Last Time

Hi all,
       I need to calculate 8 weeks of data is there any function related to this??
 

Hi All,
Can anyone tell me how to find the index size and table size using Ferret Utility..
Also what are the other methods to find out index size apart of creating the table with and without index and then take the difference of both..

Hello All,
I have created a dummy table and inserted one record in that table. When i simply select that record it gives me unwanted leading space in the answer set. 

CREATE TABLE dummy (a INTEGER);

INSERT INTO dummy VALUES (986);

 

SELECT '(' || TO_CHAR(a+1) || ')' FROM dummy;

 

SEL  CAD.CUST_ACCT_KEY, CATD.CUST_ACCT_MKT_DESC,  CASD.CUST_ACCT_STAT_CODE, ETD.RCVR_TYPE_DESC, ETD.EQUIP_BUS_CAT_DESC  

 

FROM CUST_ACCT_DIM AS CAD

  JOIN CUST_ACCT_STAT_DIM AS CASD 

     ON CAD.CURR_CUST_ACCT_STAT_KEY = CASD.CUST_ACCT_STAT_KEY

     

  JOIN CUST_ACCT_TYPE_DIM AS CATD 

The following query gives the higest #number count per day, which is 7 rows each for 1 day. I want to include HOURS column to the results set with changing the resulting result set.So I want to know at which hour the count was the highest? without listing all the hours. Is it possiable?

 

result should be as this:

 

how can I merge these two sql in to one query?

 

SELECT MONTH_BGN_DATE 

 FROM MONTHDAY_DIM 

 WHERE MONTHDAY_KEY = ADD_MONTHS(DATE , -4)) 

 

 SELECT  MONTH_END_DATE 

 FROM MONTHDAY_DIM WHERE 

 MONTHDAY_KEY = ADD_MONTHS(DATE , -3)) 

 

thanks.

 

How can I translate this loop into Teradata SQL?
 

DECLARE @logdate DATETIME  

DECLARE @hrinput1 INTEGER

DECLARE @hrinput2 INTEGER

SET @logdate = '2013-03-01' 

SET @hrinput1 = '0'

SET @hrinput2 = '1'

 

while @logdate <= '2013-04-01' 

BEGIN 

How can I translate the following ms sql to Teradat Stored Procedure?
 

DECLARE @logdate DATETIME  

DECLARE @hrinput1 INTEGER

DECLARE @hrinput2 INTEGER

SET @logdate = '2013-03-01' 

SET @hrinput1 = '0'

SET @hrinput2 = '1'

 

while @logdate <= '2013-04-01' 

BEGIN 

Hello,
 
I have some code that I wrote that joins 2 separate tables:
 
SELECT
A.*,
B.MTN_1
 
FROM
TABLE_1 AS A
 
JOIN
TABLE_2 AS B
ON A.ACCT_NUM=B.ACCT_NUM
 
WHERE A.MTN=B.MTN_1