Query disobeying conditions?

General
Enthusiast

Query disobeying conditions?

Hi,

I have a query with a subquery that selects Id numbers from a table based on several conditions.

Those Id Numbers are then used in an Update statement to populate a field with a value.

The problem I have is that the conditions I have created are being ignored.

Firstly I create a table to show what type of services they have on their account - there can be 1 or more services on 1 account.

So for example, Account Number 123456 could have a homephone service, a mobile (cell phone) & an Internet service. 

The table 'Account_Services' is therefore created as ;

Create Table Account_Services as 

( Select Account_Number, Service_Type From Table 'A'
Group by Account_Number )

With Data
Primary Index(Account_Number, Service_Type);

Next, I write an Update query using this table;

UPDATE TABLE A

SET FIELD1 AS 'HMI'

WHERE ACCOUNT_NUMBER IN

(SELECT ACCOUNT_NUMBER

FROM Account Services

Where Service_Type IN ( 'Homephone', 'Mobile', 'Internet' ) )

The Update happens but I find that while the Accounts with these 3 Service Types are updated, other Accounts are also updated that have other service types? 

I thought I'd try to stop this by adding another condition as per below;

-- added to subquery after first condition

And Service_Type NOT IN ( 'Service_Type1' ,  'Service_Type2' ,  'Service_Type3' )

This still doesn't stop the incorrect updates happening??

Can anyone suggest what's going on?

I also checked for Nulls in Service_Type & there aren't any - I added the clause 'AND Service_Type IS NOT NULL'

But from here I'm stuck ...

11 REPLIES
Enthusiast

Re: Query disobeying conditions?

Hi,

It didnt ignore anything.

UPDATE TABLE A

SET FIELD1 AS 'HMI'

WHERE ACCOUNT_NUMBER IN

(SELECT ACCOUNT_NUMBER

FROM Account Services 

Where Service_Type IN ( 'Homephone', 'Mobile', 'Internet' ) )


You are saying here update all the rows for a given account number if that account number has any one of the service as homephone or mobile or internet, irrespective of the service_type in target table.

If you want to update only the the service_type in target table include that also in the where clause like below

UPDATE TABLE A

SET FIELD1 AS 'HMI'

WHERE ACCOUNT_NUMBER IN

(SELECT ACCOUNT_NUMBER

FROM Account Services 

Where Service_Type IN ( 'Homephone', 'Mobile', 'Internet' ))

 

and Service_Type in ( 'Homephone', 'Mobile', 'Internet' )
Enthusiast

Re: Query disobeying conditions?

Thanks barani_sachin,

That makes sense but Table 'A' doesn't have a 'Service_Type' field.

Are your brackets correct? Do you mean to have the second

Service_Type in ( 'Homephone', 'Mobile', 'Internet' 

as a filter from table 'A'?

Table 'A' does however have the Fields Mobile, Homephone, & Internet, these contain the counts for those Service_Types.

Any further advice? I think you're very close to solving this.

Thanks

Enthusiast

Re: Query disobeying conditions?

I've now changed the target table to a table that has a Service_Type on each row & added the Updatable field 'FIELD1' field to accept the new Update value. 

As there can be > 1 Service Type on an Account, I want the Update statement to add (for this example) 'HMI' to the FIELD1 field where the Account Number has Service_Types of Homephone AND a Mobile AND an Internet service but NO other types of Service. 

I tried your script barani_sachin as per below but it is missing some Service_Types & adding others so I must be doing something wrong.

UPDATE TABLE_B

SET FIELD1 = 'HMI'    

WHERE  Accnt_Num IN

(

SELECT  Accnt_Num

FROM Account_Services

WHERE Service_Type IN ( 'Homephone', 'Mobile', Internet' ))

AND Service_Type IN  ( 'Homephone', 'Mobile', Internet' )

Any suggests from here??

Thanks

Teradata Employee

Re: Query disobeying conditions?

The semantics of your question are not clear to me. Do you want accounts set to 'HMI' IFF the account has each of those attributes and no other attributes?

the subquery you have does not answer that question. It gets a list of accounts that have any of H, M or I and does not exclude accounts which have other attributes in addition to one or more of H, M or I.

To get exactly H, M and I and no others, more logic is required. Eg in your CREATE TABLE AS (not tested):

CT Account_Services AS (SELECT account_number, MAX(CASE WHEN service_type='homephone' THEN 1 ELSE 0) AS H, MAX(CASE WHEN service_type='internet' THEN 1 ELSE 0) AS I,MAX(CASE WHEN service_type='mobile' THEN 1 ELSE 0) AS M GROUP BY account_number) Unique Primary Index (account_number);

Then your update would be:

UPDATE A SET Field1='HMI' WHERE account_number IN (Select account_number FROM Account_services WHERE H=1 AND I=1 and M=1);

Of course you don't really need the create table, you can substitute the query from the CT instead of the subquery in the update and add a HAVING clause with the "WHERE H=1 AND I=1 and M=1" condition.

Enthusiast

Re: Query disobeying conditions?

Hi ToddAWalter,

Thank you!!

That has removed all the 'vagueness' I had in the query, great solution, Cheers 

I have created the table as you have advised I am getting a datatype error though when I try to do the Update.

I think it's because the below isn't creating an Integer field so when I try to update I get a failed 3535 - A Character string failed conversion to a Numeric value. Can you assist here? 

?

1

MAX(CASE WHEN service_type='homephone' THEN 1 ELSE 0) AS H



Also, I'm curious as to the purpose of using the MAX Function? Wouldn't this line still work without the 'MAX' part?

Thanks again




Teradata Employee

Re: Query disobeying conditions?

I guess you will have to define the columns in the create table statement with the datatypes. 

The MAX is required to allow all the flags to be grouped onto one row for the account. 

Enthusiast

Re: Query disobeying conditions?

Thanks again,

Last question, does this forum have a way to thank someone?

Senior Apprentice

Re: Query disobeying conditions?

Without the GROUP/MAX you get one row per account_number/service_type, so this updates all accounts where any of those service_types exists. 

There's another solution to get an account where all service_types exist:

UPDATE A
SET FIELD1 = 'HMI'
WHERE ACCOUNT_NUMBER IN
(
SELECT ACCOUNT_NUMBER
FROM a
WHERE Service_Type IN ( 'Homephone', 'Mobile', 'Internet' )
GROUP BY 1
HAVING COUNT(*) = 3
)

This assumes that the combination account_number/service_type is unique, otherwise simply replace COUNT(*) with COUNT(DISTINCT Service_Type).

Dieter

Enthusiast

Re: Query disobeying conditions?

Thanks Dieter.

I'm still struggling with the datatype error when I try to do the update part that ToddAWalter has kindly provided.

I think the datatype created (BYTEINT) in the below line is causing the error so I want to force it to make the field an INTEGER datatype;

MAX(CASE WHEN service_type='homephone' THEN 1 ELSE 0) AS H

Is there a way to define the datatype to INTEGER during the Create table process that ToddAWalter describes?

Thanks