Select Query Doubts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query Doubts

Author

Topic

jacobmk
Starting Member

6 Posts

Posted - 2009-09-23 : 09:12:21

Hi all,

I have some doubts in Select query.Could somebody please guide me on this.

we have normal Order,OrderDetails and usercart tables. I am posting a normal order for a flower. Order is submitted to order table and item is submitted to Orderdetails. we have now new functionality for reorder, that means customer can reorder the existing item. I need to check whether Item qty is available in the pctproductTypes table then only insert into usercart.For some products, itemqty is not available in the pctproductTypes ,for that qty we don't need to check the max qty , we can directly insert the qty from orderdetails table.
If max qty is available in pctproductTypes then only i need to check the remaining qty otherwise i can use the exisiting Qty in the order.

I am using the following Case condition for that

CASE WHEN pt.maxquantity is null THEN OrderDetails.Quantity

WHEN pt.maxquantity <> 0 THEN

CASE WHEN OrderDetails.Quantity <= CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
THEN OrderDetails.Quantity
ELSE CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
END

End as ActualQty

i need to develop a sql query for inserting into usercart if actualQty is not zero.

this is my sql query.

insert into Usercart(col1,col2,col3,....)

SELECT CASE WHEN pt.maxquantity is null THEN OrderDetails.Quantity
WHEN pt.maxquantity <> 0 THEN
CASE WHEN OrderDetails.Quantity <= CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
THEN OrderDetails.Quantity
ELSE CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
END
End as ActualQty,
, ActualQty *Products.SellingPrice

FROM
Orders INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderID and Orders.OrderID=231
left outer join pctproductTypes PT on OrderDetails.ProductID=PT.ProductID and OrderDetails.FormularySet=PT.FormularySet
inner join Products on OrderDetails.QuantityInBox=Products.QuantityInBox and OrderDetails.ManufacturerName=Products.ManufacturerName and OrderDetails.ProductName=Products.ProductName AND TariffMonth=MONTH(GETDATE()) AND TariffYear=YEAR(GETDATE())
left Outer join ProductsDiscount on Products.ProductID=ProductsDiscount.ProductID and Orders.PCtID=ProductsDiscount.PctID and Orders.SupplierID=ProductsDiscount.SupplierID and ActualQty <>0

But i am getting error as Invalid column name 'ActualQty'.

Could somebody suggest some alternative for this query.

Thanks

jacob

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 09:15:04

On the join, replace the ActualQty with the CASE statement that created that value.

John

"The smoke monster is just the Others doing barbecue"

jacobmk
Starting Member

6 Posts

Posted - 2009-09-23 : 09:23:12

Hi,

I am not clear abut what u have mentioned.

Jacob

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 09:25:16

quote:
Originally posted by jacobmk

Hi,

I am not clear abut what u have mentioned.

Jacob

Like this

SELECT CASE WHEN pt.maxquantity is null THEN OrderDetails.Quantity
WHEN pt.maxquantity <> 0 THEN
CASE WHEN OrderDetails.Quantity <= CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
THEN OrderDetails.Quantity
ELSE CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
END
End as ActualQty,
, CASE WHEN pt.maxquantity is null THEN OrderDetails.Quantity
WHEN pt.maxquantity <> 0 THEN
CASE WHEN OrderDetails.Quantity <= CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
THEN OrderDetails.Quantity
ELSE CAST((PT.MaxQuantity - ISNULL(dbo.UserOrderedQtyTest(pt.productid, 4,OrderDetails.FormularySet),0)) AS VARCHAR(10))
END
End *Products.SellingPrice

FROM
Orders INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderID and Orders.OrderID=231
left outer join pctproductTypes PT on OrderDetails.ProductID=PT.ProductID and OrderDetails.FormularySet=PT.FormularySet
inner join Products on OrderDetails.QuantityInBox=Products.QuantityInBox and OrderDetails.ManufacturerName=Products.ManufacturerName and OrderDetails.ProductName=Products.ProductName AND TariffMonth=MONTH(GETDATE()) AND TariffYear=YEAR(GETDATE())
left Outer join ProductsDiscount on Products.ProductID=ProductsDiscount.ProductID and Orders.PCtID=ProductsDiscount.PctID and Orders.SupplierID=ProductsDiscount.SupplierID and ActualQty <>0

Madhivanan

Failing to plan is Planning to fail

jacobmk
Starting Member

6 Posts

Posted - 2009-09-23 : 09:30:22

Hi,

Ok now i understood . but that means i need to put the case statement thrice and i need to check the case statement in the where
clause also. if actualqty is Zero, i don't need to select the item details from Order.Does it affect perfomance?

jacob

jacobmk
Starting Member

6 Posts

Posted - 2009-09-23 : 09:44:46

Do we have any other option to rebuild the query. i need to insert into usercart,depending on the userqty available.

jacob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources