Hi,

I have a procedure that uses SQL Passthru code. I have some Amper Variables in the SQL Code. When I create an HTML Lauch Page for the procedure everything works fine until I try to select multiple values from my listboxes. Then I get an error message like
(FOC1400) SQLCODE IS 4145 (HEX: 00001031) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] An expression of non-b
: oolean type specified in a context where a condition is expected, near '
: AND'. [42000] Statement(s) could not be prepared. [] Deferred prepare co
: uld not be completed.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

It looks like the way the code is parsed is not acceptable by MS SQL. Consider the following...

ENGINE SQLMSS SET DEFAULT_CONNECTION WF_Dev
SQL SQLMSS PREPARE SQLOUT FOR
SELECT IND_SECT, EQUIP_CLASS, PROD_VOC, OE_BRAND, OE_NAME, OE_MODEL, ENG_SUPP, ENG_MODEL, ENG_COUNTRY
FROM OE_DATA_VIEW
WHERE (PROD_QTY > 0) AND
(IND_SECT IN(&IND_SECT))

If I select 'Marine Auxilliary' from the listbox on my launch page the WHERE clause looks like...

WHERE (PROD_QTY > 0) AND
(IND_SECT IN('Marine Auxilliary'))

This works OK.

If I select more than one item from the lisbox on my launch page the WHERE clause looks like...

WHERE (PROD_QTY > 0) AND
(IND_SECT IN('Marine Auxilliary' OR 'Marine Propulsion'))

This does not work because the correct SQL syntax should be...

WHERE (PROD_QTY > 0) AND
(IND_SECT IN('Marine Auxilliary', 'Marine Propulsion'))

Anybody know if this should work? If so, is there something different I need to do with my SQL Passthru code or with my Launch Page?

Thanks,

Dan

This message has been edited. Last edited by: Dan Pinault,

This sounds familiar. Do a search on STRREP.

The idea is to transform the OR-separated list to a comma-separated list, using a -SET statement with STRREP(), before you get to the line in the report that references the amper var.
--------------------------------------------------------------------------------------------------
Syntax: How to Replace Character Strings

STRREP(inlength, instring, searchlength, searchstring, replength,repstring, outlength, outstring)
--------------------------------------------------------------------------------------------------

So

-SET &dummy= &IND_SECT.(yada yada).;     // establish &IND_SECT early on
-SET &before=''' OR ''';
-set &after =''', ''';
-SET &IND_SECT=STRREP(&IND_SECT.LENGTH,&IND_SECT, &before.LENGTH,&before, &after.LENGTH,&after, &IND_SECT.LENGTH,'A&IND_SECT.LENGTH');

and then your report

This message has been edited. Last edited by: j.gross,

Thanks for the technique j.gross.

I was hoping to avoid the extra coding. One would think that if the WebFOCUS parser can turn FOCUS procedures into properly formed SQL that it would be able to pass parameters to a SQL Passthru procedure.

In any event. Thanks for the workaround. I'll test it out right now.

Regards,

Dan

j.gross - The technique works great!

The other thing I had to remember was to put all my WHERE clauses on separate lines so those who defaulted to FOC_NONE would go away and still leave the others in tact.

Thanks again!

Dan

One last question (OK, well 2 really)...

I'm looking a the documentation on the STRREP function. Just to be clear, the reason &before is set to ''' OR ''' is because it requires three single quotes to get the result of ' OR ', is that right?

Also, you have the output string as 'A&IND_SECT.LENGTH'. Can you tell me why you have it formatted like that instead of just using 'IND_SECT'?

Thanks,

Dan

FOC_NONE is more complicated to use in a SQL SELECT statement than in a WebFOCUS TABLE statement.

In TABLE you can have multiple WHERE statements, with a FOC_NONE removing each individual WHERE statement.

In SELECT you have only one WHERE statement, with AND combining the multiple clauses, so it's important where the AND is coded.

-SET &SEL1 = 'FOC_NONE';

SQL DB2 SELECT TIME_DIM_KEY FROM TIME_D
WHERE TIME_DIM_KEY > 100 AND 
TIME_DIM_KEY < &SEL1
FETCH FIRST 10 ROWS ONLY;
END
-RUN

will not work, while

-SET &SEL1 = 'FOC_NONE';

SQL DB2 SELECT TIME_DIM_KEY FROM TIME_D
WHERE TIME_DIM_KEY > 100
AND TIME_DIM_KEY < &SEL1
FETCH FIRST 10 ROWS ONLY;
END
-RUN

will.

"Just to be clear, the reason &before is set to ''' OR ''' is because it requires three single quotes to get the result of ' OR ', is that right?"
--yes. Within a quote-literal, single-quote marks are represented by a pair of single-quotes. The literal ends after an even-numbered quote-mark not immediately followed by another one.

"Also, you have the output string as 'A&IND_SECT.LENGTH'. Can you tell me why you have it formatted like that instead of just using 'IND_SECT'?"
-- In DEFINE you can code the final argument as either a format literal (like 'A24') or the name of a variable with the same format as the desired result (typically, the very variable that you are defining -- it 'exists' and has a fixed format, regardless of its current content).

In Maintain's COMPUTE you can only use the latter form.

In dialog manager -SET you can, in principle, use either form -- but you generally cannot use the name of the variable you are setting, because it does not 'exist' at the time of evaluation; and using another variable will overlay that one's value (see example below) -- so I generally stick with 'Ann'.

-SET &X1='ABCDEF';
-SET &X2=REVERSE(&X1.LENGTH,&X1,&X1);
-SET &X3=REVERSE(&X1.LENGTH,&X1,'A6');
-? &X

produces:

 -SET &X1='ABCDEF';                 
 -SET &X2=REVERSE(06,ABCDEF,ABCDEF);
 -SET &X3=REVERSE(06,FEDCBA,'A6');  <== &X1 got reversed!
 CURRENTLY DEFINED & VARIABLES STARTING WITH 'X':
 &X1           = FEDCBA  (!)
 &X2           = FEDCBA
 &X3           = ABCDEF  (!)