Hi Jiri,
Sorry for the delay, I´m in a hurry with this project, at this moment I
don't have enough time for more test on this, all I can tell you is that I
made the following procedure:
CREATE PROCEDURE USUARIOS_CENTRO(
ID_USUARIO INTEGER)
RETURNS (
ID_CENTRO INTEGER,
DESCRIPCION VARCHAR(30))
AS
BEGIN
FOR
SELECT DISTINCT CENTROS.ID_CENTRO, CENTROS.DESCRIPCION
FROM CENTROS
INNER JOIN USUARIO_ZONA ON (CENTROS.ID_CENTRO =
USUARIO_ZONA.ID_CENTRO)
WHERE USUARIO_ZONA.ID_USUARIO = :ID_USUARIO AND
NOT EXISTS (
SELECT USUARIO_ZONA.ID_USUARIO
FROM USUARIO_ZONA
WHERE USUARIO_ZONA.ID_CENTRO=0 AND USUARIO_ZONA.ID_USUARIO =
:ID_USUARIO)
UNION
SELECT DISTINCT CENTROS.ID_CENTRO, CENTROS.DESCRIPCION
FROM CENTROS
INNER JOIN USUARIO_ZONA ON (CENTROS.ID_CENTRO =
USUARIO_ZONA.ID_CENTRO)
WHERE EXISTS (
SELECT USUARIO_ZONA.ID_USUARIO
FROM USUARIO_ZONA
WHERE USUARIO_ZONA.ID_CENTRO=0 AND USUARIO_ZONA.ID_USUARIO =
:ID_USUARIO)
INTO :ID_CENTRO,
:DESCRIPCION
DO
BEGIN
SUSPEND;
END
END
I'm using these to retrieve the data :
DataSet ds = new DataSet();
string queryString =
"SELECT USUARIOS_CENTRO.ID_CENTRO,
USUARIOS_CENTRO.DESCRIPCION " +
"FROM USUARIOS_CENTRO(@ID_USUARIO) ";
FbCommand cmd = new FbCommand(queryString, _Conexion.mConexion);
cmd.Parameters.AddWithValue("@ID_USUARIO", idUsuario);
FbDataAdapter da = new FbDataAdapter(cmd);
da.Fill(ds,"CENTROS");
Works like a charm with this approach, I don't think that this issue has
anything to do with database structure or the query itself, maybe is a
provider thing, anyway when I get some time I will send you script of the
involved tables and their data.
Thanks for your help,
Mocte
""Jiri Cincura"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
> I tried to run this:
> "select * from test where x = @x union select * from test where x = @x"
>
> without problems. Your query look fine on the first sight. Look at the
> error message, you'll have there line number, where FB isn't happy, so
> you get a hint. Or send a table structure, to be able to test it.
>
> --
> Jiri {x2} Cincura (CTO x2develop.com)
> http://blog.vyvojar.cz/jirka/ | http://www.ID3renamer.com
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Firebird-net-provider mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
