sqlite join returning null even when both subqueries says otherwise

Question

I'm trying to run join on two tables in a sqlite database using subqueries(with/as). When I run the subqueries independently the value where ON clause is working exists in both tables yet when I apply a LEFT OUTER JOIN it gives null for those.

    
with transactionsData as (select (fee+amount) as chargedToCustomer,cast(substr(created,1,2) as decimal) as month,cast(substr(created,4,2) as decimal) as day from cardPayInvoices),
invoicesData as (select "Invoice Date",cast(substr(Amount,2) as decimal) as amountOnInvoice,
CASe substr(customerExcels."Invoice Date",1,3)
    WHEN "Jan"
        Then 1
    WHEN "Feb"
        Then 2
    WHEN "Mar"
        Then 3
    WHEN "Apr"
        Then 4
    WHEN "May"
        Then 5
    WHEN "Jun"
        Then 6
    WHEN "Jul"
        Then 7
    WHEN "Aug"
        Then 8
    WHEN "Sep"
        Then 9
    WHEN "Oct"
        Then 10
    WHEN "Nov"
        Then 11
    WHEN "Dec"
        Then 12
END monthFromDate           ,CAST( REPLACE(SUBSTR(customerExcels."Invoice Date",5,2),",","") as decimal) as dateOfMonthOnInvoice
 from customerExcels  where "Customer Group"="General" or "Customer Group" like "%OT LOGGED%")


select      distinct t.*,a.* from transactionsData  AS t LEFT JOIN invoicesData as a ON t.month=a.monthFromDate AND t.day=a.dateOfMonthOnInvoice AND t.chargedToCustomer=a.amountOnInvoice

I have tried literally everything I could think of, I've checked for trailing blankspaces and what not and yet i cant find the solution

table a(from subquery)

table t(from subquery)

Scheme

CREATE TABLE cardPayInvoices ( 
[id] nvarchar(500) ,
[created] nvarchar(500) ,
[description] nvarchar(500) ,
[currency] nvarchar(500) ,
[fee] nvarchar(500) ,
[amount] nvarchar(500) ,
[origin] nvarchar(500) ,
[originId] nvarchar(500) )



CREATE TABLE customerExcels ( 
[Invoice #] nvarchar(500) ,
[Invoice Date] nvarchar(500) ,
[Bill to Name] nvarchar(500) ,
[Customer Group] nvarchar(500) ,
[Amount] nvarchar(500) )


dummy values: cardPayInvoices 
insert into cardpayinvoices(id,created,description,currency,fee,amount,origin,originID) values(
"xxxx"  "07/01/2020 23:47:00"   "Capture (€294.30)" "EUR"   "20.86" "273.44"    "Transaction"   "xxxx")


customerExcel
insert into customerExcel("invoice #","invoice Date","Bill To Name","Customer Group","Amount") values(
"IT20-xxxx" "May 1, 2020 6:53:49 AM"    "ABC DEF"       "General"   "€103.79"),
("EN20-xxxx" "May 1, 2020 9:29:53 AM"   "GEH  IJK"  "General"   "€103.20"),
(EN20-xxxx May 1, 2020 1:41:18 PM   LMN OPQ     NOT LOGGED IN   €106.75)

user_10590072 · Answer

This took longer that I like to admit but after endless hours i found the solution ROUND(fee+amount,2)

I am using DBSQLITE for displaying the result of queries and it always rounds it off to two digits on display so 15.99 and 15.99000000001 (in second table) was both shown as 15.99 yet on comparison at ON clause of my JOIN statement it failed.

Had to run the query through python's sqlite module and get the output to figure out what was wrong. Lesson learned: never trust dbSqlite or any other sqlite DB explorer tool.

sqlite join returning null even when both subqueries says otherwise

1 Answers