Teradata Forums / Analytical Applications / Teradata

Data Mining - Best Practise

STARTER — Tue, 20 Oct 2009 12:38:24 GMT

Hi,Can you give me pointers to best practise for Data Mining queries on Teradata data?Thanks in anticipation,Starter.

week issues

mad_about_coffee — Wed, 04 Nov 2009 12:23:54 GMT

Hello,I hope someone could help me with my task:Weekends = Fri, Sat, SunWeekend days = 6, 7, 1What I need to do is to identify the number of times a specific guest should win depending on how many weekends the guest stayed. The situation is if the guest stayed for 2 consecutive weekends (which could either be Fri+Sat or Sat+Sun), then that is already considered as 1 win. If on the next weekends the guest still hasn't checked-out, then that is already considered as 2 wins. If on the 3rd weekends the guest still hasn't checked-out, then that already is his 3rd winning... and so on as long the guest is still checked-in.In the attached xls file, I have placed examples of guests' stay dates (which are already given), and the number of winnings (which I have to find-out).I pray that someone will be able to help me on this... ;) thanks again!

How to search through multiple stored procedures??

ratu — Wed, 28 Oct 2009 19:11:38 GMT

Hi All,I am trying to search for dependencies in our database, and I have about 500 stored procedures to go through. Is there an easy way to do this?I compiled all the show procedures as follows:SHOW PROCEDURE database_name.procedure_name_1;SHOW PROCEDURE database_name.procedure_name_2;SHOW PROCEDURE database_name.procedure_name_3;SHOW PROCEDURE database_name.procedure_name_4;SHOW PROCEDURE database_name.procedure_name_5;etcDoes anyone know how I can save the result set into a table or export it to a text file where I can perform my searches on the keywords?Thanks!

Validate column data

David Korb — Tue, 27 Oct 2009 17:31:37 GMT

Hi,I'm trying to validate that each character in a varchar(100) row is between 0 and 9 on a 250M row table. Any rows that are not valid will be ignored in an insert statement into a base table. However, this is giving me a hard time. I tried using a tally table with substring comparison in an exists clause, but the data are just too huge. Is there an efficient way to validate my data?Thanks,David

teradata certification

ttf — Tue, 13 Oct 2009 01:42:31 GMT

I am planning to prepare for teradata certification and want to sit for Teradata basics V2R5 first. If there is anyone who has already given this teradata certification exam then please guide me how do I take preparation. It would be of great help if you can provide me sample questions and answers.

Inserting '?' into a TD table

manjeet — Tue, 15 Sep 2009 10:42:45 GMT

Hi all,How do i insert the special character '?' into a table.ThanksManjeet

Difference between select * from Table & select individual columns

az_maverick — Thu, 20 Aug 2009 14:17:18 GMT

Hello all, I am having trouble understanding difference select * from TABLE X or select a , b, c ( columns) from table X ( same table ) above.I was wondering if someone actually knows what is the difference at the Teradata DB Architecture level.Thanks,Aditi

Storing multiple values in a column

adash — Sun, 04 Oct 2009 23:27:58 GMT

Hello,I want to store multiple values in a column so that when i use it in IN clause all values are picked up in it as separate entities..What I mean from above is that: Suppose I have a table T1 with column C1, the data should be present as:Record_Type C11 100,200,3002 140,500,789,334,223,657I want to use C1 as follows:select * from T2 where outlet_id in (select c1 from T1 where record_type=1);As far as i know there is no array in Teradata.Let me know how it can be achieved in Teradata.Thanks in advance.

SQL query

srinu — Thu, 10 Sep 2009 20:23:48 GMT

Hi all, I am trying to create a new variable depending on already existing values in the table but got stuck.. here is the examplecolA colB new_col1 new_col2jet a 1 1jet b 1 1cat c 1 2cat d 1 2dog e 2 3hen f 3 4 I need to create new columns (new_col1 & new_col2) . I am looking to assign a common value for new_col1 if the values in colA are duplicates and values in colB are unique (i.e jet and cat should get the value of 1 in new_col1) and assign a separate value for each duplicate value in colA (i.e . jet should get 1 and cat should get 2 in new_col2). Could anyone please help me in getting this result? Really appreciate any help on this issue! Thanks in advance!Srinivas.

