Variable type conversion in where clause

Hi,

I have the attached as part of my WHERE clause
 
 The "GL_Calendar_Details_"."Fiscal_Period_"  and  "GL_Calendar_Details_"."Fiscal_Year_" fields are int32.

I am not getting any results returned probably because the MONTH and YEAR functions are returning a varchar type.  How can I modify my code?

Thanks,
Bruce
and "General_Ledger_Transactions_"."DB-NAME"	               = "GL_Calendar_Details_"."DB-NAME"
   and MONTH("General_Ledger_Transactions_"."Effective_Date_") = "GL_Calendar_Details_"."Fiscal_Period_"
   and YEAR("General_Ledger_Transactions_"."Effective_Date_") = "GL_Calendar_Details_"."Fiscal_Year_"
   and "General_Ledger_Transactions_"."Entity_" = "GL_Calendar_Details_"."Entity_"

Open in new window

pelicansysAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>because the MONTH and YEAR functions are returning a varchar type
no, the do return integer data also.

can you show the full table creation script, please?
0
 
pelicansysAuthor Commented:
Attached is the complete select stmt.

Thanks
select "General_Ledger_Transactions_"."DB-NAME" + '-' + "General_Ledger_Transactions_"."Account_" as "Account_Unique_",
       "General_Ledger_Transactions_"."Project_" as "Project_",
       "General_Ledger_Transactions_"."Entity_" as "Entity_",
       "General_Ledger_Transactions_"."Account_" as "Account_",
       "General_Ledger_Transactions_"."Transaction_Amount_SRC_" as "Transaction_Amount_SRC_",
       "General_Ledger_Transactions_"."Transaction_Amount_DW_" as "Transaction_Amount_DW_",
       "General_Ledger_Transactions_"."Transaction_Amount_TX_" as "Transaction_Amount_TX_",
       "General_Ledger_Transactions_"."DB-NAME" as "DB-NAME",
       "Accounts_"."Account_Type_" as "Account_Type_",
       "Accounts_"."Account_Description_" as "Account_Description_",
       "Accounts_"."Account_" + ' (' + "Accounts_"."Account_Description_" + ')' as "Account+Description",
       "Entities_"."Entity_Name_" as "Entity_Name_",
       "Sub_Accounts_"."Sub_Account_" as "Sub_Account_",
       "Sub_Accounts_"."Sub_Account_Description_" as "Sub_Account_Description_",
       "Cost_Centers_"."Cost_Center_" as "Cost_Center_",
       "Cost_Centers_"."Cost_Center_Description_" as "Cost_Center_Description_",
       "Base_Currency_"."Currency_" as "Currency_",
       "General_Ledger_Transactions_"."Effective_Date_" as "Effective_Date_",
	MONTH("General_Ledger_Transactions_"."Effective_Date_") as "Fiscal_Period_",
	YEAR("General_Ledger_Transactions_"."Effective_Date_") as "Fiscal_Year_",
	"General_Ledger_Transactions_"."DB-NAME" as "DB-NAME"
      
from   "PUB"."General_Ledger_Transactions_",
       "PUB"."Base_Currency_",
       "PUB"."Cost_Centers_",
       "PUB"."Sub_Accounts_",
       "PUB"."Entities_",
       "PUB"."Accounts_",
       "PUB"."GL_Calendar_Details_",
       "PUB"."GL_Calendar_" 	
 
where  "General_Ledger_Transactions_"."DB-NAME"             = "Base_Currency_"."DB-NAME" (+)
 
   and "General_Ledger_Transactions_"."DB-NAME"             = "Cost_Centers_"."DB-NAME" (+)
   and "General_Ledger_Transactions_"."Cost_Center_"        = "Cost_Centers_"."Cost_Center_" (+)
 
   and "General_Ledger_Transactions_"."DB-NAME"             = "Sub_Accounts_"."DB-NAME" (+)
   and "General_Ledger_Transactions_"."Sub_Account_"        = "Sub_Accounts_"."Sub_Account_" (+)
 
   and "General_Ledger_Transactions_"."DB-NAME"             = "Accounts_"."DB-NAME" (+)
   and "General_Ledger_Transactions_"."Account_"            = "Accounts_"."Account_" (+)
   and "General_Ledger_Transactions_"."Account_"            = '21000'
 
   and "General_Ledger_Transactions_"."DB-NAME"	               = "GL_Calendar_Details_"."DB-NAME" (+) 
   and MONTH("General_Ledger_Transactions_"."Effective_Date_") = "GL_Calendar_Details_"."Fiscal_Period_" (+)
   and YEAR("General_Ledger_Transactions_"."Effective_Date_") = "GL_Calendar_Details_"."Fiscal_Year_" (+)
   and "General_Ledger_Transactions_"."Entity_" = "GL_Calendar_Details_"."Entity_"
 
 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
which is not what I asked for :)
note: try to get "updated" on the OUTER JOIN syntax, the (+) stuff is
1) quite unreadable
2) subject to get less performant results

however, it shall not influence the results of the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.