Query not returning the correct dataset.

> DifusoraId = ifusoraId AND
> Fecha BETWEEN :Fecha1 AND :Fecha2

To check, could you list the parameter values you are using for your test
please?

--

Eivind Bakkestuen
Nexus Database Systems

Hi,

I'm having a problem with the following query:

SELECT DiaId, Fecha, DiasModelo.Clave
FROM
ProgramacionD
JOIN DiasModelo ON DiaModeloId = DiaModeloIdUsado
WHERE
DifusoraId = ifusoraId AND
Fecha BETWEEN :Fecha1 AND :Fecha2
ORDER BY
Fecha

This doesn't return any record, even if, at least as far as I can see, it
should return all the records in ProgramacionD. This worked fine in NX1

Changing the query to this:

SELECT DiaId, Fecha, DiaModeloIdUsado AS DiaModeloId INTO #INFO_DIAS
FROM
ProgramacionD
WHERE
DifusoraId = ifusoraId AND
Fecha BETWEEN :Fecha1 AND :Fecha2
ORDER BY
Fecha;

SELECT #INFO_DIAS.*, DiasModelo.Clave
FROM
#INFO_DIAS
LEFT JOIN DiasModelo USING(DiaModeloId)

returns the correct dataset. For what I can see, the problem seem to be the
ON condition in the JOIN clause in the first query. The name of the field is
not the same. Renaming it so it is the same, into a temporary table, and
using it, solves the problem.

I don't know if there is a more involved issue, or maybe something that has
to be like that because of "The Standard" <vbg>, because I tryed with a set
of smaller tables (created in memory, just to have a nice looking testcase)
but I wasn't able to reproduce it.

I posted the tables in the .binaries ng.

Thanks!

--
Rodrigo Gómez [NDX]
rodrigo.gomez at ndx dot nexusdb dot com
www.rodrigogomez.com.mx/gallery/

DifusoraId = 1
Fecha1 = 2005-01-01
Fecha2 = 2005-12-31

Oh, btw... when opening the parameters value in EM, if I put the first
parameter, DifusoraId, and then change to the next field without pressing
Enter (Null is still checked), it gives me an error: "DataSet not in editing
mode". And two tables gets listed in the database, MemoryTable_<bignumber>.
One of them has the parameters, and the other seem to have the available
types.

Thanks,

--
Rodrigo Gómez [NDX]
rodrigo.gomez at ndx dot nexusdb dot com
www.rodrigogomez.com.mx/gallery/

"Eivind Bakkestuen [NDD]" <Ieivind.bakkestuenHATE@nexusdb.comSPAM> escribió
en el mensaje news:42dd7145@wic040d....
>> DifusoraId = ifusoraId AND
>> Fecha BETWEEN :Fecha1 AND :Fecha2
>
> To check, could you list the parameter values you are using for your test
> please?
>
> --
>
> Eivind Bakkestuen
> Nexus Database Systems
>
>

> DifusoraId = 1
> Fecha1 = 2005-01-01
> Fecha2 = 2005-12-31

The query returns records for me with our current internal codebase, so I am
guessing that its something that has been fixed recently. Btw 1) it was the
WHERE clause causing the problem, not the join, 2) the queries are not 100%
generic replacements for each other, the second only works because there is
a match in the inner join of the first query for every record in the left
table of the second.

> Oh, btw... when opening the parameters value in EM, if I put the first
> parameter, DifusoraId, and then change to the next field without
> pressing Enter (Null is still checked), it gives me an error:
> "DataSet not in editing mode". And two tables gets listed in the
> database, MemoryTable_<bignumber>. One of them has the parameters,
> and the other seem to have the available types.

Thanks, issue #1514, fixed.

--

Eivind Bakkestuen
Nexus Database Systems

Hi Eivind,

> The query returns records for me with our current internal codebase, so I
> am
> guessing that its something that has been fixed recently. Btw 1) it was
> the
> WHERE clause causing the problem, not the join

Ok, I'll wait for the next binaries to try it.

I said about the JOIN because removing it (basically, having only the first
query in the second script), return the records correctly, so I assummed the
JOIN condition wasn't working, even if I tryed with FULL, LEFT, RIGHT, etc.,
without any luck.

> 2) the queries are not 100%
> generic replacements for each other, the second only works because there
> is
> a match in the inner join of the first query for every record in the left
> table of the second.

Ok, I'll need to think twice about this <g>. There should always be records
in DiasModelo that match the used ones in ProgramacionD, but it can be cases
where it's not this case, and it should be able to handle it without
problems. Thanks for the comment. I need to open the SQL guide now :-)

Thanks for your help,

--
Rodrigo Gómez [NDX]
rodrigo.gomez at ndx dot nexusdb dot com
www.rodrigogomez.com.mx/gallery/