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

[Smith, Neil (Worcester)]

What database are you using?

In Oracle you could do it with a DECODE or a CASE

-- 
Neil 


-----Original Message-----
From: Thant Zin [mailto:thantzinuk at yahoo.com]
Sent: 12 May 2004 09:09
To: Zope Database
Subject: [Zope-DB] Zope db, Using Conditional IF in SELECT statement


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.



**********************************************************************
Support the npower 500 with cricket star Phil Tufnell and his celebrity friends who will walk 500 miles from 16th May to 12th June to raise money for npower's charity partner, Macmillan Cancer Relief. To donate money or follow Phil's progress click to HTTP://www.npower500.com.
**********************************************************************
This e-mail is provided for general information purposes only and does not constitute investment or transactional advice. For  the avoidance of doubt the contents of this e-mail are subject to contract and will not constitute a legally binding contract. 

The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster at npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the RWE Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.  Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277. This e-mail may be sent on behalf of a member of the RWE Innogy group of companies.

**********************************************************************