I am working on a requirement which requires data retrieval from 3 different tables. here is the expectation.
1. Table A (Session table)
2. Table B (Physical table)
3. Table C (Physical Table)
I have to inner join Table B and Table C, and for the resultant records i have to join with Table A to retreive the few columns from Table A, Table b and Table c.
LEFT OUTER JOIN
(
SELECT
COALESCE(ID,0)
,COALESCE(Depot,' ' )
,COALESCE(Tax,' ' )
,COALESCE(Qty_1,0)
,COALESCE(Qty_2,0)
,COALESCE(Qty_3,0)
,COALESCE(Timestamp,' ' )
FROM
Table B
, Table C
WHERE
condition 1
... condition 4
)
AS D
ON D.ID = A.ID
AND D.Depot = A.Depot
AND D.Tax = A.Tax
GROUP BY D,depot
, D.tax
, d.qty_1
,d.qty_2
,d.qty_3
, d.Timestamp
i am getting -401 sqlcode, when i checked the reason for that,
Code:
-401 THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT
COMPARABLE
I need your help in validating the above query, and to resolve the issue i am facing. Any suggestions to make it simpler is also welcome
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
You have not supplied us with thr field/column type definitions so how are we expected to help. Check them yourself to make sure that they are compatible with each other.
Below is the field definitions of the table fields
Depot - Char(08)
tax - Decimal(5,3)
Qty1,2,3 - Decimal(18,3)
Timestamp - Timestamp
Amt, Onloan - Decimal(18,3)
I have modified the query like below based on the suggestions
LEFT OUTER JOIN
(
SELECT
COALESCE(ID,0)
,COALESCE(Depot,' ' )
,COALESCE(Tax,0 )
,COALESCE(Qty_1,0)
,COALESCE(Qty_2,0)
,COALESCE(Qty_3,0)
,COALESCE(Timestamp,'0000-00-00-00.00.00.000000' )
FROM
Table B
, Table C
WHERE
condition 1
... condition 4
)
AS D
ON D.ID = A.ID
AND D.Depot = A.Depot
AND D.Tax = A.Tax
GROUP BY D,depot
, D.tax
, d.qty_1
,d.qty_2
,d.qty_3
, d.Timestamp
Now I am getting -206 sqlcode, the issue is w.r.t D.ID column. The resultant table records of Table B & C is referred as Table D. But I am unable to join the Table D column value with the Session table (Table A) .
SELECT
COALESCE(ID,0) AS ID
,COALESCE(Depot,' ' ) AS DEPOT
,COALESCE(Tax,0 ) AS TAX
,COALESCE(Qty_1,0) AS QTY_1
,COALESCE(Qty_2,0) AS QTY_2
,COALESCE(Qty_3,0) AS QTY_3
,COALESCE(Timestamp,'0000-00-00-00.00.00.000000' ) as TIMESTAMP
Thanks Chandan for your suggestion. I have incorporated the changes and able to Open the cursor now.
There is a problem when fetching records, getting sqlcode -305 now for the first column. Ideally after adding COALESCE function there should not be NULL returned from the query. Even added null indicator for the first column (Depot) and tried. But now getting -305 issue for the 2nd column. These columns are not NULL also in the DB2 table.