Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
a
 Login/Join
 
Platinum Member
posted
a

This message has been edited. Last edited by: swati,
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi,
Both the StartYRMTH and EndYRMTH are declared with FOC_NONE before the SET commands are used.


7.6.7
windows
PDF,EXCEL.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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}'
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6314 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6314 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6314 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6314 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6314 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
i checked in launch page and the date fields are
StartYRMTH
EndYRMTH
their format is YYYYMM

This message has been edited. Last edited by: swati,


7.6.7
windows
PDF,EXCEL.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Swati,

Have you had any training and what can you see out of the window?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 160 | Location: Atlanta,GA | Registered: July 16, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.