Click here to Skip to main content
15,902,894 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi to all ..

I want to creat a View in my MySQL Database using this query:

SQL
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`clouddust`@`%` SQL SECURITY INVOKER VIEW `viewstocktransfersum` AS
select `tblstocktransfer`.`DateReference` AS `DateReference`,
`tblstocktransfer`.`InvoiceDate` AS `InvoiceDate`,
`tblstocktransfer`.`RefNo` AS `RefNo`,`tblstocktransfer`.`TransNo` AS `TransNo`,
`tblstocktransfer`.`WarehouseCodeFrom` AS `WarehouseCodeFrom`,
`tblstocktransfer`.`WHNameFrom` AS `WHNameFrom`,
`tblstocktransfer`.`WarehouseCodeTo` AS `WarehouseCodeTo`,`tblstocktransfer`.`WHNameTo` AS `WHNameTo`,
`tblproductlist`.`ProductCode` AS `ProductCode`,
`tblproductlist`.`Description` AS `Description`,
`tblproductlist`.`ProductCategory` AS `ProductCategory`,
`tblproductlist`.`UnitOfMeasure` AS `UnitOfMeasure`,
m.`Qty` AS Qty,
`tblstocktransfer`.`Status` AS `Status`,
`tblstocktransfer`.`StoreId` AS `StoreId`
from ((`tblstocktransfer`
INNER JOIN (SELECT TransNo, StoreId, ProductCode, SUM(tblstocktransferdetail.Qty) AS Qty
FROM `tblstocktransferdetail` GROUP BY StoreId, TransNo, ProductCode) m
on(`tblstocktransfer`.`TransNo` = `m`.`TransNo` AND tblstocktransfer.StoreId = m.StoreId)))
INNER JOIN tblproductlist ON (m.ProductCode = tblproductlist.ProductCode AND m.StoreId = tblproductlist.StoreId);


My problem is that this query returns an error "View's SELECT contains a sub query in the FROM clause". But when i run this query by not creating a view, it returns a correct result. Please help me through this.

Thank you all! God bless you always!!
Posted

1 solution

A simple google search of the error explains the limitation of MySql views and also provides some suggested workarounds. For example, see http://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900