Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Select statement doesn't work until cut in half (Read 2698 times)
sqlabc123
YaBB Newbies
*


I Love SQL*XL

Posts: 1
Select statement doesn't work until cut in half
27.04.09 at 23:15:48
 
I read several posts in this forum about maximum statement length, but this one seems to be well within those bounds:
 
select count(*) from SOME.DATABASE
where
(xmlexists('$c/aaa/bbb/one'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/two'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/three'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/four'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/five'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/six'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/seven'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/eight'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/nine'
passing SOME.DATABASE.THIS_INFO as "c")
) AND NOT  
xmlexists('$c/aaa/bbb/otherthing'
passing SOME.DATABASE.THIS_INFO as "c")
AND ACTION = 'SEARCH'
 
 
However, it does not run successfully. When cutting out some of the middle stuff just to test, however, it works fine. For example,  
 
select count(*) from SOME.DATABASE
where
(xmlexists('$c/aaa/bbb/one'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/two'
passing SOME.DATABASE.THIS_INFO as "c")
OR xmlexists('$c/aaa/bbb/three'
passing SOME.DATABASE.THIS_INFO as "c")
) AND NOT  
xmlexists('$c/aaa/bbb/otherthing'
passing SOME.DATABASE.THIS_INFO as "c")
AND ACTION = 'SEARCH'
 
runs just fine. Any thoughts?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Select statement doesn't work until cut in hal
Reply #1 - 28.04.09 at 10:18:23
 
Hi,
 
SQL*XL does only pass the SQL on to the driver. It can be that the driver is truncating the statement. Which driver are you using?
 
What you can try to do is to use another driver/provider. You use Oracle so you can use OO4O, OLE DB and ODBC. For the latter two there are at least a version from Oracle corp and from Microsoft.  
 
You can also try to connect using the Microsoft Data Link Properties wizard. It is one of the buttons on the new connections screen in SQL*XL. You can specify more options in this wizard. Try to look for maximum SQL length.
 
If you can let me know which driver you use and which database version you use I can have a look whether I can also see this error so I can fault find and fix.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1