cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

QuerySpec not returning all columns from table

adesai_87
Aquamarine

QuerySpec not returning all columns from table

Hi Team,

 

I have a case 15726907 where QuerySpec not returning all columns from table

 

It’s a table customer created in the windchill database for testing. Here are the columns:

 

Table.jpg

Customer used the following API to query external table:

 

QuerySpec qs = new QuerySpec();

qs.setAdvancedQueryEnabled(true);

 

ExternalTableExpression ete = new ExternalTableExpression("ZES_WORKCENTER_T");

int tableIndex = qs.appendFrom((TableExpression)ete);

qs.appendSelect(KeywordExpression.Keyword.COLUMN_WILDCARD.newKeywordExpression(), new int[] { tableIndex }, false);

 

 

SQLFunction upperMATNR = SQLFunction.newSQLFunction(SQLFunction.UPPER, new TableColumn(qs.getFromClause().getAliasAt(tableIndex), "MATNR"));

qs.appendWhere(new SearchCondition(upperMATNR, SearchCondition.EQUAL, new ConstantExpression(partNumber)), new int[] {tableIndex});

 

System.out.println("Query: " + qs.toString());

qR = PersistenceServerHelper.manager.query(qs);

 

System.out.println(qR.getObjectVectorIfc().toString());

 

The logs show this:

2021-01-27 13:28:20,741 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - Query: SELECT *

2021-01-27 13:28:20,741 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - FROM ZES_WORKCENTER_T A0

2021-01-27 13:28:20,741 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - WHERE (UPPER(A0.MATNR) = N'WP35H2700')

2021-01-27 13:28:20,741 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - joins=null

2021-01-27 13:28:20,741 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - useBind=true [WP35H2700]

2021-01-27 13:28:20,932 INFO [ajp-nio-127.0.0.1-8010-exec-2] wt.system.out jmomber - wt.fc.ObjectSetVector{[050],[050],[050],[050],[050],[050],[050],[050],[050],}

 

 

We can see in the logs that there are 9 records returned, BUT it is only returning the 1st column in the table.

 

The results from SQLServer look liks this:

 

MANDT ARBPL MATNR WERKS KTEXT MAKTX BISMT

050 401061 WP35H2700 RHM Table Chrom Special Leadtime Chrome FRAME ST T16 160x220 An.60/80SS ****

050 401020 WP35H2700 RHM Glas FRAME ST T16 160x220 An.60/80SS ****

050 401099 WP35H2700 RHM Special product long lead time FRAME ST T16 160x220 An.60/80SS ****

050 400999 WP35H2700 RHM Recodification RHM MBI -- >SAP FRAME ST T16 160x220 An.60/80SS ****

050 401010 WP35H2700 RHM Veneer FRAME ST T16 160x220 An.60/80SS ****

050 401031 WP35H2700 RHM Verl. Lieferzeit Chamelion Dekor FRAME ST T16 160x220 An.60/80SS ****

050 401040 WP35H2700 RHM Not used FRAME ST T16 160x220 An.60/80SS ****

050 400100 WP35H2700 RHM Schreibtischendmontage 1 FRAME ST T16 160x220 An.60/80SS ****

050 401030 WP35H2700 RHM Decor top special Edge FRAME ST T16 160x220 An.60/80SS ****

 

Where we would expect a SELECT * to return all column values.

 

Worked on WTDocument table and it worked fine

 

package ext.AdvancedQueryCapabilities;

 

import wt.doc.WTDocument;

import wt.fc.PersistenceHelper;

import wt.fc.QueryResult;

import wt.query.ClassAttribute;

import wt.query.QueryException;

import wt.query.QuerySpec;

import wt.util.WTException;

import wt.util.WTPropertyVetoException;

import wt.vc.Versioned;

 

public class AdvancedQS {

 

               public static void main(String[] args) throws WTException, WTPropertyVetoException {

 

                              QuerySpec select = new QuerySpec();

                              int docIndex = select.appendClassList(WTDocument.class, false);

 

 

                              ClassAttribute docName = new ClassAttribute(WTDocument.class, WTDocument.NAME);

                              docName.setColumnAlias("DocName");

                              select.appendSelect(docName, new int[] { docIndex } , false);

 

                              ClassAttribute docNumber = new ClassAttribute(WTDocument.class, WTDocument.NUMBER);

                              docNumber.setColumnAlias("DocNumber");

                              select.appendSelect(docNumber, new int[] { docIndex } , false);

 

                              ClassAttribute docinfo=new ClassAttribute(WTDocument.class, WTDocument.DEPARTMENT);

                              docinfo.setColumnAlias("DocDeptt");

                              select.appendSelect(docinfo, new int[] { docIndex } , false);

 

 

 

                              QueryResult qr = PersistenceHelper.manager.find(select);

 

 

                              while (qr.hasMoreElements()) {

                                             Object[] object = (Object[]) qr.nextElement();

 

                                             System.out.println(object[0]+" :: "+ object[1] +" : "+ object[2]);

                              }

 

               }

}

 

Give the Article#CS164448 which didn't work.

Customer know the code I sent will work. Customer have used things like that many times. The different between what I sent and the code I’m asking you to verify is the following:

 

  1. The code customer asking to check uses an external table.
  2. The code customer asking to check uses KeywordExpression.Keyword.COLUMN_WILDCARD.newKeywordExpression().

 

The logs indicate that it is running the query:

 

SELECT *

2021-02-04 15:48:28,418 INFO  [ajp-nio-127.0.0.1-8010-exec-1] wt.system.out jmomber -  FROM ZES_WORKCENTER_T A0

2021-02-04 15:48:28,418 INFO  [ajp-nio-127.0.0.1-8010-exec-1] wt.system.out jmomber -  WHERE (UPPER(A0.MATNR) = N'WP35H2700')

 

So, as with any select * customer would expect ALL columns to be returned. As we will see in the logs, it is only returning the value for the first column. We modified the code to print out the line EXACTLY as wanted to and get an ArrayOutOfBounds exception when accessing o[1] this because it is only returning the first column:

 

System.out.println("Query: " + qs.toString());

                                        qR = PersistenceServerHelper.manager.query(qs);

 

                                       

                                       

                                        System.out.println(qR.getObjectVectorIfc().toString());

                                                     System.out.println(qR.size());

                                                    

 

                                                     while(qR.hasMoreElements())

                                                     {

                                                                  Object[] o = (Object[])qR.nextElement();

                                                                 

                                                                  System.out.println(o[0]+" :: "+ o[1] +" : "+ o[2]);

 

                                                     }           

 

 

 

2021-02-08 09:05:06,415 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber - Query: SELECT *

2021-02-08 09:05:06,415 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber -  FROM ZES_WORKCENTER_T A0

2021-02-08 09:05:06,415 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber -  WHERE (UPPER(A0.MATNR) = N'WP35H2700')

2021-02-08 09:05:06,415 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber - joins=null

2021-02-08 09:05:06,415 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber - useBind=true [WP35H2700]

2021-02-08 09:05:07,331 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber - wt.fc.ObjectSetVector{[050],[050],[050],[050],[050],[050],[050],[050],[050],}

2021-02-08 09:05:07,331 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.out jmomber - 9

2021-02-08 09:05:07,333 INFO  [ajp-nio-127.0.0.1-8010-exec-5] wt.system.err jmomber - java.lang.ArrayIndexOutOfBoundsException: 1

 

This is not a problem with how iterating through the results. The problem is that not all the columns are being returned and THAT is what customer would like you to investigate.

 

Regards,

Amar         

Announcements