unknown error

[dumbom]

hi Guys

I have set up a drill process from a Sql table. i get an error message saying "1840 [6] ERROR 2013-10-14 09:03:47.750 TM1.SQLAPI 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'FA'."

below is the coding i am using. if i change the division parameter in the Query to read ('?Division?')) instead of ('?vDivision?')), i can drill through to the lowest level, but not any consolidated levels of division. if i keep it as it is below, it says drill handle was succesfully built, but did not return a valid handle. check the message log.

any ideas?

Query

Code: Select all

Select 
Source, Journal, EntryGroup, Customer, CustomerName, Supplier, SupplierName,
 Invoice, SubJournal, StockCode, Description, substring(GlCode,1,4) as Account, substring(GlCode,6,2) as Division,
substring(GlCode,9,2) as BusinessUnit, substring(GlCode,12,2) as Location, GlCode, GlCodeDescription, EntryValue,
GlPeriod as vMonth , GlYear as Year, Reference, Comment, GRN, NetSalesValue, PurchaseOrder, PaymGrossValue, 
WarehouseFrom, WarehouseTo, PurchaseOrderLin, Job, JobDescription,
SalesOrder, SalesOrderLine, RegisterNumber, Asset, Line, JnlDate, EntryType, Branch, AssetCostCenter, AssetGroupCode,
AssetStatus,ADescription, DateSold, PurchaseDate, DisposedFlag, 'Actual' as Version
from tmpTrnDetail
where GlYear ='?Year?'
and (GlPeriod ) = ('?vMonth?')
and (substring(GlCode,1,4) in ('?Accounts?'))
and (substring(GlCode,6,2) in ('?vDivision?'))
and (substring(GlCode,12,2) = '?Location?')
and (substring(GlCode,9,2) = '?BusinessUnit?')

Prolog

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

#vmonth = subst(vmonth,1,(scan(vmonth,'.'))-1);
#vMonth = UPPER(DimensionElementPrincipalName('Month',vmonth));

Accounts = UPPER(DimensionElementPrincipalName('Accounts',Accounts));
division = UPPER(DimensionElementPrincipalName('Division',division));
BusinessUnit = UPPER(DimensionElementPrincipalName('Business Unit',BusinessUnit));
Location = UPPER(DimensionElementPrincipalName('Location',Location));
Currency = UPPER(DimensionElementPrincipalName('Currency',Currency));


# variable for generated SQL
sSQL=' ';

# we need the children of this consolidated element in Division dimension
sDimName = 'Division';
sParentElemName=Division;

# build subset in Division dimension with all leaf children of the parent
sSubName='zTemp';
SubsetCreateByMdx(sSubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {['|sDimName|'].['|sParentElemName|']}, ALL, RECURSIVE )}, 0)}');

# iterate subset and add leaf elements to SQL statement
nCounter=1;
nMaxCount= SubsetGetSize(sDimName,sSubName);
WHILE(nCounter<=nMaxCount);
  sElemName= SubsetGetElementName(sDimName,sSubName,nCounter);
  # make sure it is principal name to match values in source system
  # probably don't need this line - the MDX should return this
  sPrincipalElemName= DimensionElementPrincipalName(sDimName,sElemName);
  # update SQL
  sSQL= sSQL|CHAR(39)|sPrincipalElemName|CHAR(39);
  # need a comma unless last item in subset
  IF(nCounter<nMaxCount);
    sSQL=sSQL|',';
  ENDIF;
  nCounter=nCounter+1;
END;

# clean up temp subset
SubsetDestroy(sDimName,sSubName);

# debug output
AsciiOutput('debug.txt',sSQL);

# assign to drill-through query
vDivision=sSQL;

[David Usherwood]

Looking through your SQL I'm thinking that you should unquote the division variable

Code: Select all

and (substring(GlCode,6,2) in (?vDivision?))

not

Code: Select all

and (substring(GlCode,6,2) in ('?vDivision?'))

as you are supplying a list of divisions as eg
'101','105','203'

More generally, to debug this sort of thing, you might find rewriting the whole query into the TI variable DataSourceQuery easier to debug as you can asciioutput the result and check quotes, commas and syntax.

Like the use of MDX to get the descendants of your consolidated elements - could be useful.

[dumbom]

hi David.

thanks for your response. i am busy playing around with the query now as we speak, but had a thought while i was doing it. in my prolog tab, i have the following code to create my hierarchy for the division dimension:

Code: Select all

