4 Replies Latest reply: Jun 21, 2013 4:33 PM by RJ Samp RSS

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

      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?!
          Krunoslav Pap

          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?!
              Tim Benoit

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

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

                  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

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

                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