pro-iv adds FROM to SQL/ENDSQL statement
Posted 06 April 2006 - 07:10 PM
The following pro-iv logic is not working for me:
Pro-iv parses this as:
Which does not work very well when passed to sqlserver.
Everything within the SQL DYNAMIC / ENDSQL works properly from the query console.
This is not the first time I have run into this, but up until now I have been able to rephrase the sql to get around pro-iv's desire to insert a "from" into everything, but I cant figure out a way around this one.
Does anyone know how to turn off this idiotic behaviour?
I know that code usually does what I say instead of what I mean, but this time I think that I know better than pro-iv.
If only I could convince the pro-iv kernel of that.
Posted 10 April 2006 - 11:25 AM
We use a fair amount of dynamic SQL and we use it fairly restricted. Only in the format:
Other interesting SQL-constructs tend to be warped by Pro-IV. We've found no way around inserts by Pro-IV, which is annoying as they do not tend to help.
Mind you, this is for SQL-Server. Could be different for the other databases
Posted 10 April 2006 - 11:47 AM
Yes, its a bit annoying that you cant actually specify all the SQL you want.
The only thing that is supported by ProIV is the dynamic setting of the WHERE clause.
You can get other bits to work, but its all a bit random
Posted 10 April 2006 - 04:18 PM
IF $TEST = 'Y' THEN $COL = 'COLUMNA' ELSE $COL = 'COLUMNB';
But you can't just use this as a pass-thru. For example, you can't use joins in some cases, since PRO-IV will insert 'WITH(NOLOCK)' if the table is in lookup mode and it inserts it incorrectly if you use INNER JOIN or LEFT OUTER JOIN.
Posted 11 April 2006 - 01:55 PM
CREATE TABLE TEMP_RESULT
TERM_ID VARCHAR(8) NOT NULL,
TEMP_RESULT VARCHAR(100) NOT NULL
Then I can use
$TERM = @TERM
INSERT INTO TEMP_RESULT (TERM_ID, TEMP_RESULT)
VALUES (:$TERM,function(:$PARAM, :$PARAM2) )
in default logic, and
then before read set TERM_ID to @TERM and let pro-iv do the file read.
At least it works.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users