topic SQL Select not returning records in QlikView

SQL Select not returning records

Wed, 04 Apr 2012 11:01:45 GMT

Hi, we have setup an installation of QlikView and connected it to our Progress database, via ODBC. When I try and query a table it returns me no rows, even though I know there are 6222 records in that table. I thought at first that maybe there was a very large field, or some bad data in one of the records that was causing me a problem, because if i return just one field of the table (an integer field) it does return records. After some more investigation it looked like it was one specific field, which was called cInitials and stores customer names essentially. When I look at the difference between records that I can retrieve and records that I cant.........it seems the difference is that if they have a space in the value then QlikView wont return it.

So if the field contains "Steve Smith" it wont come back, whereas if it was "SteveSmith" it will come back!

If I point Crystal Reports at the same table, I do get all the records. The script looks like this:

SQL SELECT cInitials, cSurname, custRef FROM PUB.Customer;

Do I need to do something special with my SELECT statement to cope with spaces in the data??

Thanks

Steve

SQL Select not returning records

Anonymous — Wed, 04 Apr 2012 11:45:21 GMT

Hi Steve,

I connect to Progress via ODBC all the time, so that is not the problem.

Did you use the Select button in the script windows?

Here you can go to the table you want and use the preview tab if there is the data you expect.

Also us preceding load, this makes it easier to controle your data in the script

I your case it should look something like:

Costumers:

LOAD cInitials,

cSurname

custRef ;

SQL SELECT cInitials,

cSurname,

custRef

FROM PUB.Customer;

Good Luck,

SQL Select not returning records

Or — Wed, 04 Apr 2012 11:49:15 GMT

Steve,

Have a look-see if your ODBC settings are correct - for example, by using your ODBC connection to access this data from Excel. There's sometimes ODBC settings that have to be changed from the default (related to quoted identifiers, nulls/padding, etc), so this should narrow down your issue to either the ODBC connection, or an actual QlikView problem.

SQL Select not returning records

Wed, 04 Apr 2012 12:14:34 GMT

It turns out it was a problem with the SQL WIDTH attribtues on the DB fields. I tried to query from Excel and got an actual error back along the lines of: Column cInitials in table PUB.Customers has a value exceeding its max length or precision". As soon as I saw that I remembered the SQL WIDTHs attributes. Increased those, and now it returns the values!

Thanks all.