Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Invalid Column Reference


This question is answered.


Permlink Replies: 1 - Last Post: Apr 24, 2018 3:00 PM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Melissa Torn

Posts: 143
Registered: 4/30/09
Invalid Column Reference  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 23, 2018 4:16 AM
SELECT B.ITEMNO,B.CHILDITEMNO,SUM(B.NETQTY)QTY,
I.ITEMTYPE,I.ITEMNAME,U.UOM,I.STOCK,
Case
When ( I.LASTUOMRNO = I.UOMRNO) Then I.LASTPRICE
When ( I.LASTUOMRNO = I.UOMRNO2) Then (I.LASTPRICE / I.UOMQTY2)
When ( I.LASTUOMRNO = I.UOMRNO3) Then (I.LASTPRICE / I.UOMQTY3) End AS LASTPRICE,

I.AVGCOST,I.BOMCOST_LAST,I.BOMCOST_AVG,I.CURRENCY

FROM BOM B
JOIN ITEMS I ON I.ITEMNO=B.CHILDITEMNO
LEFT JOIN UOM U ON U.RNO=I.UOMRNO
WHERE B.ITEMNO= :"ITMNO"

GROUP BY B.ITEMNO,B.CHILDITEMNO,I.ITEMTYPE,I.ITEMNAME,U.UOM,I.STOCK,
I.AVGCOST,I.BOMCOST_LAST,I.BOMCOST_AVG,I.CURRENCY
ORDER BY B.CHILDITEMNO

with above query i'm getting Invalid Column reference error.
as far i know Column Reference is releated with GROUP by
but i cant place LASTPRICE( comes from Case When ) into GROUP BY
what is wrong ?

Thank You
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Invalid Column Reference
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 24, 2018 3:00 PM   in response to: Melissa Torn in response to: Melissa Torn
On 4/23/2018 7:16 AM, Melissa Torn wrote:
SELECT B.ITEMNO,B.CHILDITEMNO,SUM(B.NETQTY)QTY,
I.ITEMTYPE,I.ITEMNAME,U.UOM,I.STOCK,
Case
When ( I.LASTUOMRNO = I.UOMRNO) Then I.LASTPRICE
When ( I.LASTUOMRNO = I.UOMRNO2) Then (I.LASTPRICE / I.UOMQTY2)
When ( I.LASTUOMRNO = I.UOMRNO3) Then (I.LASTPRICE / I.UOMQTY3) End AS LASTPRICE,

I.AVGCOST,I.BOMCOST_LAST,I.BOMCOST_AVG,I.CURRENCY

FROM BOM B
JOIN ITEMS I ON I.ITEMNO=B.CHILDITEMNO
LEFT JOIN UOM U ON U.RNO=I.UOMRNO
WHERE B.ITEMNO= :"ITMNO"

GROUP BY B.ITEMNO,B.CHILDITEMNO,I.ITEMTYPE,I.ITEMNAME,U.UOM,I.STOCK,
I.AVGCOST,I.BOMCOST_LAST,I.BOMCOST_AVG,I.CURRENCY
ORDER BY B.CHILDITEMNO

with above query i'm getting Invalid Column reference error.
as far i know Column Reference is releated with GROUP by
but i cant place LASTPRICE( comes from Case When ) into GROUP BY
what is wrong ?

Thank You

In IB2017 you can try derived tables although looks like there is a bug with
this type of derived table.

You can make a view that has the key + the case and join that view in.

Or you can add last price as a computed field (should work, never tried with a
case statement).

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02