[Zope-DB] Zope db, Using Conditional IF in SELECT statement
Charlie Clark
charlie at egenix.com
Wed May 12 04:20:23 EDT 2004
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 ! ::::
More information about the Zope-DB
mailing list