[code] SubsetCreateByMdx(sSubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {['|sDimName|'].['|sParentElemName|']}, ALL, RECURSIVE )}, 0)}');

[/code]
the element i am selecting for the Sql query is the 0 level element. is TM1DRILLDOWNMEMBER the correct formula to use to get all the parents of this element? or is that formula used to get all the children of an element?

[David Usherwood]

Looks right albeit I'm not an MDX expert as I loathe the language and use it when I have no alternative. DESCENDANTS would be what I'd think of using. But I suggest you put a debug statement in your TI to find out what it delivered.

[dumbom]

i think i have managed to get the Mdx code right as it doesnt show any errors in the asci output

Code: Select all

SubsetCreateByMdx(sSubName,'{['|sDimName|'].['|sParentElemName|'].Ancestors}');

but i am having difficulty in the Query tab. i have played around and taken the '' away from my parameters, and that works for everything except division. i get SQL statement failed if i remove the '' from Division. all the other elements are numeric values, where division is FA. i can drill to the lowest level if i use division or vdivision as my parameter in the query tab, but not to he higher level.

Code: Select all

Select 
Source, Journal, EntryGroup, Customer, CustomerName, Supplier, SupplierName,
 Invoice, SubJournal, StockCode, Description, substring(GlCode,1,4) as Account, substring(GlCode,6,2) as Division,
substring(GlCode,9,2) as BusinessUnit, substring(GlCode,12,2) as Location, GlCode, GlCodeDescription, EntryValue,
GlPeriod as vMonth , GlYear as Year, Reference, Comment, GRN, NetSalesValue, PurchaseOrder, PaymGrossValue, 
WarehouseFrom, WarehouseTo, PurchaseOrderLin, Job, JobDescription,
SalesOrder, SalesOrderLine, RegisterNumber, Asset, Line, JnlDate, EntryType, Branch, AssetCostCenter, AssetGroupCode,
AssetStatus,ADescription, DateSold, PurchaseDate, DisposedFlag, 'Actual' as Version
from tmpTrnDetail
where GlYear =?Year?
and (GlPeriod ) = ?vMonth?
and (substring(GlCode,9,2) = ?BusinessUnit?)
and (substring(GlCode,12,2) = ?Location?)
and (substring(GlCode,1,4) = ?Accounts?)
and (substring(GlCode,6,2) = '?Division?')

[declanr]

i think i have managed to get the Mdx code right as it doesnt show any errors in the asci output

Code: Select all

SubsetCreateByMdx(sSubName,'{['|sDimName|'].['|sParentElemName|'].Ancestors}');

but i am having difficulty in the Query tab. i have played around and taken the '' away from my parameters, and that works for everything except division. i get SQL statement failed if i remove the '' from Division. all the other elements are numeric values, where division is FA. i can drill to the lowest level if i use division or vdivision as my parameter in the query tab, but not to he higher level.

Code: Select all

Select 
Source, Journal, EntryGroup, Customer, CustomerName, Supplier, SupplierName,
 Invoice, SubJournal, StockCode, Description, substring(GlCode,1,4) as Account, substring(GlCode,6,2) as Division,
substring(GlCode,9,2) as BusinessUnit, substring(GlCode,12,2) as Location, GlCode, GlCodeDescription, EntryValue,
GlPeriod as vMonth , GlYear as Year, Reference, Comment, GRN, NetSalesValue, PurchaseOrder, PaymGrossValue, 
WarehouseFrom, WarehouseTo, PurchaseOrderLin, Job, JobDescription,
SalesOrder, SalesOrderLine, RegisterNumber, Asset, Line, JnlDate, EntryType, Branch, AssetCostCenter, AssetGroupCode,
AssetStatus,ADescription, DateSold, PurchaseDate, DisposedFlag, 'Actual' as Version
from tmpTrnDetail
where GlYear =?Year?
and (GlPeriod ) = ?vMonth?
and (substring(GlCode,9,2) = ?BusinessUnit?)
and (substring(GlCode,12,2) = ?Location?)
and (substring(GlCode,1,4) = ?Accounts?)
and (substring(GlCode,6,2) = '?Division?')

There have been quite a few questions about this (1 very recently) you can use the EXPAND function or my personal (arguably lazy) way is to do ...

Code: Select all

