I have three tables with different fields;for every table I must extract
only the row where the field fxx='value' and combine them in a fourth table.

Example

Table A: f1, f2, f3, fxx
Table B: f4, f5, fxx
Table C: f6, f7, fxx


WHERE xx='value'

the fxx field is the same for all tables

The result  tables is

Table RESULT:  f1, f2, f3, f4, f5, f6, f7,fxx

every time I empty the table result..

The table name and fielsd name are constants.
Is possible to use a single SQL statement  ?

Delphi 7 Ent + Dbisam 3.30

Thank you

Carlos

Carlos


I don't know if UNION is supported by DBISAM 3.30 but if it does you should be able to do something like

SELECT f1, f2, f3, '' AS f4, '' AS f5, '' AS f6, '' AS f7, fxx FROM A WHERE fxx='value'
UNION
SELECT '' AS f1, '' AS f2, '' AS f3, f4, f5, '' AS f6, '' AS f7, fxx FROM A WHERE fxx='value'
UNION
SELECT '' AS f1, '' AS f2, '' AS f3, '' AS f4, '' AS f5,f6,f7, fxx FROM A WHERE fxx='value'

assuming the fields are character, if integer use 0 etc

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> ha scritto nel messaggio
news:041A20F9-3F0B-418A-8BB6-9CFA3C5F714B@news.elevatesoft.com...
> Carlos
>
>
> I don't know if UNION is supported by DBISAM 3.30 but if it does you
> should be able to do something like
>
> SELECT f1, f2, f3, '' AS f4, '' AS f5, '' AS f6, '' AS f7, fxx FROM A
> WHERE fxx='value'
> UNION
> SELECT '' AS f1, '' AS f2, '' AS f3, f4, f5, '' AS f6, '' AS f7, fxx FROM
> A WHERE fxx='value'
> UNION
> SELECT '' AS f1, '' AS f2, '' AS f3, '' AS f4, '' AS f5,f6,f7, fxx FROM A
> WHERE fxx='value'
>
> assuming the fields are character, if integer use 0 etc
>
> Roy Lambert [Team Elevate]
>

I Roy,

thank you !

I translate the SQL in my test case:


SELECT  NUMFATTURA,    "NUMDDT", "NUMREG", CODINT   FROM FATDET    WHERE
CODINT='AP1200RP15'
UNION
SELECT "NUMFATTURA " , NUMDDT,  "NUMREG", CODINT   FROM DDT            WHERE
CODINT='AP1200RP15'
UNION
SELECT "NUMFATTURA " , "NUMDDT", NUMREG,  CODINT   FROM MM0MOVI  WHERE
CODINT='AP1200RP15'


and I obtain EXACTLY the following result:

+-------------+-------------+--------------+--------------+
NUMFATTURA NUMDDT  EXPRESSION   CODINT  +
+-------------+-------------+--------------+--------------+
      00056     +                   + NUMREG   +AP1200RP15+
      00058     +                   + NUMREG   +AP1200RP15+
      00062     +                   + NUMREG   +AP1200RP15+
      00068     +                   + NUMREG   +AP1200RP15+
NUMFATT   + 000137      + NUMREG   +AP1200RP15+
NUMFATT   + NUMDDT + 000678        +AP1200RP15+
NUMFATT   + NUMDDT + 000684        +AP1200RP15+
NUMFATT   + NUMDDT + 000699        +AP1200RP15+
NUMFATT   + NUMDDT + 000700        +AP1200RP15+


Is possible to clean the cell ?

Thank yo

Carlos

Carlos



SELECT  NUMFATTURA,    '' AS NUMDDT, '' AS NUMREG, CODINT   FROM FATDET    WHERE
CODINT='AP1200RP15'
UNION
SELECT '' AS NUMFATTURA, NUMDDT,  '' AS NUMREG, CODINT   FROM DDT            WHERE
CODINT='AP1200RP15'
UNION
SELECT '' AS NUMFATTURA, '' AS NUMDDT, NUMREG,  CODINT   FROM MM0MOVI  WHERE
CODINT='AP1200RP15'

Should work ie I'm asking for an emptystring to be returned as the column

Roy Lambert