ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

Database
Enthusiast

ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

Dear All,

I am getting the subject error while running following code theough ODBC.

with t1(Account_Num, Acct_Status_End_Dt) As
(
Select Account_Num, Acct_Status_End_Dt from dp_view_t24_ibg.account_status_hist
where account_num in (select account_num from dp_tab.agreement where branch_party_id = '5520')
and substring(Account_Num from 1 for 1) = '5'
Group by Account_Num, Acct_Status_End_Dt
)
Select t2(Account_Num,
Sum(Event_Amt) = Tot_Amt,
Count(Event_Start_Dt) = Tot_Count)
from dp_view_t24_ibg.event
inner join t1 ON t1.Account_Num = t2.Account_Num
AND t2.Event_Start_Dt BETWEEN Acct_Status_End_Dt and Current_Date()

Please guide what i am doing wrong here.

Regards

KAM

5 REPLIES
Junior Contributor

Re: ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

There are multiple problems with your query, the error message is caused by Select t2(Account_Num this should probaby be Select t2.Account_Num, but there's no alias t2 defined, yet.

Additionally this Sum(Event_Amt) = Tot_Amt, should be Sum(Event_Amt) AS Tot_Amt, same for the next line.

Enthusiast

Re: ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

@dnoeth

Actually i want to select Account_Num and Account_Status_End_Dt from Account_Status_Hist table based on Account_Num from Agreement Table with condition that their bnrahc_party_id should be '5520' and Account_Num in account_status_hist table start with '5'. Then i want sum of transactions in these accounts starting from Account_Status_End_Dt till current date from Event table.

So that was the aim of this query please guide what steps should i take.

regards

KAM

Junior Contributor

Re: ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

Do the modifications I wrote (and add a final Group By), this results in a query like this:

with t1(Account_Num, Acct_Status_End_Dt) As
(
Select Account_Num, Acct_Status_End_Dt from dp_view_t24_ibg.account_status_hist
where account_num in (select account_num from dp_tab.agreement where branch_party_id = '5520')
and substring(Account_Num from 1 for 1) = '5'
Group by Account_Num, Acct_Status_End_Dt
)
Select t2.Account_Num,
Sum(t2.Event_Amt) AS Tot_Amt,
Count(t2.Event_Start_Dt) AS Tot_Count
from dp_view_t24_ibg.event AS t2
inner join t1 ON t1.Account_Num = t2.Account_Num
AND t2.Event_Start_Dt BETWEEN Acct_Status_End_Dt and Current_Date()
GROUP BY 1
Enthusiast

Re: ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

Thank you very much Sir,

Your suggested code worked fine.

Just need to ask two more things if you can please help.

1. The field Event_Amt contains Negative as well as positive number so their sum would not be accurate if sum command is applied. How can i converty the Event_Amt into abolute value before summing it.

2. Apparantly Current_Date() function was giving error e.g. "Expected some thing between "(" and ")". So i hardcoded the date in code is there a parameter required inside Current_Date() function.

Regrads

KAM

Enthusiast

Re: ODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name.

Oh found the abs() function and it worked.

Sum(abs(t2.Event_Amt))

Regards

KAM