Joing two files by Numeric and alpha field.

[Former Member]

I asked this question yesterday and thought it complete. Unfotunately, I don't know SQL as well as I thought I did. I nedd to link two files together. In the Address Book Master(F0902), I need to link ABAN8(numeric Field) to GBSBL(Alpha Field) in the Account Balances File(F0902).

I was not sure how to convert the correct field and then how to link them. Below is the SQL that I have built. It is missing the convert and the Link.

Thanks again, Rick

SELECT "F0101_Address_Book_Master"."ABAN8_Address_Number", "F0101_Address_Book_Master"."ABALPH_Alpha_Name", "F0101_Address_Book_Master"."ABAT1_Search_Type"

FROM "F0101_Address_Book_Master" "F0101_Address_Book_Master"

WHERE ("F0101_Address_Book_Master"."ABAT1_Search_Type"='E' OR "F0101_Address_Book_Master"."ABAT1_Search_Type"='X')

SELECT "F0902_Account_Balances"."GBAN01_Net_Posting_01", "F0902_Account_Balances"."GBFY_Fiscal_Year", "F0902_Account_Balances"."GBSBL_Subledger"

FROM "F0902_Account_Balances" "F0902_Account_Balances"

WHERE "F0902_Account_Balances"."GBFY_Fiscal_Year"=11

[Former Member · Accepted solution]

Not clear what your database is assuming its SQL server

SELECT "F0101_Address_Book_Master"."ABAN8_Address_Number", "F0101_Address_Book_Master"."ABALPH_Alpha_Name", "F0101_Address_Book_Master"."ABAT1_Search_Type", "F0902_Account_Balances"."GBAN01_Net_Posting_01", "F0902_Account_Balances"."GBFY_Fiscal_Year", "F0902_Account_Balances"."GBSBL_Subledger"

FROM "F0101_Address_Book_Master" "F0101_Address_Book_Master"

inner join "F0902_Account_Balances" "F0902_Account_Balances" on "F0101_Address_Book_Master"."ABAN8" = cast("F0902_Account_Balances" ."GBSBL" as int)

WHERE ("F0101_Address_Book_Master"."ABAT1_Search_Type"='E' OR "F0101_Address_Book_Master"."ABAT1_Search_Type"='X')

and "F0902_Account_Balances"."GBFY_Fiscal_Year"=11

again assuming "F0101_Address_Book_Master"."ABAN8" is in fact an int, change cast to numeric if appropriate

Ian

[Former Member · Accepted solution]

Thank you. It is working.

Rick