CODEWITHSUNDEEP
sqlnetezza
Merrin
Merrin

Reputation: 675

Is there a limit on IN clause for netezza tables?

create table accounts as
select account_name, count(company) from tmp_table
where account_name in (select account_name from test_accounts)
group by account_name;

select * from accounts;

This query only fetches 12 records whereas it should have been 50 in my case, because there are 50 records in test_accounts.

I tried multiple things.

create table accounts as
select account_name, count(company) from tmp_table
where account_name in (list the name of all accounts in test_accounts)
group by account_name;

create table accounts as
select account_name, count(company) from tmp_table
where (added every account name in test_accounts with an OR clause)
group by account_name;

In all cases, I just got 12 records.

If I query tmp_table they are all there.

What did I miss?

Appreciate any help.

Upvotes: 0

Views: 3325

Answers (1)

Niederee
Niederee

Reputation: 4295

I would try the following. Depending on the datatypes of account_name I would also consider converting all the data to the same case using a upper or lower if it isn't an issue of case then I would suspect that you have spaces or other characters which need to be trimmed. 

select ta.account_name
,nvl(tt.account_name,'No Match Found') as tmp_tbl_acct_nm
, count(tt.company) from (select account_name from test_accounts) ta  left outer join
 tmp_table tt
on trim(both from lower(ta.account_name))=trim(both from lower(tt.test_accounts))
group by ta.account_name,nvl(tt.account_name,'No Match Found');

Upvotes: 1

Related Questions