what lock is when inserting data

shubh — Thu, 22 Oct 2009 14:34:09 GMT

Hi,I want to know if I am inserting data from TBL1 to TBL2, what lock is on the TBL2.I want to make sure when data is being inserted to TBL2 it is still available to read but not with half full data.Ex -> 1. TBL2 is empty initially.2. My query runs, which say takes about 2 hrsinsert into TBL2select * from TBL1;now in these 2 hrs if a user fires a query against the TBL2 he/sheshould get: "0 rows returned".Please help.Thank you.Shubh

mbbs in ukraine

iglobal1 — Thu, 08 Oct 2009 20:31:02 GMT

Hi, Join the world recognized MBBS program in Ukraine. You can now earn your MBBS degree at an expense less than 30% by doing it in one of the most reputed universities in Ukraine with world class accreditation. * 50% Aggregate In Physics, Chemistry & Biology * 30% Cheaper * World Class Standards * Medical University Listed With International Medical Education Directory (IMED). KarabutsLogovenko Street,MykolayivUkraine - 16600+380930484422Website: http://www.karabuts.comEmail: info@karabuts.com

Data migration open source tool

ofdabler — Tue, 06 Oct 2009 11:12:43 GMT

Hello, We are trying to find an ETL tool open source. Basically, we need our software to perform ETL, data migration and data synchronization. The program should not be used on larger projects. A few open source tools are on the market. Some ideas? Thanks.

Averaging time field

indo81 — Wed, 05 Aug 2009 11:54:01 GMT

Hello there, I am encountering an error when I am trying to perform average function on a field with data type of time(0). For example: sel avg(end_time)from table1It gave error "5407: Invalid operation on an ANSI Datatime or Interval value"I tried: sel sum(end_time)/count(end_time)from table1This failed with the same error message due to the sum function.Has anyone encountered this problem before? If so, can someone be kindly offer a solution to this? Thanks in advanced,N

7502: A system trap was caused by UDF/XSP/UDM ALL_ETL_UDF

pgoutam — Wed, 23 Sep 2009 17:17:29 GMT

I wrote a UDF to read the string and if there are any control characters replace with spaceswhen I run the udf one time is working and next time its giving 7502 system trap error and if I run again its working, I am not sure what needs to fix, can anyone helpI am also pasting the codeselect myreplace(loss_desc) from xxx #define SQL_TEXT Latin_Text#include "sqltypes_td.h"#include #include #include #include #define MAX_STRING_LENGTH 256#define IsNull -1#define IsNotNull 0#define NoSqlError "00000"#define NUL '\0'void myreplace( char *inp_str, char *out_str, int *inp_strIsNull, int *out_strIsNull){ int i = 0, j = 0; char *tmp_str ; *out_str = '\0' ; memset(out_str, NULL, sizeof(out_str)) ; tmp_str = malloc(strlen(inp_str)*sizeof(char)); strcpy(tmp_str, inp_str) ; if (*inp_strIsNull == IsNull) { *out_strIsNull = IsNull; return; } *out_strIsNull = IsNotNull; *out_str = NUL; if (strlen(inp_str) == 0) { *out_str = '\0'; return; } while (tmp_str[i] != NULL ) { if (iscntrl(tmp_str[i]) ) { tmp_str[i] = ' '; out_str[j] = toupper(tmp_str[i]); } else if (tmp_str[i] == '^' ) { tmp_str[i] = ' '; out_str[j] = toupper(tmp_str[i]); } else { out_str[j] = toupper(tmp_str[i]); } ++i; ++j; } out_str[j] = '\0' ; free(tmp_str) ; return;}

Date Gap Analysis

ccotton333 — Thu, 20 Aug 2009 16:59:54 GMT

