Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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);
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
You have an error in your sql. Try this: ORDER BY TEXT
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
;
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