Export from SQL database

11-18-2014 12:17 PM
Occasional Contributor

I'm attempting to write a model which will select records matching a specific query and map the result. Let me start by saying that I'm completely new to doing this with a SQL database as the origin of the data. Our IT folks just gave me access to the database this morning.

I first wanted to test by attempting to export records satisfying a simple query and going from there. Found the table (dbo.lwmain), right-clicked, and chose Export --> To dBase (single). Using the expression builder, I built an expression to pull a single record using the unique ID (inci_id LIKE '14036678_'). The expression will verify but no records are returned. I get Warning 000117: Warning empty output generated. The record does exist and the query is valid, so I am confused.

Does anyone have any ideas?

Tags (2)
0 Kudos
5 Replies
MVP Esteemed Contributor

What, specifically, is the id of the record you are trying to select?  Is it 14036678?  If so, it is the underscore that is tripping you up.  The LIKE operator is for pattern/wildcard searching.  There are two operators, the percent sign (%) and the underscore (_).  The % operator matches zero or more characters while the _ operator only matches one character; thus, '11%' will match '11' while '11_' will not.

0 Kudos
Occasional Contributor

Yes, the record is 14036678. I do know that there can be some trailing spaces behind the string, so I was attempting to mitigate for that. Inci_id = ‘14036678’ won’t verify and neither will inci_id LIKE ‘14036678%’. I’m starting to think there might be something wrong with the permissions.

0 Kudos
New Contributor III

Hi Sallie.

Have you tried to execute the query into a SQL text window? Is there any posibility? I don't know which application you are using, but if you can, maybe it can helps:

select * from dbo.lwmain where inci_id LIKE '14036678%'.

Other question; while I'm writting, I can see that the simple quotes are different between yours and mine. It depends on the text editor used, can be other character. If you open a simple notepad and copy/paste this:
Your simple quote

' My simple quote

As you can see, they are not the same char. Test in your query builder if it runs with this simple quote:

inci_id LIKE '14036678%'

Good luck!

I hope it helps,

0 Kudos
by Anonymous User
Not applicable

Can you try:

"inci_id" LIKE '14036678%'


[inci_id] LIKE '14036678%'

Hope this helps

Jesús de Diego

0 Kudos
Occasional Contributor

So it wasn’t a syntax issue but a software issue. The driver needed to connect to the SQL database isn’t compatible with my operating system. We’ve got to drop back and punt on this one and hope for new hardware & software.

0 Kudos