Android Question [Solved] SQLite UNION ALL problem

[makis_best]

Hi

I execute one SQL script and really confuse me....
The select I execute is this one

SQlScript = $"SELECT 1 AS RANK, Date('${BDate}', '-1 Day') AS RegistrationDate, DocumentCode,
                    'Εξ απογραφής' AS LongComment,    0 AS Debit, 0 AS Credit, Balance, 'First' AS MaxDate
                    FROM LOCAL_EGM_Kartela
                    WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}' AND DocumentCode = 'Parastatiko Apografis'
                    AND Date(RegistrationDate) = (SELECT BeginDate FROM LOCAL_EGM_YEARS WHERE BeginDate = '${BDate}')
                    UNION ALL
                    SELECT 2 AS RANK, RegistrationDate, DocumentCode, LongComment, Debit, Credit, Balance, '' AS MaxDate
                    FROM LOCAL_EGM_Kartela
                    WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}'
                    AND Date(RegistrationDate) BETWEEN '${BDate}' AND '${EDate}'
                    AND DocumentCode <> 'Parastatiko Apografis' AND DocumentCode NOT LIKE 'ΑΧΓ%' AND DocumentCode NOT LIKE 'ΑΧΕ%'
                    ORDER BY RANK, RegistrationDate"$

When I run it on DB Browser for SQLite the result is correct.

When I execute it in B4A the result is the same minus the first line.
Always the first line is missing.

The code I run at B4A is

SQlScript = $"SELECT 1 AS RANK, Date('${BDate}', '-1 Day') AS RegistrationDate, DocumentCode,
                    'Εξ απογραφής' AS LongComment,    0 AS Debit, 0 AS Credit, Balance, 'First' AS MaxDate
                    FROM LOCAL_EGM_Kartela
                    WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}' AND DocumentCode = 'Parastatiko Apografis'
                    AND Date(RegistrationDate) = (SELECT BeginDate FROM LOCAL_EGM_YEARS WHERE BeginDate = '${BDate}')
                    UNION ALL
                    SELECT 2 AS RANK, RegistrationDate, DocumentCode, LongComment, Debit, Credit, Balance, '' AS MaxDate
                    FROM LOCAL_EGM_Kartela
                    WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}'
                    AND Date(RegistrationDate) BETWEEN '${BDate}' AND '${EDate}'
                    AND DocumentCode <> 'Parastatiko Apografis'
                    ORDER BY RANK, RegistrationDate"$
    Log(SQlScript)
    Starter.LocalSQL.Initialize(Starter.SafeFolder, "DVDatabase.db", True)
    Curs1 = Starter.LocalSQL.ExecQuery(SQlScript)
    Dim Balance1, SDebit, SCredit As Double = 0
    Dim SecondRow, ThirdRow As Double = 0
    Log(Curs1.RowCount) =====> Here I get the result of the rows and always the count one less
    For ii = 0 To Curs1.RowCount - 1
        Curs1.Position = ii
        DateTime.DateFormat = "yyyy-MM-dd"
        Dim BDate1 As Long
        BDate1 = DateTime.DateParse(Curs1.GetString("RegistrationDate"))
        If Curs1.GetString("MaxDate") = "First" Then
            Balance1 = Curs1.GetDouble("Balance")
        Else
            If Curs1.GetDouble("Debit") = 0 Then
                SecondRow = (Balance1 - Curs1.GetDouble("Credit")) + ThirdRow
            Else
                SecondRow = (Balance1 + Curs1.GetDouble("Debit")) + ThirdRow
            End If
            Balance1 = SecondRow
        End If
        SDebit = SDebit + Curs1.GetDouble("Debit")
        SCredit = SCredit + Curs1.GetDouble("Credit")
        DateTime.DateFormat = "dd/MM/yyyy"
        Kartela_Table.AddRowAutomaticWidth(Array As String(DateTime.Date(BDate1), _
        Curs1.GetString("DocumentCode"), Curs1.GetString("LongComment"), _
        NumberFormat2(Curs1.GetDouble("Debit"), 1, 0, 2, False) & " €", _
        NumberFormat2(Curs1.GetDouble("Credit"), 1, 0, 2, False) & " €", _
        NumberFormat2(Balance1, 1, 0, 2, False) & " €"))
    Next

It is like the B4A doesn't accept the first select on the sql.
But on DB Browser the result is correct.

For example at DB Browser I execute the result of Log(SQlScript) and I get 8 rows as result.
At B4A I get 7 rows and the first row always missing.

Thank you.

 

[makis_best]

Delete the post please.
I find the error on Sql script.

Thank you.

 

[Erel]

The correct way is to make a post that describes the mistake and the way it was fixed.

 

[makis_best]

Sorry @Erel.
The solution was that I change the sql script to this one and now work fine.

SQlScript = $"SELECT 1 AS RANK, '${BDate}' AS RegistrationDate, DocumentCode,
'Εξ απογραφής' AS LongComment, 0 AS Debit, 0 AS Credit, Balance, 'First' AS MaxDate
FROM LOCAL_EGM_Kartela
WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}' AND DocumentCode = 'Parastatiko Apografis'
AND Date(RegistrationDate) = '${BDate}'
UNION ALL
SELECT 2 AS RANK, RegistrationDate, DocumentCode, LongComment, Debit, Credit, Balance, '' AS MaxDate
FROM LOCAL_EGM_Kartela
WHERE fTradeAccountGID = '${Main.G1.ClientValueGID}'
AND Date(RegistrationDate) BETWEEN '${BDate}' AND '${EDate}'
AND DocumentCode <> 'Parastatiko Apografis' AND DocumentCode NOT LIKE 'ΑΧΓ%' AND DocumentCode NOT LIKE 'ΑΧΕ%'
ORDER BY RANK, RegistrationDate"$