Here are the details I have a table that contains effective start/end dates. What I want to do is find where the gap is and only pull data on either side of that gap. for example:UPC effect_start effect_end123 2-7-2007 8-2-2007123 8-2-2007 11-2-2007123 11-2-2007 12-4-2008123 8-1-2009 8-4-2009123 8-4-2009 NULLIf I wanted to pull the most current items history information, I would only return the records from 8-1-2009. The indication is the mismatch between the effect_start and the effect_end fields. I have tried all sorts of ways to make this happen. My understanding of SQL deals with databases other than Teradata, and I can't recall ever having this much trouble doing this. I would think that there is a way to compare the data within a order recordset/answerset. Am I completely off base here? Can this and/or how do you perform this operation. Please understand that I would prefer to not build temp tables, but I will if I have to. All of my attempts have been without the use of temp tables.

Teradata SQL Assistant result set

JustMe — Wed, 16 Sep 2009 13:41:57 GMT

I am performing some stress tests of user queries, and the query text contains carriage returns. When I try to put this data in an Excel spreadsheet, it breaks up the query text. I tried checking the appropriate box on the EXPORT tab on SQL Assistant, but the carriage return remains in my result set. Any suggesstions?

how to export table values into a file?

rock — Mon, 16 Jun 2008 22:40:22 GMT

Hi All,Is it possible to export the table values generated from a select query into a file ???I have tried using the tools menu >> export option - but this only exports the queryman history values. I am looking at exporting the actual data in the in table to a file ??It would be great if any of u can help me with this...!!Thanks,Rock

[NCR][ODBC Teradata Driver] Timeout expired

tsnr — Tue, 01 Sep 2009 15:29:59 GMT

