Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
covert Access query
ASC
2012-10-24 11:54:25.0
Im having trouble getting the proper syntax to convert this query to from access to be executed with Cold Fusion Engine and HXTT DBF driver...

This is the original Access Query:

SELECT ppwohd10.custno, ppwohd10.wono, ppwohd10.item, IIf(tblDivision.gllink Like "DEF" Or tblDivision.gllink Like "FRP",IIf(ppwohd10.item Like "7*","Homeopathic",IIf(ppwohd10.item Like "5*" Or ppwohd10.item Like "04*" Or ppwohd10.item Like "05*","Nutritional",IIf(ppwohd10.item Like "4*" Or ppwohd10.item Like "1*" Or ppwohd10.item Like "09*" Or ppwohd10.item Like "3*","Nutritional","Food"))),[Division]) AS Div, IIf(IsNull([ProductType]),"Powder",[producttype]) AS ProdType, IIf(ppwohd10.item Like "#####?##*",IIf(ppwohd10.item Like "#####M##*","Tab/Encap","Packaging"),IIf(ppwohd10.item Like "?###?##*","Packaging",IIf(ppwohd10.item Like "?####?##*","Packaging","Manufacturing"))) AS [Group], ppwohd10.status, ppwohd10.reqdte, icitem10.itmdesc, ppwohd10.sono, arcust10.salesmn, Trim([taxdist]) AS CSR, IIf([Group] Like "Manufacturing" And [ProdType] Like "Liquid","Manufacturing - Liquid",[Group] & " - " & [Div] & " " & [ProdType]) AS Department, ppwohd10.qty, ppwohd10.compqty, ppwohd10.status, ppwohd10.type, icitem10.ionhand
FROM (((ppwohd10 INNER JOIN (icitem10 LEFT JOIN tblProductTypes ON icitem10.itmclss = tblProductTypes.ItemCode) ON ppwohd10.item = icitem10.item) INNER JOIN iciloc10 ON ppwohd10.item = iciloc10.item) INNER JOIN arcust10 ON ppwohd10.custno = arcust10.custno) LEFT JOIN tblDivision ON iciloc10.gllink = tblDivision.GlLink
WHERE (((ppwohd10.status) Not Like 4) AND ((ppwohd10.compqty)=0) AND ((ppwohd10.type) Like "US"))
ORDER BY ppwohd10.custno, ppwohd10.wono DESC;


This is what I have been trying so far and omitting the IIF. I am getting error on JOINS (Syntax error: Stopped parse at ON(id140)):

SELECT pwohd10.custno
FROM
((("d:\mypath\ppwohd10" INNER JOIN ("d:\mypath\icitem10" LEFT JOIN "d:\mypath\tblProductTypes" ON icitem10.itmclss = tblProductTypes.ItemCode)
ON ppwohd10.item = icitem10.item)
INNER JOIN "d:\mypath\iciloc10" ON ppwohd10.item = iciloc10.item)
INNER JOIN "d:\mypath\arcust10" ON ppwohd10.custno = arcust10.custno)

LEFT JOIN ( "d:\mypath\TBLDIVISION" ON iciloc10.gllink = TBLDIVISION.GlLink )

WHERE

(((ppwohd10.status) Not Like 4) AND ((ppwohd10.compqty)=0) AND ((ppwohd10.type) Like "US"))

ORDER BY ppwohd10.custno
Re:covert Access query
HXTT Support
2012-10-25 07:46:23.0

>LEFT JOIN ( "d:\mypath\TBLDIVISION" ON iciloc10.gllink = TBLDIVISION.GlLink )
LEFT JOIN "d:\mypath\TBLDIVISION" ON iciloc10.gllink = TBLDIVISION.GlLink

>(((ppwohd10.status) Not Like 4) AND ((ppwohd10.compqty)=0) AND ((ppwohd10.type) Like "US"))
(((ppwohd10.status) Not Like '4') AND ((ppwohd10.compqty)=0) AND ((ppwohd10.type) Like 'US'))

Re:covert Access query
HXTT Support
2012-10-25 07:47:49.0
BTW, you needn't too much ()
For instance
(ppwohd10.status Not Like '4') AND ppwohd10.compqty=0 AND (ppwohd10.type Like 'US')
id motr clear.
Re:Re:Re:covert Access query
ASC
2012-10-25 15:16:43.0
Thank you I have most of the query working now. This is the last statement that is giving me a problem:

IIf([Group] Like 'Manufacturing' And [myProdType] Like 'Liquid','Manufacturing - Liquid',[Group] & ' - ' & [Div] & ' ' & [myProdType]) AS Department


Error: Two string type can't do & operation
Re:Re:Re:Re:covert Access query
HXTT Support
2012-10-25 17:39:08.0
& is a bitwise logical operator(&, |, ~, ^, <<, >>). You can use string operator(|| (left string concat right string), +(left string concat right string), -(trim left string then concat rightstring), $(check whether left string is contained in right string), condition operator(>, >=, =, ==, <=, <, !=, <>)

You should use || or + .
Re:Re:Re:Re:Re:covert Access query
HXTT Support
2012-11-01 22:03:19.0
>convert this query to from access to be executed
Forgot one thing, if your MS Access sql is a view, you can see the converted standard sql in java.sql.DatabaseMeta through HXTT Access since it supports most of views of MS Access.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap