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

[Charlie Clark]

Dear Thant,

it seems your problem has little or nothing to do with Zope itself but with 
your database system. You need to look at what functions the database 
provides or how to write your own. You might be able to do what you want 
simply by using views.

Good luck

Charlie

On 2004-05-12 at 10:08:54 [+0200], Thant Zin <thantzinuk at yahoo.com> wrote:
> 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.
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::