All Forums Database
khawar.ameer 7 posts Joined 02/15
02 Aug 2016
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

dnoeth 4628 posts Joined 11/04
02 Aug 2016

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.

Dieter

khawar.ameer 7 posts Joined 02/15
02 Aug 2016

@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

dnoeth 4628 posts Joined 11/04
02 Aug 2016

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

 

Dieter

khawar.ameer 7 posts Joined 02/15
03 Aug 2016

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

khawar.ameer 7 posts Joined 02/15
03 Aug 2016

Oh found the abs() function and it worked.

Sum(abs(t2.Event_Amt))

Regards
 
KAM

You must sign in to leave a comment.