How to fix "ambiguous" where clause in procedure

Question

I am getting an error:

1052 - Column 'orderId' in where clause is ambiguous

when trying to do CALL GetOrderById(2000)

BEGIN
    SELECT
        hsorders.*,
        hslineitem.partNum, hslineitem.price
    FROM
        hsorders
    JOIN hslineitem ON hslineitem.orderId = hsorders.orderId
    WHERE
        orderId = orderId;
END

I need to pass an Id and join data from two tables that have the same `orderId``.

What am I doing wrong?

Show the complete code. Where is this programming block? – Gordon Linoff Apr 13 '18 at 18:21 — Gordon Linoff, Apr 13 '18 at 18:21

Lukasz Szozda · Answer 1 · 2018-04-13T18:34:29.833

2

You need to use alias:

BEGIN
    SELECT
        hsorders.*,
        hslineitem.partNum, hslineitem.price
    FROM  hsorders
    JOIN hslineitem ON hslineitem.orderId = hsorders.orderId
    WHERE
        hsorders.orderId = p_orderId;  -- I suggest to rename parameter to avoid 
                                       -- name collision
END

edited Apr 13 '18 at 18:34

answered Apr 13 '18 at 18:23

Lukasz Szozda

162,964
23
234
275

Gordon Linoff · Accepted Answer · 2018-04-13T18:28:46.500

You are getting the error because orderid is in both tables. Hence, the SQL engine does not know which orderid the where clause refers to.

Let me hypothesize that you have a parameter or variable called orderid.

That name conflicts with columns of the same name in the table. That is why I use prefixes. For instance, if orderid is being passed in as a parameter, I would name it in_orderid. If it were a local variable, I might use v_orderid.

Then the code would look like this:

BEGIN
    SELECT o.*, li.partNum, li.price
    FROM hsorders o JOIN
         hslineitem li
         ON li.orderId = o.orderId
    WHERE o.orderId = v_orderId;
END;

Notice that I also added table aliases so the query is easier to write and to read.

Thank you! Though I had to change v_orderId to li.orderId if that makes sense. — kinx, Apr 13 '18 at 18:30

score 1 · Answer 3 · answered Apr 13 '18 at 18:22

1

ambiguous means use table name or alias name before FIELD

Like hsorders.orderId in where clause.

answered Apr 13 '18 at 18:22

Devsi Odedra

5,244
1
23
37

Sas · Answer 4 · 2018-04-13T18:27:42.380

1

Use alias while joining tables. It will be helpful to distinguish the columns if they are in both the tables.

When to use alias

BEGIN
SELECT
    HO.*,
    HL.partNum, HL.price
FROM
    hsorders HO
JOIN hslineitem HL 
ON HO.orderId = HL.orderId
END

you have already used ON clause to give HO.orderId = HL.orderId so you do not have to use where clause again

edited Apr 13 '18 at 18:27

answered Apr 13 '18 at 18:22

Sas

278
1
12

How to fix "ambiguous" where clause in procedure

4 Answers4