Bug with revDataFromQuery

[brainois]

Hello,

I have a very annoying and strange behaviour with revDataFromQuery.
Let the following function to count the number of clients whose age is between pAgeMin and pAgeMax and for which an intervention occured between the following date: sFromDate and sToDate (script local variables)

Code: Select all

function count pGender, pAgeMin, pAgeMax
    local tFromBirthYear, tToBirthYear, tRetVal
    get the date
    convert it to dateItems
    
    put the first item of it into tFromBirthYear # 2007
    subtract pAgeMax from tFromBirthYear 
    
    put the first item of it into tToBirthYear # 2007
    subtract pAgeMin from tToBirthYear

    put revDataFromQuery(comma,return,gDatabaseId, \
            "SELECT COUNT(DISTINCT(d.id))" && \
            "FROM dossier d INNER JOIN intervention i ON i.id_dossier = d.id" && \
            "WHERE d.antenne=:1 AND d.sexe=:2 AND d.annee_naissance>=:3" && \
            "AND d.annee_naissance<=:4 AND i.date>=:5 AND i.date<=:6", \
            "sOffice", "pGender", "tFromBirthYear", "tToBirthYear", "sFromDate", "sToDate") into tRetVal
    return the first item of tRetVal
end count

when i call the function as follow:

Code: Select all

get count("FEMALE", 60, 69)

the result is 2, which is WRONG!!! It should be 21!!!
Now, if i hard code the tFromBirthYear and tToBirthYear, then it works (but it is not an acceptable workaround):

Code: Select all

function count pGender, pAgeMin, pAgeMax
    local tFromBirthYear, tToBirthYear, tRetVal
    put 1938 into tFromBirthYear
    put 1947 into tToBirthYear
    put revDataFromQuery(comma,return,gDatabaseId, \
            "SELECT COUNT(DISTINCT(d.id))" && \
            "FROM dossier d INNER JOIN intervention i ON i.id_dossier = d.id" && \
            "WHERE d.antenne=:1 AND d.sexe=:2 AND d.annee_naissance>=:3" && \
            "AND d.annee_naissance<=:4 AND i.date>=:5 AND i.date<=:6", \
            "sOffice", "pGender", "tFromBirthYear", "tToBirthYear", "sFromDate", "sToDate") into tRetVal
    return the first item of tRetVal
end count

Note that the following hard coded versions DO NOT WORK and lead to the wrong result of 2:

Code: Select all

function count pGender, pAgeMin, pAgeMax
    local tFromBirthYear, tToBirthYear, tRetVal
    
    put 2007 into tFromBirthYear
    subtract pAgeMax from tFromBirthYear
    
    put 2007 into tToBirthYear
    subtract pAgeMin from tToBirthYear

    
    put revDataFromQuery(comma,return,gDatabaseId, \
            "SELECT COUNT(DISTINCT(d.id))" && \
            "FROM dossier d INNER JOIN intervention i ON i.id_dossier = d.id" && \
            "WHERE d.antenne=:1 AND d.sexe=:2 AND d.annee_naissance>=:3" && \
            "AND d.annee_naissance<=:4 AND i.date>=:5 AND i.date<=:6", \
            "sOffice", "pGender", "tFromBirthYear", "tToBirthYear", "sFromDate", "sToDate") into tRetVal
    return the first item of tRetVal
end count

Code: Select all

function count pGender, pAgeMin, pAgeMax
    local tFromBirthYear, tToBirthYear, tRetVal
    
    put 2007 into tFromBirthYear
    subtract 69 from tFromBirthYear
    
    put 2007 into tToBirthYear
    subtract 60 from tToBirthYear

    
    put revDataFromQuery(comma,return,gDatabaseId, \
            "SELECT COUNT(DISTINCT(d.id))" && \
            "FROM dossier d INNER JOIN intervention i ON i.id_dossier = d.id" && \
            "WHERE d.antenne=:1 AND d.sexe=:2 AND d.annee_naissance>=:3" && \
            "AND d.annee_naissance<=:4 AND i.date>=:5 AND i.date<=:6", \
            "sOffice", "pGender", "tFromBirthYear", "tToBirthYear", "sFromDate", "sToDate") into tRetVal
    return the first item of tRetVal
end count

I have tried the following workaround: instead of passing age interval to the function, i am directly passing year interval.

Code: Select all

function count2 pGender, pFromBirthYear, pToBirthYear
    local tRetVal
    put revDataFromQuery(comma,return,gDatabaseId, \
            "SELECT COUNT(DISTINCT(d.id))" && \
            "FROM dossier d INNER JOIN intervention i ON i.id_dossier = d.id" && \
            "WHERE d.antenne=:1 AND d.sexe=:2 AND d.annee_naissance>=:3" && \
            "AND d.annee_naissance<=:4 AND i.date>=:5 AND i.date<=:6", \
            "sOffice", "pGender", "pFromBirthYear", "pToBirthYear", "sFromDate", "sToDate") into tRetVal
    return the first item of tRetVal
end count2

The following call works and returns 21 as expected:

Code: Select all

get count("FEMALE", 1938, 1947)

But the following code does not work and returns the wrong result 2:

Code: Select all

put 2007 into tYear
get count("FEMALE", tYear - 69, tYear - 60)

The following code does not work and returns the wrong result 2:

Code: Select all

get count("FEMALE", 2007 - 69, 2007 - 60)

I am completely stuck!!
Any help is appreciate.

Regards

PS: i am working on MacOSX 10.4.8 French with Revolution Studio 2.7.4 and MySQL 5 database

françois