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.

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'

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.

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.

Hi,
Is there a way in WF where there is a division by zero, and it needs to be displayed as N/A.

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.

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.

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.