Android Question Why does this variable show in green?

[RB Smissaert]

Have the following Sub:

Sub ShowRegistrationHistory
    
    Dim strSQL As String
    Dim iDateOfDeath As Object 'need object here for in case there is no row 
    
    strSQL = "SELECT DATE_OF_DEATH FROM REGISTRATION_ALL WHERE ID = ?"
    iDateOfDeath = SQL_ExecQuerySingleResult2(strSQL, Array As String(iEMIS_NUMBER))
    
    If iDateOfDeath = Null Then
        strSQL = $"SELECT DATE_STATUS_ADDED, PATIENT_TYPE, REG_STATUS, LINKS_REG_STATUS, DATE_STATUS_ENDED
FROM REG_HISTORY_ALL WHERE ID = ?"$
    Else
        'Note the use of the iDateOfDeath variable here, note we need Null here to cause a blank cell in the table
        '---------------------------------------------------------------------------------------------------------
        strSQL = $"SELECT DATE_STATUS_ADDED, PATIENT_TYPE, REG_STATUS, LINKS_REG_STATUS, DATE_STATUS_ENDED,
CASE WHEN REG_STATUS = 'Deceased' THEN ${iDateOfDeath} Else NULL END
FROM REG_HISTORY_ALL WHERE ID = ?"$
    End If
    
    RS_Registration_History = SQL_ExecQuery2(strSQL, Array As String(iEMIS_NUMBER))
    
    If RS_Registration_History.RowCount = 0 Then
        Dim rs As ResumableSub = Dialog.Show(Activity, Array As Object("OK"), _
                                  "Registration history", "", "No registration history for this patient", _
                                  -1, False, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, Null, False, arrS, 1)
        Wait For (rs) Complete (strResult As String)
        Return
    End If
    
    If pnlRegHistory.IsInitialized = False Then
        Activity.LoadLayout("RegHistory")
        arrPanels(Enums.ePanelType.RegHistory) = pnlRegHistory
        arrPatientLables(Enums.ePanelType.RegHistory) = lblPatRegHistory
        SetPanelViewsFontSize(Enums.ePanelType.RegHistory)
    End If
    
    If RS_Registration_History.RowCount > 0 Then
        lblPatRegHistory.Text = GetPatLabelString(-1) & " - " & RS_Registration_History.RowCount
    Else
        lblPatRegHistory.Text = GetPatLabelString(-1)
    End If
    
    If iDateOfDeath = Null Then
        tblRegHistory.LoadSQLiteDB4(RS_Registration_History, _
                                True, _
                                Array As String("Added", "Type", "Reg Status", "Links Status", "Status End"), _
                                Array As String("XD", "T", "T", "T", "XD"), _
                                Array As Int(0, 0, 0, 0, 0), _
                                Array As Int(0))        
    Else
        tblRegHistory.LoadSQLiteDB4(RS_Registration_History, _
                                True, _
                                Array As String("Added", "Type", "Reg Status", "Links Status", "Status End", "Date Death"), _
                                Array As String("XD", "T", "T", "T", "XD", "XD"), _
                                Array As Int(0, 0, 0, 0, 0, 0), _
                                Array As Int(0))
    End If
                                
    pnlRegHistory.Height = Activity.Height - pnlRegHistory.Top
    tblRegHistory.Height = pnlRegHistory.Height - (lblPatRegHistory.Height + btnRegHistoryColumns.Height)
    
    'RS_Registration_History.Close

    GotoPanel(Enums.ePanelType.RegHistory, False, True)
    mapSubAndPatID.Put("ShowRegistrationHistory", iEMIS_NUMBER)
    
End Sub

Sub SetPatientListPatLabel(bVisible As Boolean)
    
    If bVisible Then
        lblPatRegistration.Visible = True
        lblPatRegistration.Text = GetPatLabelString(-1)
        tblPatientList.Top = 40dip
        tblPatientList.Height = 482dip
        lblActionBar.Text = "Reg"
    Else
        lblPatRegistration.Visible = False
        tblPatientList.Top = 0
        tblPatientList.Height = 522dip
    End If
    
End Sub

Why does the variable in: ${iDateOfDeath} show in green?

RBS

 

[Mahares]

I think it will work for you this way:

strSQL = $"SELECT DATE_STATUS_ADDED, PATIENT_TYPE, REG_STATUS, LINKS_REG_STATUS, DATE_STATUS_ENDED,
CASE WHEN REG_STATUS = 'Deceased' THEN 
${iDateOfDeath} Else NULL END
FROM REG_HISTORY_ALL WHERE ID = ?"$

This might also work:

strSQL = $"SELECT DATE_STATUS_ADDED, PATIENT_TYPE, REG_STATUS, LINKS_REG_STATUS, DATE_STATUS_ENDED,
CASE WHEN REG_STATUS = Deceased THEN ${iDateOfDeath} Else NULL END
FROM REG_HISTORY_ALL WHERE ID = ?"$

Of course no way to test them

 

[RB Smissaert]

The one where Deceased is not in single quotes can't work and indeed crashes with:
net.sqlcipher.database.SQLiteException: no such column: Deceased

The other one gives the variable in the normal black, but then why does the posted SQL show the variable in green?
To me this seems a minor bug in the IDE.

Note that although the variable shows in green the query runs fine.

RBS

 

[Erel]

Why does the variable in: ${iDateOfDeath} show in green?

It is a bug or more correctly a limitation of the code editor parser related to multiline smart strings.
The performance overhead of dealing with this case isn't justified.