Board index » delphi » Union with null value

Union with null value

I have a Union query like this
 SELECT
            TKNO,
              NULL,
              NULL,
              NULL,
              SUM(GIATRI),
              NULL
  FROM BUTTOAN     WHERE TKNO ='111'   GROUP BY TKNO
  UNION
   SELECT
               NULL,
               TKCO,
               NULL,
               NULL,
               NULL,
               GIATRI
     FROM BUTTOAN   WHERE TKNO ='111'

Each Select Query can run by itself, but the union query can not compile ,
it say 'Invalid command'. How can I do  to union them together?
Please help me !

 

Re:Union with null value


Quote
XuongRong wrote:
> I have a Union query like this
>  SELECT
>             TKNO,
>               NULL,
>               NULL,
>               NULL,
>               SUM(GIATRI),
>               NULL
>   FROM BUTTOAN     WHERE TKNO ='111'   GROUP BY TKNO
>   UNION
>    SELECT
>                NULL,
>                TKCO,
>                NULL,
>                NULL,
>                NULL,
>                GIATRI
>      FROM BUTTOAN   WHERE TKNO ='111'

> Each Select Query can run by itself, but the union query can not compile ,
> it say 'Invalid command'. How can I do  to union them together?
> Please help me !

Hello,

a union can only be done thru same datatypes. You have to cast the null values
to the corresponding datatypes.
Try this:

 SELECT
            TKNO,
              cast (NULL as <datatype of TKCO>),
              NULL,
              NULL,
              SUM(GIATRI),
              cast (NULL as <dataype of GIATRI>)
  FROM BUTTOAN     WHERE TKNO ='111'   GROUP BY TKNO
  UNION
   SELECT
               NULL,
               TKCO,
               NULL,
               NULL,
               cast (NULL as integer),
               GIATRI
     FROM BUTTOAN   WHERE TKNO ='111'

Nils

Re:Union with null value


Thank you very much , It works well
Quote
"Nils Gorges" <nocta...@gmx.net> wrote in message

news:3C1EEF3D.1A7C9629@gmx.net...
Quote
> XuongRong wrote:

> > I have a Union query like this
> >  SELECT
> >             TKNO,
> >               NULL,
> >               NULL,
> >               NULL,
> >               SUM(GIATRI),
> >               NULL
> >   FROM BUTTOAN     WHERE TKNO ='111'   GROUP BY TKNO
> >   UNION
> >    SELECT
> >                NULL,
> >                TKCO,
> >                NULL,
> >                NULL,
> >                NULL,
> >                GIATRI
> >      FROM BUTTOAN   WHERE TKNO ='111'

> > Each Select Query can run by itself, but the union query can not compile
,
> > it say 'Invalid command'. How can I do  to union them together?
> > Please help me !

> Hello,

> a union can only be done thru same datatypes. You have to cast the null
values
> to the corresponding datatypes.
> Try this:

>  SELECT
>             TKNO,
>               cast (NULL as <datatype of TKCO>),
>               NULL,
>               NULL,
>               SUM(GIATRI),
>               cast (NULL as <dataype of GIATRI>)
>   FROM BUTTOAN     WHERE TKNO ='111'   GROUP BY TKNO
>   UNION
>    SELECT
>                NULL,
>                TKCO,
>                NULL,
>                NULL,
>                cast (NULL as integer),
>                GIATRI
>      FROM BUTTOAN   WHERE TKNO ='111'

> Nils

Other Threads