Created this statement
select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id
Which works amazingly enough fine & dandy - however when I add the statement
sum(IS.on_hand_qty) as below & add the group by clause it errors out & I'm stuck as to what I'm missing. Can't find documentation indicating if I can call the colums as their aliases but using the 'formulas' also error out so I'm stumped.
select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2,
Sum(IP.on_hand_qty)
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id
group by IP.inv_id, Option_ID0, Option_ID1, Option_ID2
Help?
Thanks
Gina
select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id
Which works amazingly enough fine & dandy - however when I add the statement
sum(IS.on_hand_qty) as below & add the group by clause it errors out & I'm stuck as to what I'm missing. Can't find documentation indicating if I can call the colums as their aliases but using the 'formulas' also error out so I'm stumped.
select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2,
Sum(IP.on_hand_qty)
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id
group by IP.inv_id, Option_ID0, Option_ID1, Option_ID2
Help?
Thanks
Gina