Hi,
is it possible with Pro-iv 4.6 to do a the following select :
$STRINGA = '%001%'
SQL
SELECT * FROM TABLE_NAME WHERE AN_COD LIKE :$STRINGA
ENDSQL
Because nothing rows return me.
if i put this select it all ok.
SQL
SELECT * FROM TABLE_NAME WHERE AN_COD LIKE '%001%'
ENDSQL
I don't put SQL DYNAMIC because pro-iv return an error.
Thanks
Marco
Click the link below to see the new game I'm developing!
Sql and Like
Started by
Marco.Bega
, Jan 30 2006 05:08 PM
18 replies to this topic
#2
Joseph Bove
-
- Members
-
- 756 posts
ProIV Guru
- Gender:Male
- Location:Ramsey, United States
Posted 30 January 2006 - 09:42 PM
Marco,
I'm almost definite that you need 5.5 in order to do the SQL DYNAMIC.
I don't think you'll get it to work in 4.6 - unless you used a nasty CASE statement.
hth,
Joseph
I'm almost definite that you need 5.5 in order to do the SQL DYNAMIC.
I don't think you'll get it to work in 4.6 - unless you used a nasty CASE statement.
hth,
Joseph
#3
mdexter
-
- Members
-
- 70 posts
Advanced
- Gender:Male
Posted 31 January 2006 - 12:33 AM
Your problem might just be quoting. Have you tried $STRINGA = "'%001%'" (i.e., putting the single quotes inside of double quotes)? Otherwise, if you need SQL DYNAMIC, Joseph is correct. This was added post-4.6. You can definitely do this with SQL DYNAMIC. HTH. Mark Dexter
#4
Neil Hunter
-
- Members
-
- 420 posts
ProIV Guru
- Gender:Male
- Location:Johannesburg, South Africa
Posted 31 January 2006 - 05:04 AM
Oracle or SQL?
Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless
If you running SQL you might need to pad % for the length of the field ie if AN_COD is A6 then $STRINGA = '%001%%'
Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless
If you running SQL you might need to pad % for the length of the field ie if AN_COD is A6 then $STRINGA = '%001%%'
#5
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 31 January 2006 - 08:01 AM
Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless
ProIV only selects the columns that you modify in logic... (with a few exceptions, like arrays and variables that might get inherited from other table reads)
Rob D.
#6
Neil Hunter
-
- Members
-
- 420 posts
ProIV Guru
- Gender:Male
- Location:Johannesburg, South Africa
Posted 31 January 2006 - 08:31 AM
In 4.6 ?Your original statement should work fine. I usually substitute the Select * with just select, ProIV selects the whole table regardless
ProIV only selects the columns that you modify in logic... (with a few exceptions, like arrays and variables that might get inherited from other table reads)
Rob D.
From what little i've read in trace files and from Oracle Trace files, the whole table is selected.
On the other hand I might be wrong
#7
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 31 January 2006 - 09:45 AM
hmm...
Dont have a 4.6 version around to test on, but that is what 5.0 does.
Maybe it was a 5.0 enhancment.
However, you do have to test with a very simple function, ie just an update with a single table with lots of columns on it, and in BW logic assign one of the columns... and see what the trace file says....
I would be interested to know what the results are....if anyone can test it on 4.6
Rob D.
Dont have a 4.6 version around to test on, but that is what 5.0 does.
Maybe it was a 5.0 enhancment.
However, you do have to test with a very simple function, ie just an update with a single table with lots of columns on it, and in BW logic assign one of the columns... and see what the trace file says....
I would be interested to know what the results are....if anyone can test it on 4.6
Rob D.
#8
Neil Hunter
-
- Members
-
- 420 posts
ProIV Guru
- Gender:Male
- Location:Johannesburg, South Africa
Posted 31 January 2006 - 10:21 AM
Only 4.6 machine I have access to is at a client. I'll try this when I get a chance to get on site. Would also love to know the result, as they also are always on the lookout for improving performance on their machine
#9
Cleve Haynes
-
- Members
-
- 172 posts
Expert
- Gender:Male
Posted 31 January 2006 - 10:26 AM
My experience with 4.6 was if you use transparent mode SQL, it only fetches the fields you refer to in the function:
Cleve
SQL SELECT FROM TABLENAME WHERE SOMETHING = 'A_VALUE' ENDSQLHowever, if you use full function mode SQL (this is where you define the fields to select), it will return the explict fields you define in your select statement - so this full function mode example should select all fields:
SQL SELECT * FROM TABLENAME WHERE SOMETHING = 'A_VALUE' ENDSQLWhereas this full function mode example will just select the specified fields:
SQL SELECT FIELD1, FIELD2, FIELD3 FROM TABLENAME WHERE SOMETHING = 'A_VALUE' ENDSQL
Cleve
#10
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 31 January 2006 - 11:41 AM
There is quite a bit to test and look at regarding performance with SQL, in this area.Only 4.6 machine I have access to is at a client. I'll try this when I get a chance to get on site. Would also love to know the result, as they also are always on the lookout for improving performance on their machine
Sometimes it takes a bit of work to figure out why ProIV is requesting all fields, but if you manage to write the function in a certain way, you can get great performance gains from this....
One area you should look into is Arrays. If you have file defs with large arrays, this can slow down reads alot. You need to use some tricks like an alternate file def with the arrays specified as individual fields (ie Balance1, Balance2, Balance3 instead of Balance(3)...
Although ProIV tries to figure out what fields you need , it does not try to figure out which array elements you are using, so it drags back all the data from Oracle for that array.
Rob.
#11
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 31 January 2006 - 11:44 AM
Yup - AFAIK V4.6 has always minimized the set of columns selected for "transparent" and "Type-1-full-function" SQL to those that are actually "used" by your function. This is a significant advantage of Type-1 over Type-2 - and it also decouples your code from possible changes to the actual database definitions.
Cleve - your first example is "Type-1" full function mode rather than "transparent" - and the others "Type-2".
As Rob pointed out though, you have to remember that if a filevar occurs in more than one referenced filedef (note that this includes using the same filedef more than once) then ProIV has to treat that filevar as "used" even if it's not mentioned in logic/fields/headers etc.
Marco - although I haven't used anything exactly like your original example, I would have expected it to work fine - if it doesn't it seem to me there's a bug of some kind. Are you on Oracle or something else?
As I understood it, the purpose of SQL DYNAMIC is to vary the whole where clause - you don't need it to vary a single bound value.
Cleve - your first example is "Type-1" full function mode rather than "transparent" - and the others "Type-2".
As Rob pointed out though, you have to remember that if a filevar occurs in more than one referenced filedef (note that this includes using the same filedef more than once) then ProIV has to treat that filevar as "used" even if it's not mentioned in logic/fields/headers etc.
Marco - although I haven't used anything exactly like your original example, I would have expected it to work fine - if it doesn't it seem to me there's a bug of some kind. Are you on Oracle or something else?
As I understood it, the purpose of SQL DYNAMIC is to vary the whole where clause - you don't need it to vary a single bound value.
Nothing's as simple as you think
#12
George Macken
-
- Members
-
- 248 posts
ProIV Guru
- Gender:Male
- Location:Co. Wicklow, Ireland
Posted 31 January 2006 - 11:46 AM
Hi
this is an example of LIVE code from our application - working on both
ORACLE 7 with pro-iv 4.6
ORACLE 9i with pro-iv 5.5
ER_NAME is type ALPHA on the ORACLE table ERDATA
IF $ER.SURNAME # '' THEN $WILD.SURNAME = '%' + $ER.SURNAME + '%';
SELECT FROM ERDATA WHERE ER_NAME LIKE :$WILD.SURNAME
hth
George
this is an example of LIVE code from our application - working on both
ORACLE 7 with pro-iv 4.6
ORACLE 9i with pro-iv 5.5
ER_NAME is type ALPHA on the ORACLE table ERDATA
IF $ER.SURNAME # '' THEN $WILD.SURNAME = '%' + $ER.SURNAME + '%';
SELECT FROM ERDATA WHERE ER_NAME LIKE :$WILD.SURNAME
hth
George
#13
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 31 January 2006 - 11:55 AM
In general ProIV couldn't possibly figure out what elements are used, because the "normal" of an array is with a variable subscript. To "know" what subset of the possible subscripts might actually be used requires a "global" understanding of the function and its execution that only the programmer has (we hope).Although ProIV tries to figure out what fields you need , it does not try to figure out which array elements you are using, so it drags back all the data from Oracle for that array.
If all subscripts are numeric literals then, as Rob's post suggests, it's not clear you really needed an array anyway. I would sympathize with ProIV's apparent decision that this was not a special case worth optimizing for.
Nothing's as simple as you think
#14
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 31 January 2006 - 12:22 PM
Just to explain why....
There is a large application used at many sites in Australia, that has their balance file with lots of field arrays like BAL(40), and PARTBAL(70), to hold different balances.
Some places loop through looking at all Bal arrays, while some updates just use say BAL(9)...
It made a huge difference in speed because of the Array.
Agreed that the underlying design was a bit 'wierd', but that was something that was unlikely to change as the whole system was based around it
It would be nice if there was some more optimization done around this, since it was giving us so much of a gain... but the work around made the code a bit messy....
Rob.
There is a large application used at many sites in Australia, that has their balance file with lots of field arrays like BAL(40), and PARTBAL(70), to hold different balances.
Some places loop through looking at all Bal arrays, while some updates just use say BAL(9)...
It made a huge difference in speed because of the Array.
Agreed that the underlying design was a bit 'wierd', but that was something that was unlikely to change as the whole system was based around it
It would be nice if there was some more optimization done around this, since it was giving us so much of a gain... but the work around made the code a bit messy....
Rob.
#15
Joseph Bove
-
- Members
-
- 756 posts
ProIV Guru
- Gender:Male
- Location:Ramsey, United States
Posted 31 January 2006 - 02:27 PM
Guys,
Follow up question to the ProIV traces and what is selected.
Is it the case that if you do not embed any SQL, but rely instead on ProIV converting a SEL-ONLY, SEL-PARTIAL or SEL-RANGE to SQL that it will select all columns every time?
From what I've seen in trace logs, I've thought this to be the case. However, based on my read of this thread, maybe I've not looked closely enough.
If the answer to my question is yes, then it seems that the best programming practice would be to embed SQL in every Update, Report, and LSCALL. Even if the SQL is no different than the SEL logic, it would be beneficial nearly always.
Thanks in advance,
Joseph
Follow up question to the ProIV traces and what is selected.
Is it the case that if you do not embed any SQL, but rely instead on ProIV converting a SEL-ONLY, SEL-PARTIAL or SEL-RANGE to SQL that it will select all columns every time?
From what I've seen in trace logs, I've thought this to be the case. However, based on my read of this thread, maybe I've not looked closely enough.
If the answer to my question is yes, then it seems that the best programming practice would be to embed SQL in every Update, Report, and LSCALL. Even if the SQL is no different than the SEL logic, it would be beneficial nearly always.
Thanks in advance,
Joseph
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