' string stuff = ''' | pDivision | '''

etc


Since the ' symbol is the string delimiter in tm1 it assumes that is the end of your string unless you specify otherwise.

[dumbom]

hi Declan

thanks for the response. unfortunately i'm not too sure wher eto put that code. could you elaborate please?

thanks,
Dale

[declanr]

hi Declan

thanks for the response. unfortunately i'm not too sure wher eto put that code. could you elaborate please?

thanks,
Dale

Dale,

In the code you provided it is reading the following as 1 string

Code: Select all

Select 
Source, Journal, EntryGroup, Customer, CustomerName, Supplier, SupplierName,
 Invoice, SubJournal, StockCode, Description, substring(GlCode,1,4) as Account, substring(GlCode,6,2) as Division,
substring(GlCode,9,2) as BusinessUnit, substring(GlCode,12,2) as Location, GlCode, GlCodeDescription, EntryValue,
GlPeriod as vMonth , GlYear as Year, Reference, Comment, GRN, NetSalesValue, PurchaseOrder, PaymGrossValue, 
WarehouseFrom, WarehouseTo, PurchaseOrderLin, Job, JobDescription,
SalesOrder, SalesOrderLine, RegisterNumber, Asset, Line, JnlDate, EntryType, Branch, AssetCostCenter, AssetGroupCode,
AssetStatus,ADescription, DateSold, PurchaseDate, DisposedFlag, 'Actual' as Version
from tmpTrnDetail
where GlYear =?Year?
and (GlPeriod ) = ?vMonth?
and (substring(GlCode,9,2) = ?BusinessUnit?)
and (substring(GlCode,12,2) = ?Location?)
and (substring(GlCode,1,4) = ?Accounts?)
and (substring(GlCode,6,2) = '

With this random bit at the end:

Code: Select all

?Division?')

... but nothing telling it that it should be combining them together into 1


EDIT - I also noticed a couple of 's up with the 'Actual'. I usually have my query in the prolog tab rather than having it in the datasource box (which i've just realised you probably aren't doing), I find this a much easier way to follow what is going on. You should do a search for the EXPAND function though as there are some well explained descriptions of that on here.

[dumbom]

With this random bit at the end:

CODE: SELECT ALL
?Division?')


... but nothing telling it that it should be combining them together into 1

that '?Division?' is quoting that parameter (the Division dimension). if i remove the quotes on either side of it, i get the SQL statement failed. i will have a look at your suggestion about EXPAND function and see if it helps.

[declanr]

With this random bit at the end:

CODE: SELECT ALL
?Division?')


... but nothing telling it that it should be combining them together into 1

that '?Division?' is quoting that parameter (the Division dimension). if i remove the quotes on either side of it, i get the SQL statement failed. i will have a look at your suggestion about EXPAND function and see if it helps.

Sorry, I have been sending you in completely the wrong direction based on the fact that I am used to passing my queries through the prolog all as 1 string.

What error message have you actually been getting?

[dumbom]

hi Declan

if i run the process, i dont get any error as long as i am using '?division?', and i can drill on the lowest levels in the cube fine with this. but when i try drill on a consolidated level, i get an error that says drill handle was successfully built but did not return a valid handle. check the message log. and the message log says "1840 [6] ERROR 2013-10-14 09:03:47.750 TM1.SQLAPI 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'FA'."

fa is an element in the division dimension.

as soon as i change '?division?' to ?division?, it tells me Sql statement failed on the query tab.if i try drill in the cube on the lowest level, it tells me drill handle was successfully built but did not return a valid handle. check the message log. and the message log says "5104 [12] ERROR 2013-10-17 05:59:24.907 TM1.SQLAPI S0022[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'FA'." and a similar error if itry drill at a higher level.

the end goal i am trying to achieve is to be able to drill at any level of the division dimension.

[dumbom]

hi Declan

if i run the process, i dont get any error as long as i am using '?division?', and i can drill on the lowest levels in the cube fine with this. but when i try drill on a consolidated level, i get an error that says drill handle was successfully built but did not return a valid handle. check the message log. and the message log says "1840 [6] ERROR 2013-10-14 09:03:47.750 TM1.SQLAPI 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'FA'."

fa is an element in the division dimension.

as soon as i change '?division?' to ?division?, it tells me Sql statement failed on the query tab.if i try drill in the cube on the lowest level, it tells me drill handle was successfully built but did not return a valid handle. check the message log. and the message log says "5104 [12] ERROR 2013-10-17 05:59:24.907 TM1.SQLAPI S0022[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'FA'." and a similar error if itry drill at a higher level.

the end goal i am trying to achieve is to be able to drill at any level of the division dimension.

[David Usherwood]

You need to follow my earlier suggestions and write debug statements ( asciioutput('test.csv', this_is_the_query_code_I_am_executing) ).