Posted: Thu Jul 01, 2004 3:20 pm Post subject: SQL Help needed with Insert SQL
I am trying to insert a row into a table using the following SQl but every attempt thaT I made failed me. This SQL is being executed in a Teradata Environment hence the error message code may be different than what you will see from DB2. The error code is 3607 and the message is Too many values in a subquery. I will appreciate any help to make this insert work.
Thanks
Phillips
Code:
Insert into datadw.dq_customer_metrics
select 'customer', 'DATADW.customer', 'HH GrT 1 Distinct CustID', count(*),
a.period_end_dt
from DATADW.customer a
where household_id in
(select household_id, count(distinct(customer_id)) as nbr
from DATADW.customer v
where v.period_end_dt = 1040531
having nbr > 1
group by 1)A
group by 1, 2, 3, 5
;
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu Jul 01, 2004 3:51 pm Post subject:
okonita,
You have a couple syntax errors.
1. You are looking for HOUSEHOLD_ID, but your sub-select is having more than 1 column in the select statement. So you are basically matching 1 column(HOUSEHOLD_ID) with HOUSEHOLD_ID & COUNT(DISTINCT(CUSTOMER_ID)) which is error
2. DB2 does not you group by the col no: You need to give the column names.
Try this
Code:
INSERT INTO
DATADW.DQ_CUSTOMER_METRICS(SELECT B.COL1
,B.COL2
,B.COL3
,B.COL4
,B.COL5
FROM (
SELECT 'CUSTOMER' AS COL1
,'DATADW.CUSTOMER' AS COL2
,'HH GRT 1 DISTINCT CUSTID' AS COL3
,COUNT(*) AS COL4
,A.PERIOD_END_DT AS COL5
FROM DATADW.CUSTOMER A
WHERE HOUSEHOLD_ID IN (SELECT HOUSEHOLD_ID
FROM DATADW.CUSTOMER V
WHERE V.PERIOD_END_DT = 1040531
GROUP BY HOUSEHOLD_ID
HAVING COUNT(DISTINCT(CUSTOMER_ID)) > 1)) B
GROUP BY B.COL1, B.COL2, B.COL3, B.COL5)
;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
