 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Platinum Member |
aThis message has been edited. Last edited by: swati, | ||
|
| Expert |
WHERE ( INV_INVOICE_DATE_Y GE '&StartYRMTH' ) AND ( INV_INVOICE_DATE_Y LT '&EndYRMTH' ); I don't know what FOC_NONE01 is, but the error message is very clear: THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT Check the Master of the table you are accessing for the format of the column INV_INVOICE_DATE_Y. The coding of the WHERE statement is highly dependent on the formal of the column. Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Virtuoso |
Please show us what values &StartYRMTH and &EndYRMTH had *before* the -SET commands are being run. -TYPE StartYRMTH = &StartYRMTH -TYPE EndYRMTH = &EndYTMTH -SET &STYRMTH = EDIT(&StartYRMTH,'$$$$99/') | EDIT(&StartYRMTH,'9999'); -SET &EDYRMTH = EDIT(&EndYRMTH,'$$$$99/') | EDIT(&EndYRMTH,'9999'); -SET &StartYRMTH = &StartYRMTH | '01'; -SET &EndYRMTH = EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; -TYPE &StartYRMTH &EndYRMTH Also, please keep this tip in mind whenever you are posting either code or sample output; it really helps to avoid weird HTML formatting issues. Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Platinum Member |
Hi, Both the StartYRMTH and EndYRMTH are declared with FOC_NONE before the SET commands are used. 7.6.7 windows PDF,EXCEL. | |||
|
| Platinum Member |
Hi francis, How can we check the format of the columns in the master file with out going into the .mas file,i think there is a command to see the formats of all the columns in .mas file.. please tell........... 7.6.7 windows PDF,EXCEL. | |||
|
| Virtuoso |
Good, that's your answer then. You seem to be concatenating 'FOC_NONE' and '01' which results in the invalid WHERE condition being sent to the database. Fix that keeping in mind Francis' suggestion about your data field format and your retrieval will work. How will you fix it? well, for starters, how about you use a -DEFAULT value for each of those values different from FOC_NONE? Or, you could attempt something like replacing: -SET &StartYRMTH = &StartYRMTH | '01'; -SET &EndYRMTH = EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; by -SET &StartYRMTH = IF &StartYRMTH EQ 'FOC_NONE' THEN &StartYRMTH ELSE &StartYRMTH | '01'; -SET &EndYRMTH = IF &EndYRMTH EQ 'FOC_NONE' THEN &EndYRMTH ELSE EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; And see how it works! Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Expert |
Swati, why are you setting the two date variables to FOC_NONE, if its not a date, then its not going to work. To see the column formats, you can issue several commands. ? HOLD {master} (for an active hold file)
?FF (inside a TABLE for DEFINE)
?FF {master}
TABLE FILE SYSCOLUM
PRINT SEG.NAME
WHERE TBNAME EQ '{master}'
ENDWaz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
Hi waz, what should i replace the FOC_NONE with for the start and end dates..? it is declared by the other developer that start and end dates are with FOC_NONE.so as you told that dates are not suppose to use FOC_NONE then what should i declare for the dates...? please help.... 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
As this process feeds a WHERE clause to filter on a date range, then the variable should be populated with a Start date and End Date. Looks like they should be in the format YYYYMM. I also assume that these dates are supplied in some sort of launch page. Keep in mind we can only know what you post. As Francis says, what is the format of the field INV_INVOICE_DATE_Y ? Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
Waz, what u told is correct that the dates are sent from launch page through drop downs. the dates are in the format YYYYMM in the launch page i.e; eg 201005,201004.... i checked using ?FF (MASTER) i got the formats for all the columns and the funniest thing is that i dont see the column named INV_INVOICE_DATE_Y,But i see a column called INV_INVOICE_DATE,and the format of it YYMD. 7.6.7 windows PDF,EXCEL. | |||
|
| Platinum Member |
I guess i think we need to modify this following line for getting the output bcoz i am having he default StartYRMTH AS FOC_NONE,so this getting concatenated to 01 and resulting in FOC_NONE01 which is resulting to invalid where condition as njsden posted earlier,so what should i modify the below line... -SET &StartYRMTH = &StartYRMTH | '01'; But EndYRMTH looks gus as it is using EDIT STMT as below.. -SET &EndYRMTH = EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
Do you have any defines in your code. It may help to see the code, can you post it ? You could also issue the command ?FF inside the TABLE FILE, this will show all the fields available to the table request. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
Waz, here is the code i posted it here some 4 days back... http://forums.informationbuild...71057331/m/987109254 the problem is getting created because of those start and end dates only...need to fix those.. 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
Well.... Your code defaults the dates to FOC_NONE, so the question is if no dates are supplied, what do you want to do ? Do you want to not use the WHERE Clause causing the problem ? Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
if i remove the below where clause the report is running fine... WHERE ( INV_INVOICE_DATE_Y GE '&StartYRMTH' ) AND ( INV_INVOICE_DATE_Y LT '&EndYRMTH' ); but i am getting the total data in some 300 pages irrespective of any date range i pick,so my question How to alter the dates in such way that if i pick start date,enddate as 201001,201002 i need to get the corresponding data between those 2 monthe rather than all the data.... 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
I think we are going in circles. You are passing dates from the launch page. e.g. 201001 and 201002. You are getting the error FOC_NONE01. Looks like you are passing the dates in a different variable. Can you issue a -? & at the beginnin of the report, or check the launch page for the name of the date fields. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
i checked in launch page and the date fields are StartYRMTH EndYRMTH their format is YYYYMMThis message has been edited. Last edited by: swati, 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
Swati, Have you had any training and what can you see out of the window? T
| |||||||||
|
| Platinum Member |
Hi tony,no i dont have any formal training... I JUST CLICKED ON THE START AND END DATES DROPDOWNS AND IN THE PROPERTIES BOX I GOT StartYRMTH EndYRMTH as the name of those start and enddates... 7.6.7 windows PDF,EXCEL. | |||
|
| Expert |
If you haven't had any formal training to do what you are being asked to do, then it is unfair on you. Do you have someone in your vicinity to ask these questions? Hopefully someone who has developed these interfaces? If you do then badger them with your questions and perhaps your management might relent and give you some formal training. You wouldn't give a child a bike and expect them to ride it on a road without giving them support would you? - and I don't mean asking some body who lives across the road who knows how to ride a bike. T
| |||||||||
|
| Expert |
19 responses to correct a simple problem, a new record? Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Platinum Member |
Hi All, thanks for all your help, finally i used these as suggested by nsjden and my report worked fine... -SET &StartYRMTH = IF &StartYRMTH EQ 'FOC_NONE' THEN &StartYRMTH ELSE &StartYRMTH | '01'; -SET &EndYRMTH = IF &EndYRMTH EQ 'FOC_NONE' THEN &EndYRMTH ELSE EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; replacing -SET &StartYRMTH = &StartYRMTH | '01'; -SET &EndYRMTH = EDIT(AYM(&EndYRMTH,1,'I6YYM'),'999999') | '01'; Thanks all.... 7.6.7 windows PDF,EXCEL. | |||
|
| Platinum Member |
yes tony, my manager and other Higher authorities are planning to give us some IBI training in the coming months,so hope it might help.... 7.6.7 windows PDF,EXCEL. | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |