myBatis 3 query from tables without relationship

Question

I got 2 tables: Table StatementDetails with columns:

    private String detailId;

    private BigDecimal tranxlogid;

    private String statementId;

Table transLog with columns:

    private BigDecimal tranxlogid;

    private Date datetime;

Then in StatementDetailsMapper.xml, I got a query:

<sql id="Base_Column_List">
        DETAIL_ID, TRANXLOGID, STATEMENT_ID, STATEMENT_GEN,
        STATEMENT_RECON
    </sql>

    <select id="reconStmtDetails" resultMap="StmtTranxDetailsResult"
        parameterType="java.lang.String">
        select
        <include refid="Base_Column_List" />
        from STATEMENT_DETAILS STD, POST_TRANX PT
        where PT.TRANXLOGID=STD.TRANXLOGID
    </select>

As you see that tranxlogid is not mapped between 2 tables, it's just a field. However, when I exe query, I got errors:

The error occurred while setting parameters
Cause: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

It seems like POST_TRANX PT is invalid.

Any ideas. Thanks.

Answer 1

What @BerndBuffen said is true, the error states that you are probably getting a column that exists in both tables (at least TRANXLOGID) so Oracle doesn't know which one to use. So your MyBatis Base_Column_List should look like this:

<!-- since I don't know which column is from wich table I consider -->
<!-- it to be all from STD -->
<sql id="Base_Column_List">
    STD.DETAIL_ID, STD.TRANXLOGID, STD.STATEMENT_ID, STD.STATEMENT_GEN,
    STD.STATEMENT_RECON
</sql>