Hi ,I'm trying to connect from Siebel 7.8.2 application to Teradata database(V12) using Teradata ODBC driver.The application is on Solaris 8 server.I'm getting the following errorError Codes: OPR4ONWY:U9IM8TAC:OI2DL65PFile: odbcstatementimpl.cpp, Line: 186State: S1000. Code: 10058. [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: IM006 code: 0 message: [DataDirect][ODBC lib] Driver's SQLSetConnectAttr failed. [nQSError: 16001] ODBC error state: S1T00 code: 0 message: [NCR][ODBC Teradata Driver] Timeout expired. (S1000).I'm thinking there is some problem with odbc.ini file settings.Please help in identifying the correct issue.ThanksSiva.

Teradata SQL assistant: limiting the number of rows returned in "Export results"

m_nardone — Mon, 02 Oct 2006 13:44:31 GMT

I'm running some pregenerated queries that contain several (100+) subqueries, each of which is returning data to (spawning?) a separate answerset window.
All of the answersets are to be copied to a single document as testing documentation. I know I can limit the number of rows returned for each answerset window (tools/options/Answerset/max numbers...) but I have so many answersets, I can't cut/paste each answerset to a single text document. I'm using Export Results to return the results from all the queries directly to a single text document, but then I lose the ability to limit the number of rows returned in each query. Is there a way to do that? I want to Export Results, but only keep the first 50 rows from each answerset.

Thanks
Mat

OReplace function

jayshree909 — Mon, 24 Aug 2009 07:30:50 GMT

want to know can we use Oreplace function for eliminating a character

Deadlocks on Join Indexes- How+What+Why?

roy.kabir — Mon, 10 Aug 2009 17:48:06 GMT

I have a table, CUSTOMER and 3 Join Indexes- CUST_JI1, CUST_JI2, CUST_JI3 defined on the same, say for columns- cust_address, cust_phone, cust_email.During peak hours, users insert data into the CUSTOMER table via a web UI. Randomly, they suffer from time outs. Analysis showed that deadlocks are the culprit and PMON points to the 3 JIs i.e. CUST_JI1, CUST_JI2 and CUST_JI3 as the contention points. User 1 session waits up on CUST_JI1 WRITE LOCK of User 2 and so on and so forth.Per TD text, JIs cannot be updated directly, rather they are updated post insert/updates to the base table, CUSTOMER in this case.Since the earlier random time outs are occurring frequently now, someone, please do shed some light on how come lock contention happens on JIs?Thanks in advance.

How to store the result set from SELECT query using CLIv2 programming

shrihari_s — Fri, 07 Aug 2009 07:52:02 GMT

Hi,I'm new to Teradata and trying to write simple program using CLIv2 (Teradata version 12). Using the sample cli code provided by Teradata I could able to connect and execute the statements like CREATE TABLE, INSERT INTO.. But when I want to fetch the result set of a query SELECT * from SAMPLES.table1 I'm facing problem in coding. I dont know how to get the records from the query. Could any one help me in coding this. According to me after executing the query using DBCHCL(&result,cnta,&dbcarea), the result will be stored in dbcarea.fet_data_ptr; I dont know for which structure I have to type cast this, and also do I need to take care of the Data Types returned by the result set. In the Teradata sample program, it is reading character by character and storing in a file. I want to how to read a Database column. I sample example for fetching the records will be very helpful me.

Release Lock from a Table

Animesh_Dutta — Tue, 04 Aug 2009 02:20:50 GMT

I'm selecting rows from a table and then Inserting a row to that table.But after selecting - the table get locked - I don't know what kind of lock!And the Insert got hanged - neither failing nor showing any errors.If I abort that Insert - and going from another select - the query got hanged - means locked table!!!What should I do to release lock from a table - and why it's getting locked whenever select is happening?Any Idea - please help....If I do RELEASE LOCKING TABLE_NAME - showing "The USER doesn't have DUMP or RESTORE access to Table_Name"BT;SELECT * FROM TABLE_NAME;.IF ERRORCODE <> 0 THEN .GOTO ERRORS.IF ACTIVITYCOUNT <> 0 THEN .GOTO LOADFAILINSERT INTO TABLE_NAMEVALUES( VAL1);.IF ERRORCODE <> 0 THEN .GOTO ERRORSET;.LOGOFF;.QUIT 0.LABEL ERRORS.QUIT ERRORCODE.LABEL LOADFAIL.QUIT 100In this Insert is not happening - even after this simple select also not happening... though only the following select is running... But No Insert....LOCKING TABLE TABLE_NAME FOR ACCESS NOWAITSEL * FROM TABLE_NAME

small SQl query

psyam — Thu, 16 Jul 2009 05:05:50 GMT

I have a table with dataColumn 1 Column 2A 1A 2A 3B 1B 2 C 4c 6c 7i need to get the output as (based on group by first column)A 1 2 3B 1 2C 4 6 7Please let me know the SQL to achieve this

Lock Info in teradata

Manish Jain — Wed, 15 Jul 2009 08:22:17 GMT

Hi all,Is there any SQL with help of which we can get the information as which table and what kind of lock is acquired on it at any point of time in teradata.Your response will be appreciated.Thanks

Will MLOAD able to Insert data into Global Temporary Tables !!!

Animesh_Dutta — Thu, 09 Jul 2009 05:38:15 GMT

Is Teradata Utilities like allows to /update/delete the data into !I need to insert huge volume of data to a GTT through MLOAD via 1 session! Is that possible via single session! It seems MLOAD will not insert into GTT via multiple sessions.Inserting data into GTT via BTEQ or MLOAD (if possible) - which is preferable?

Question on calendar week

adash — Wed, 08 Jul 2009 09:12:23 GMT

Hi,In sys_calendar.calendar the weeks start from sunday and end on saturday.But I have a requirement where the weeks should start on monday and end on sunday (irrespective of the year).Which means if the date is 2009-12-31 then its begin date should be 2009-12-28 and end date 2010-01-03.and the next week should start from 2010-01-04 and end on 2010-01-10 and so on.I wanted to know how we can achieve this in TD.Thanks in advance.

Another Question regarding DBC.AMPUSAGE

Random_thought — Thu, 02 Jul 2009 10:52:21 GMT

When usage is logged in AMPUSAGE, and we include the &D&H in the Account name, does the CPU and Disk IO get logged against multiple Days and Hours if the same unit of work spans that far?IE if I run a query that runs for 7 hours at 9 am, does it log an entry just for the 09070209 or 7 entries showing the CPU and disk io for those hours?Random

dbc.ampusage

rvp — Mon, 01 Jun 2009 17:36:14 GMT

What is the unit for CPU time in dbc.AMPUSAGE?Is it seconds ?thanks,rvp

customer segmentation

vas_srini — Mon, 22 Jun 2009 14:51:02 GMT

Hi,I have a daily aggregate table, which gives details about customer's activity - existing, new etc. There is a new requirement to identify customers who are "reactivated" within a certain time period. To pre-build an aggregate table for reactivation customers based on a specific time period is easy - say 45 or 60 or n # of days. This is easy..But there is a need to build an aggregate table to find all "reactivation" customers let us say in the last 2 weeks or last 3 weeks or last 3 days. This means that a dynamic list has to be prepared using daily level aggregation and it wont be a static table. Is there an easy way of building it in Teradata? I think in Oracle, we can use materialized views.Appreciate your response.Thanks,Vas

Levenstein Distance Algorithm

Animesh_Dutta — Tue, 16 Jun 2009 07:08:18 GMT

Levenstein Distance Algorithm is a famous algorithm used for measuring the amount of difference between two sequences (i.e., the so called edit distance).The Levenshtein distance between two strings is given by the minimum number of operations needed to transform one string into the other.This algorithm uses Arrays - which is the main challenge to be applied in Teradata Stored ProcedureCan you please anyone give any code to use this in Teradata Stored Procedure/Function or SQL!Oracle PL/SQL Code is available - that uses Arrays. How do I implement Arrays in Teradata? FYI : [url=http://en.wikipedia.org/wiki/Levenshtein_distance][/url]

fas load/Deadlock issue

Manish Jain — Tue, 30 Jun 2009 09:11:27 GMT

Hi,We use one of the ETL tool to load data in Warehouse. A simple process is as followA---------->flat file-------->C$_A---------Target A(Oracle) (T.Database) (TD)Now problem is we use fastload to load table C$ from a flat file. This is for sure that C$_A is access by only one user at a time.ETL jobs are failing coz of deadlock on table C$ when fastload try to access this table.I can ensure that none of the other user was accessing the C$ table during loadingAny clue will be highly appreciated.

I have problems with error 1920.Service NCR Put Port Management Service PUTPortMgrSvc

chuotchit — Mon, 02 Jun 2008 06:32:57 GMT

I have problems with error 1920.Service NCR Put Port Management Service (PUTPortMgrSvc) failed to start.Verify that you have sufficient privileges to start system sevices.But I use Administrator account for Set Up PUT- Parallel Upgradge tool program.

IS-NUMERIC functions

flaska — Thu, 06 Jul 2006 12:47:58 GMT

Hello,
this is my problem:
I need to find out with a query, all the non numeric charaters in a field. The field is a varchar but should contain only telephone number, does exist a ISNUMERIC funtions????

select * from table where not field is numeric

Thank you
regards

Random values

mad_about_coffee — Fri, 05 Jun 2009 06:48:29 GMT

Hi,I hope somebody can help me with my task. We are currently in the part of transitioning from oracle to teradata, and the problem that I currently have and which I really cannot identify is on how to produce random values in teradata the same way it was produced in oracle. Confusing but I have copied the results that I wanted to produce below: ORACLE code:dbms_random.value(0,1) as random_nbr TERADATA code:random(0,1) as random_nbr RESULTS:--------ORACLE--------vs-------TERADATARow1: 0.546476429------------------1Row2: 0.095457194------------------0Row3: 0.254218001------------------1Row4: 0.054972349------------------0Row5: 0.978196729------------------1 Thank you sooo much in advance, hoping that I can get the same result as the oracle's!

Remove '-' from a column

Meenakshi S — Tue, 11 Nov 2008 04:56:35 GMT

I have a phone number column which has records like '111-222-333' . I need to remove the '-' from each phone number. Any suggestions?

volatile Table

rajdaksha — Mon, 01 Jun 2009 05:22:15 GMT

HiAnybody please explain volatile Table in teradata.Bcoz i have a couple of questions about for the same.

C and Teradata

rvp — Thu, 28 May 2009 21:15:27 GMT

The environment is Unix, AIX.I am new to Teradata and C Program. Can someone tell me how to logon to Teradata from C Program, I need to write a C program to do some Table updates in Teradata. Can you please share some information/sample code on the same?Thanksrvp

Teradata Services

rajdaksha — Mon, 25 May 2009 06:01:56 GMT

HiI have installed the teradata express edition 12.0. i got message from service control. Msg:-- "reboot system to start teradata services"please help on the same.byraj

Views or Tables

svyschd — Wed, 27 May 2009 11:38:35 GMT

I have to extract data from 3 huge production tables which have the data at week level and needs to be transformed to day level. I need this data extract to do some additional processing based on it using a bteq script. Is it better to have the data extracted during the bteq script execution process or is it better to have a view created for these using the following sqls and get the data from the views during bteq execution process? Which one will give us better performance?query 1:-------- SELECT TBL1.SKU,TBL1.STORE,CAL.DT, SUM( CASE CAL.DAY_OF_WK WHEN 1 THEN TBL1.SAT_SH_QTY WHEN 2 THEN TBL1.SUN_SH_QTY WHEN 3 THEN TBL1.MON_SH_QTY WHEN 4 THEN TBL1.TUE_SH_QTY WHEN 5 THEN TBL1.WED_SH_QTY WHEN 6 THEN TBL1.THU_SH_QTY WHEN 7 THEN TBL1.FRI_SH_QTY END ) FROM TABLE123 TBL1 JOIN CALENDAR_DAY CAL ON TBL1.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = TBL1.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE TBL1.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY TBL1.SKU,TBL1.STORE,CAL.DT HAVING SUM( CASE CAL.DAY_OF_WK WHEN 1 THEN TBL1.SAT_SH_QTY WHEN 2 THEN TBL1.SUN_SH_QTY WHEN 3 THEN TBL1.MON_SH_QTY WHEN 4 THEN TBL1.TUE_SH_QTY WHEN 5 THEN TBL1.WED_SH_QTY WHEN 6 THEN TBL1.THU_SH_QTY WHEN 7 THEN TBL1.FRI_SH_QTY END ) <> 0; query 2:---------SELECT SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_QTY WHEN 2 THEN SUN_QTY WHEN 3 THEN MON_QTY WHEN 4 THEN TUE_QTY WHEN 5 THEN WED_QTY WHEN 6 THEN THU_QTY WHEN 7 THEN FRI_QTY END ) UNITS, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_AMT WHEN 2 THEN SUN_AMT WHEN 3 THEN MON_AMT WHEN 4 THEN TUE_AMT WHEN 5 THEN WED_AMT WHEN 6 THEN THU_AMT WHEN 7 THEN FRI_AMT END ) RETAIL FROM POS SLS JOIN CALENDAR_DAY CAL ON SLS.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = SLS.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE SLS.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE HAVING SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_QTY WHEN 2 THEN SUN_QTY WHEN 3 THEN MON_QTY WHEN 4 THEN TUE_QTY WHEN 5 THEN WED_QTY WHEN 6 THEN THU_QTY WHEN 7 THEN FRI_QTY END ) <> 0; query 3--------- SELECT INV.SKU,INV.STORE,CAL.DT, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_OH_QTY WHEN 2 THEN SUN_OH_QTY WHEN 3 THEN MON_OH_QTY WHEN 4 THEN TUE_OH_QTY WHEN 5 THEN WED_OH_QTY WHEN 6 THEN THU_OH_QTY WHEN 7 THEN FRI_OH_QTY END ) OH_UNITS, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_OH_QTY * RETAIL_AMT WHEN 2 THEN SUN_OH_QTY * RETAIL_AMT WHEN 3 THEN MON_OH_QTY * RETAIL_AMT WHEN 4 THEN TUE_OH_QTY * RETAIL_AMT WHEN 5 THEN WED_OH_QTY * RETAIL_AMT WHEN 6 THEN THU_OH_QTY * RETAIL_AMT WHEN 7 THEN FRI_OH_QTY * RETAIL_AMT END ) OH_RETAIL FROM INVTBL INV JOIN CALENDAR_DAY CAL ON INV.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = INV.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE INV.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY INV.SKU,INV.STORE,CAL.DT;