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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    DataFlow is not recognizing global variable set in procedure
Go
New
Search
Notify
Tools
Reply
  
DataFlow is not recognizing global variable set in procedure
 Login/Join
 
Platinum Member
posted
Hello,

I am trying to use the variables in data flow filter which are setup in the procedure.
In the procedure able to set the &FROMDATE and &TODATE
-DEFAULT &&FROMDATE='&&FROMDATE'
-DEFAULT &&TODATE='&&TODATE'

Able to see the values when run FromDate:09/20/2007 06:00:00
ToDate:09/21/2007 06:00:00
Tried to used in the data flow during the filter
Here is the sql looks like
SELECT
T1.LINECODE ,
T1.TNUMBER ,
FROM
S_GETFABLOTMOVESCOMPLIANCYASOBJECT_WS T1
WHERE
T1.STRFROMDATETIME = '&&FROMDATE' AND
T1.STRTODATETIME = '&&TODATE'

But it error out ans shows in the log
(FOC44540) SOAP Request got error. Response Status : 400 ; Reason: Bad
Now if pass the hard code value in sql statement it works fine.

Let me know if I am doing wrong or suggestion.
Thanks in advance.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Two questions:
(1) How are STRFROMDATETIME and STRTODATETIME defined?
(2) Do you get the same error when you click on the 'check SQL' button?

One thing I can think to try right off the bat: add .EVAL to your variables like this: '&&FROMDATE.EVAL'


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Master
posted Hide Post
What database are you using. We use Oracle and in this case we have to use the TO_DATE function. For example,

T1.STRFROMDATETIME = TO_DATE('&&FROMDATE','YYYYMMDD') AND 
T1.STRTODATETIME = TO_DATE('&&TODATE','YYYYMMDD')


The reason for this is that Oracle fields are datetime fields and &&FROMDATE is just a string, so it needs to be converted to an Oracle date.

The only problem with this is that, since there is no time portion to &&FROMDATE, the date will be converted to midnight and if T1.STRFROMDATETIME and/or T1.STRTODATETIME have any time other than midnight, the tests will fail.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi All,

Thanks for responding!
My source is web service and target is oracle db.

Jessica,
-SET &FROMDATE = EDIT(&FOX1, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');
-SET &TODATE = EDIT(&TOX2, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');
-TYPE &FROMDATE
-TYPE &TODATE
-DEFAULT &&FROMDATE='&&FROMDATE'
-DEFAULT &&TODATE='&&TODATE'
When I click the check sql button it say &FromDate missing.
Here is the summary when run the flow

Request - flow02 (Owner: webfocus) submitted.
DEP_3: procedure prc_parameterset_psa started.
STRFROMDATE EQ '09/23/2007 06:00:00' AND STRTODATE EQ '09/24/2007 06:00:00' ;
09/23/2007 06:00:00
09/24/2007 06:00:00
20070923
CUTOFFDT: 20070923
DEP_3 prc_parameterset_psa Return Code = 0
rvdw_busobjects_metadata/t6 type Oracle New target
Issuing PREPARE
Request will process data via NON-Pass Through (NON-APT)
HOLD file will be created for output file named: SQLIN.
(FOC44540) SOAP Request got error. Response Status : 400 ; Reason: Bad
Request
1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0
Return Code = 18708

I tried to used .EVAL also didn't work.
Let me know any suggestion to or modification to it.
How can I check the what the value for data flow is used before query the web service.

Thanks,
Viral.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Reply With QuoteReport This Post
<B_B>
posted
Hi,
Is there a way in WF where there is a division by zero, and it needs to be displayed as N/A.
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
B_B, would you please start a new thread for your question as it has nothing to do with this thread.

Viral, it seems as you may be mixing your variables. Variables with a single & are local variables. The ones with the double && are global variables. While you may have the 'name' part the same, &&FROMDATE and &FROMDATE are two different variables. I see from your last thread that you are setting &FROMDATE but in your SQL, it has &&FROMDATE. Depending on where you execute your stored procedure and/or how you call your data flow, you may be able to simply use local variables (&FROMDATE). Try making them all be the same, either local or global, not both.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Master
posted Hide Post
quote:
WHERE
T1.STRFROMDATETIME = '&&FROMDATE' AND
T1.STRTODATETIME = '&&TODATE'

Your WHERE STATEMENT SHOULD READ
WHERE
TO_DATE(T1.STRFROMDATETIME,'MM/DD/YYYY HH:MI:SS') >= TO_DATE('&FROMDATE.EVAL','MM/DD/YYYY HH:MI:SS') AND
TO_DATE(T1.STRTODATETIME,'MM/DD/YYYY HH:MI:SS') <= TO_DATE['&TODATE.EVAL','MM/DD/YYYY HH:MI:SS')

If you are doing this from within PLSQL remove the .EVAL, in WebFocus you need it. Also if you don't use the => and <= you will get only the from date/time exactly equal to the from date/time and the to date/time exactly equal to the to date/time not the entire range.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi PBrightwell,

I used web service as a source and need to pass a equal and web service does the data gathering for me.
Jessica you are right i was doing first &FromDate and then now used &&FromDate. now removed the default statement and setting up
-SET &&FROMDATE = EDIT(&FOX1, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');
-SET &&TODATE = EDIT(&TOX2, '99/99/9999 ') |
EDIT('06:00:00','99:$99:$99');

it started working but in my dataflow need to change like
T1.STRFROMDATETIME = '&&FROMDATE.EVAL' AND
T1.STRTODATETIME = '&&TODATE.EVAL'

It's working.
Thanks all for your inputs.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    DataFlow is not recognizing global variable set in procedure

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