[Zope-DB] Zope db, Using Conditional IF in SELECT statement

[Thant Zin]

Hi
I have two tables OrderDetails and TransDetails. 
 
The orderLine field in OrderDetails is in 100, 200, 300 ...(in Hundreds) if the order type is '111', and 1000,2000,3000 ...(in Thousands) if the type is '222'. 
 
But in TransDetails file there is only orderlineNo field with 1, 2, 3.  To get uniquely matched transReason data, I need to make conversion on orderLine field by dividing 100 or 1000 depending on orderType.
 
I try to use Select statement like below, but it won't work.
 
SELECT  A.warehouseNo, A.itemNo, A.plannedDate, A.orderNo, A.orderLine, A.orderType, A.status, A.orderQty, B.transReason        
 
FROM    OrderDeatails  AS A, TransDetail AS B
 
WHERE
 
( if (A.orderType = '111') A.orderLine/100 = B.orderLineNo
        else  if (A.orderType  = '222') A.orderLine/1000 = B.orderLineNumber
        AND A.itemNo=B.itemNo
        AND A.orderNumber=B.orderNumber
 )

AND  trim(A.warehouseNo)=trim(<dtml-sqlvar whouse type=string>)
AND  trim(A.itemNo) = trim(<dtml-sqlvar item type=string>)

ORDER BY plannedDate,itemNo
 
Is there anyone who can help me to sort out.





		
---------------------------------
Do you Yahoo!?
Yahoo! Movies - Buy advance tickets for 'Shrek 2' 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-db/attachments/20040512/486d7552/attachment-0001.html