Jump to content


Photo
- - - - -

pro-iv adds FROM to SQL/ENDSQL statement


5 replies to this topic

#1 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 06 April 2006 - 07:10 PM

Has anyone seen anything like this before, and better yet, do you know how to turn it off?

The following pro-iv logic is not working for me:
SQL
DYNAMIC
SELECT
tsi_funct1(:$STR,’ABCDEF’)
FROM DC1REC
ENDSQL

Pro-iv parses this as:
FROM SELECT
tsi_funct1 (
WHERE
060406DDD ,
'ABCDEF' )
FROM DC1REC

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. :eek:

#2 jcduym

jcduym

    Member

  • Members
  • PipPip
  • 35 posts

Posted 10 April 2006 - 11:25 AM

Hi,

We use a fair amount of dynamic SQL and we use it fairly restricted. Only in the format:

SQL
DYNAMIC
SELECT FROM WHERE :$
ENDSQL

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

Hth.

#3 jcduym

jcduym

    Member

  • Members
  • PipPip
  • 35 posts

Posted 10 April 2006 - 11:29 AM

Sorry,

Forgot about the HTML thingies in posts, so some stuff has gone to the great bit-bucket in the sky.

SQL should read:


SQL
DYNAMIC
SELECT FROM table_name WHERE :$sql_statment
ENDSQL


Jan

#4 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 10 April 2006 - 11:47 AM

Hi,

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 ;)

Rob D.

#5 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 10 April 2006 - 04:18 PM

Actually, you can also substitute PRO-IV strings in the SELECT clause as long as they are just columns. For example:

IF $TEST = 'Y' THEN $COL = 'COLUMNA' ELSE $COL = 'COLUMNB';
SQL DYNAMIC
SELECT :$COL
FROM TABLE1
ENDSQL

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.

Mark Dexter

#6 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 11 April 2006 - 01:55 PM

I found a way around this. Once again not a nice way, but:

CREATE TABLE TEMP_RESULT
(
TERM_ID VARCHAR(8) NOT NULL,
TEMP_RESULT VARCHAR(100) NOT NULL
)

Then I can use
$TERM = @TERM
SQL
INSERT INTO TEMP_RESULT (TERM_ID, TEMP_RESULT)
VALUES (:$TERM,function(:$PARAM, :$PARAM2) )
ENDSQL
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