I'm baffled, and hopefully someone can help me out. We use a pass through query to get results from our main dataabse. We then have other queries that call this query to produce different reports. Here is an example:
SELECT
BEGRNGNR (FORMAT '999999999999') (CHAR(8)) BEGIN_CHAR8,
BEGRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(8)) END_CHAR8,
FROM WIC.BLKDOCCL
WHERE ...
UNION
SELECT
BEGRNGNR (FORMAT '999999999999') (CHAR(8)) BEGIN_CHAR8,
BEGRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(8)) END_CHAR8,
FROM WIC.BLKDOCLG
WHERE...
ORDER BY 1,2,3 ;
This query runs fine when submitted. The calling query looks like this : (BLKINV_02 is the query above, BLKINV_01 is a two column table with the column names in the select query below).
SELECT DISTINCTROW BLKINV_02.BEGIN_CHAR8,
BLKINV_01.FV_CHAR8,
BLKINV_02.END_CHAR8,
BLKINV_02.BEGRNGNR,
BLKINV_01.FV,
BLKINV_02.ENDRNGNR
FROM BLKINV_01 INNER JOIN BLKINV_02 ON
BLKINV_01.FV_CHAR8 = BLKINV_02.BEGIN_CHAR8
WHERE ... ;
When this query is run, we get the error:
The specified field 'BLKINV_02.BEGIN_CHAR8' could refer to more than one table listed in the FROM clause of your SQL statement.(Error 3079).
As you can see the column is fully qualified, but it just doesn't like it for some reason. Any help would be appreciated.
Thanks...
SELECT
BEGRNGNR (FORMAT '999999999999') (CHAR(8)) BEGIN_CHAR8,
BEGRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(8)) END_CHAR8,
FROM WIC.BLKDOCCL
WHERE ...
UNION
SELECT
BEGRNGNR (FORMAT '999999999999') (CHAR(8)) BEGIN_CHAR8,
BEGRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(12)),
ENDRNGNR (FORMAT '999999999999') (CHAR(8)) END_CHAR8,
FROM WIC.BLKDOCLG
WHERE...
ORDER BY 1,2,3 ;
This query runs fine when submitted. The calling query looks like this : (BLKINV_02 is the query above, BLKINV_01 is a two column table with the column names in the select query below).
SELECT DISTINCTROW BLKINV_02.BEGIN_CHAR8,
BLKINV_01.FV_CHAR8,
BLKINV_02.END_CHAR8,
BLKINV_02.BEGRNGNR,
BLKINV_01.FV,
BLKINV_02.ENDRNGNR
FROM BLKINV_01 INNER JOIN BLKINV_02 ON
BLKINV_01.FV_CHAR8 = BLKINV_02.BEGIN_CHAR8
WHERE ... ;
When this query is run, we get the error:
The specified field 'BLKINV_02.BEGIN_CHAR8' could refer to more than one table listed in the FROM clause of your SQL statement.(Error 3079).
As you can see the column is fully qualified, but it just doesn't like it for some reason. Any help would be appreciated.
Thanks...