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

Thant Zin thantzinuk at yahoo.com
Wed May 12 04:08:54 EDT 2004

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
( 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

More information about the Zope-DB mailing list