topic Re: How to Read a SQL database field that's a keyword?! in QlikView

How to Read a SQL database field that's a keyword?!

Anonymous — Tue, 18 Jun 2013 15:36:26 GMT

Am having trouble with this:

[$(vMyListTableName)]:

SQL

Select

TEXT

FROM

sysdba.PICKLIST

WHERE

PICKLISTID = '$(vMyPickListID)'

ORDER BY TEXT;

STORE $(vMyListTableName) INTO $(QVDSLXPath)\$(vMyListTableName).qvd (qvd);

DROP Table $(vMyListTableName);

and the "TEXT" column\field name is the culprit. I've tried [TEXT], "TEXT", TEXT to no avail....and am sure the ORDER BY Doesn't help.....

Is there another way to go after this data......like a Column[0] reference?

Thanks!

Re: How to Read a SQL database field that's a keyword?!

Wed, 19 Jun 2013 08:32:02 GMT

Hi, try with this:

[$(vMyListTableName)]:

SQL

Select

TEXT, TEXT AS TMP_TEXT

FROM

sysdba.PICKLIST

WHERE

PICKLISTID = '$(vMyPickListID)'

ORDER BY TMP_TEXT;

STORE $(vMyListTableName) INTO $(QVDSLXPath)\$(vMyListTableName).qvd (qvd);

DROP Table $(vMyListTableName);

Re: How to Read a SQL database field that's a keyword?!

flipside — Wed, 19 Jun 2013 09:02:27 GMT

What type of SQL database is it? Oracle, MS SQL Server, MySQL, other? You can specify a column reference for the ORDER BY in some - e.g. ORDER BY 1 - but I suspect it's the underlying database causing the problem in the select command. Using ODBC to an Excel file works fine, but I haven't got a SQL database to hand to check against.

Can you run SELECT * (maybe use SELECT TOP 10 * for testing)? If this works, then maybe you can drop the unecessary columns in the preceding load by not specifying them.

Otherwise, in the underlying database either rename the offending column or try a view based on the table with an aliased name for the column and run the code against the view instead of the table.

flipside

Re: How to Read a SQL database field that's a keyword?!

Fri, 21 Jun 2013 20:00:29 GMT

You have an error in your sql. Try this: ORDER BY TEXT

Re: How to Read a SQL database field that's a keyword?!

Anonymous — Fri, 21 Jun 2013 20:33:53 GMT

Thanks Guys!!!

Load/Store as QVD files:

SalesLogix Picklist table

break out the picklists for use in ListBoxes

June 18, 2013 RJ Samp

Added Deepak Vadithala's For Next Loop to read all SLX Picklist

Lists.

June 20:

only ADMIN Picklist Items brought in.

No longer store Picklists with zero members.

// ======================================================//

Sub LoadPicklistNames

PickListList:

SQL

Select

ITEMID as PicklistIDs,

TEXT as PicklistNames

FROM

sysdba.PICKLIST

WHERE

PICKLISTID = 'PICKLISTLIST'

ORDER BY TEXT;

End Sub;

// ======================================================//

Call LoadPicklistNames;

Let vPicklistCount** = NoOfRows('PickListList') - 1;

// ======================================================//

Sub LoadPicklistData

For i** = 0 To $(vPicklistCount)**

// set variablename = string

// let variable = expression

// $(variablename) is a macro expansion, i.e. whatever is the value of the

variable is evaluated as script text.

LET vMyTableName** = Peek('PicklistNames', $(i)**, 'PickListList');

vMyTableName** = purgechar(vMyTableName, Chr(39));

LET vMyPickListID** = Peek('PicklistIDs', $(i)**, 'PickListList');

LET vMyListTableName** =

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace('$(vMyTableName)','

', ''),'(',''),')',''),'-',''),'_',''), '.',''), '/',''), '\',''), '&','')

& 'List';

Let vMyPicklist** = Left(vMyListTableName, Len(vMyListTableName) - 4);

Let vMyPicklistCode** = vMyPicklist & 'Code';

Let vMyPicklistItemID** = vMyPicklist & 'ID';

LOAD

ITEMID As ,

TEXT As ,

SHORTTEXT As ;

;

SQL

Select

ITEMID, TEXT, SHORTTEXT

FROM

sysdba.PICKLIST

WHERE

PICKLISTID = '$(vMyPickListID)'

AND USERID LIKE 'ADMIN%'

ORDER BY TEXT;

Let vPicklistItemsCount** = NoOfRows('$(vMyListTableName)') - 1;

If vPicklistItemsCount > 0 THEN

STORE $(vMyListTableName)** INTO $(QVDSLXPath)*\$(vMyListTableName)*

**.qvd (qvd);

End If

DROP Table $(vMyListTableName)**;

Next i**

End Sub;

==========================================================================================//

Call LoadPicklistData;

==========================================================================================//

Drop Table PickListList;

RJ Samp