<DIV>
<DIV>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0 ?>
<TBODY>
<TR>
<TD>
<DIV>
<DIV>Hi</DIV>
<DIV>I have two tables OrderDetails and TransDetails. </DIV>
<DIV> </DIV>
<DIV>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'. </DIV>
<DIV> </DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>I try to use Select statement like below, but it won't work.</DIV>
<DIV> </DIV>
<DIV>SELECT A.warehouseNo, A.itemNo, A.plannedDate, A.orderNo, A.orderLine, A.orderType, A.status, A.orderQty, B.transReason </DIV>
<DIV> </DIV>
<DIV>FROM OrderDeatails AS A, TransDetail AS B</DIV>
<DIV> </DIV>
<DIV>WHERE</DIV>
<DIV> </DIV>
<DIV>( if (A.orderType = '111') A.orderLine/100 = B.orderLineNo<BR> else if (A.orderType = '222') A.orderLine/1000 = B.orderLineNumber</DIV>
<DIV> AND A.itemNo=B.itemNo<BR> AND A.orderNumber=B.orderNumber</DIV>
<DIV> )<BR></DIV>
<DIV>AND trim(A.warehouseNo)=trim(<dtml-sqlvar whouse type=string>)<BR>AND trim(A.itemNo) = trim(<dtml-sqlvar item type=string>)<BR></DIV>
<DIV>ORDER BY plannedDate,itemNo</DIV>
<DIV> </DIV>
<DIV>Is there anyone who can help me to sort out.<BR></DIV></DIV></TD></TR></TBODY></TABLE></DIV></DIV><p>
<hr size=1><font face=arial size=-1>Do you Yahoo!?<br>Yahoo! Movies - <a href="http://movies.yahoo.com/showtimes/movie?mid=1808405861">Buy advance tickets for 'Shrek 2' </a>