SQL Challenge

Christian Audet

Christian Audet

Moderator
Staff member
Hi everybody,

I have this little challenge: Translate to NER this SQL statement.

Update F0911 set GLALTT = '2' where GLATT = 'X';

I was able to do it with a couple of table I/O and of course a new index on this table and I realy think that the only way to do it is with the help of a existing index and a new one.

Please before giving a answer try it because my code is working and for now I want to know if i'm stupid because my code look terrible.

Thank you all,
I will post the code here after some of your answer.

Christian Audet


Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Hi Christian,

I wasn't able to locate GLATT in F0911 just GLALTT. I suppose you missed it and the two fields are the same as GLALTT, am I right?

1.) Why did you create a new index, just for performance considerations?
2.) The "couple of Table I/O" means the "Select, FetchNext, While, Update, FetchNext, EndWhile" solution?
3.) Would you describe the new index(indicies) for us and send a copy of your "couple of Table I/O" ER code part?

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Hi Christian,

I have got a strange and silly idea. (I haven't tried it!)

1.) Create a new index for F0911 which contains the same fields in the same order than the primary unique of F0911 but extended with GLALTT on the end. (Generate it of course)
2.) Create a new Business View joining F0911 to itself (F0911).
* change the index for the master occurence of F0911 in the Business View to the newly created index
* make the join over all the fields of the primary unique key
* select GLALTT fields for the BSVW in the both occurences of F0911 in the BSVW Designer
3.) Issue a single Update Table I/O statement in the ER where:
* update is based on the new Business View instead of the table
* make your selection on the (first) occurence of GLALTT which is part of the index
* make your update mapping on the (second) occurence of GLALTT

It could seem very silly. I am very curious that could work or not, so please let us know if you have tried it. Thanks.

Good luck,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Hi Zoltan,

I tried you idea with the business view, but the View Design Aid don't let me join the two table on more than one field (when they are the same).

You were right, I did a error the field is ALTT. The query again is :
Update F0911 set GLALTT = '2' where GLALTT = 'X'.

look how I did it, it's working but the performance is bad.

------- Begin Code ----------------

VA frm_szEnvironnement_DL01 = "IVMTL400"
VA frm_HandleF0911_HF0911 = F0911.Open Handle
VA frm_cRecherche_ALTT = "X"
VA frm_cNouvelleValeur_ALTT = "2"
F0911(VA frm_HandleF0911_HF0911).Select
VA frm_cRecherche_ALTT = TK G/L Posting Code - Alternate T
F0911(VA frm_HandleF0911_HF0911).Fetch Next
VA frm_szValeur_KCO <- TK Document Company
VA frm_szValeur_DCT <- TK Document Type
VA frm_mnValeur_DOC <- TK Document (Voucher, Invoice, etc.)
VA frm_jdValeur_DGJ <- TK Date - For G/L (and Voucher) - Julian
VA frm_mnValeur_JELN <- TK Journal Entry Line Number
VA frm_szValeur_EXTL <- TK Line Extension Code
VA frm_szValeur_LT <- TK Ledger Type
While SV File_IO_Status is equal to CO SUCCESS
F0911(VA frm_HandleF0911_HF0911).Update
VA frm_szValeur_KCO = TK Document Company
VA frm_szValeur_DCT = TK Document Type
VA frm_mnValeur_DOC = TK Document (Voucher, Invoice, etc.)
VA frm_jdValeur_DGJ = TK Date - For G/L (and Voucher) - Julian
VA frm_mnValeur_JELN = TK Journal Entry Line Number
VA frm_szValeur_EXTL = TK Line Extension Code
VA frm_szValeur_LT = TK Ledger Type
VA frm_cNouvelleValeur_ALTT -> TK G/L Posting Code - Alternate T
F0911(VA frm_HandleF0911_HF0911).Fetch Next
VA frm_szValeur_KCO <- TK Document Company
VA frm_szValeur_DCT <- TK Document Type
VA frm_mnValeur_DOC <- TK Document (Voucher, Invoice, etc.)
VA frm_jdValeur_DGJ <- TK Date - For G/L (and Voucher) - Julian
VA frm_mnValeur_JELN <- TK Journal Entry Line Number
VA frm_szValeur_EXTL <- TK Line Extension Code
VA frm_szValeur_LT <- TK Ledger Type
End While

---------- End Code ---------------

Can you find someting better or faster

(Thank you for the help Zoltan)

Christian Audet




Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Wahhoo, I win the challenge!

Ok, this is going to be techie-weenie stuff, but sometimes OneWorld leaves us no choice! I fully intend to write a lengthy white-paper on this technique, but until then, here's a quick and dirty run down. Write your own ODBC interface!

This is 'fairly' complete and the savvy programmer will be able to determine how to finish the code (I can't give ALL of it away). Now, you'll notice that this is a Select, but you can easily replace the SQL with an update and take out the bind column command.


//Step 1,
//Connect to Datasource.
memset(ServerName,'\0',sizeof(ServerName));
memset(UserName,'\0',sizeof(UserName));
memset(Authentication,'\0',sizeof(Authentication));
memset(StatementText,'\0',sizeof(StatementText));
memset(&dsD98700B, 0, sizeof(dsD98700B));

/* Get the Object Librarian Data Source*/
strcpy((char *)dsD98700B.szTableName,
(const char *)"F0006");

idReturn = jdeCallObject("GetValidDataSource", NULL,
lpBhvrCom,lpVoid,(LPVOID)&dsD98700B,
(CALLMAP *)NULL,(int)0,(char *)NULL,
(char *)NULL,(int)0);
if (idReturn != ER_SUCCESS)
return ER_ERROR;


strcpy((char *)ServerName,
(const char *)dsD98700B.szDataSource);

iCounter=strlen(ServerName);
while(ServerName[iCounter--]==' ')
ServerName[iCounter+1]='\0';

sprintf(UserName,lpBhvrCom->SysPref.szUserName);
sprintf(Authentication,JDB_GetPassword( lpBhvrCom->hEnv ));

/* Look at this! Naked SQL code! put your query here!*/
sprintf(StatementText,"SELECT MCMCU FROM F0006");

retcode =SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle);

retcode =SQLSetEnvAttr(EnvironmentHandle,SQL_ATTR_ODBC_VERSION,(SQLPOINTER *)SQL_OV_ODBC3 ,0);

retcode =SQLAllocHandle(SQL_HANDLE_DBC, EnvironmentHandle, &ConnectionHandle);

retcode =SQLConnect((SQLHDBC) ConnectionHandle,
(SQLCHAR *) ServerName,
(SQLSMALLINT) strlen(ServerName),
(SQLCHAR *) UserName,
(SQLSMALLINT) strlen(UserName),
(SQLCHAR *) Authentication,
(SQLSMALLINT) strlen(Authentication));

retcode =SQLAllocHandle(SQL_HANDLE_STMT, ConnectionHandle, &StatementHandle);

//Step 2
//Initialize Handles



retcode =SQLExecDirect(StatementHandle,
(SQLCHAR *) StatementText,
(SQLINTEGER) sizeof(StatementText));

SQLNumResultCols(StatementHandle, &ColumnCountPtr);

SQLBindCol(StatementHandle,
ColumnCountPtr,
SQL_C_CHAR,
szColumnBuffer,
(ColumnSizePtr+1),
&StrLen_or_Ind);

//Step 3
//Perform the Query

retcode =SQLFetch(StatementHandle);

//Step 4
//Close the Connection
/************************************************************************
* Function Clean Up
************************************************************************/

SQLFreeHandle(SQL_HANDLE_STMT,StatementHandle);
SQLDisconnect(ConnectionHandle);
SQLFreeHandle(SQL_HANDLE_DBC,ConnectionHandle);
SQLFreeHandle(SQL_HANDLE_ENV,EnvironmentHandle);

return (ER_SUCCESS);



. . . . . . . . . . . .

This code is from a 'short' test. My *real* solution is about 5 times larger and can return multiple rows of data. That's actually much more difficult since the programmer must break the source into 3 pieces and pass pointers back to the applications(Open/Fetch Loop/Close). OW datastructures greatly limit how dynamic the return set can be and in most cases you have to write a new BSFN for each unique SQL command. Also to keep in mind, unified logon may not work with this since the password being fetched may not gain access to the server. If that's the case you'll need to (ahem) hard-code a user/pwd into the code. I leave it to you to decide how best to handle security if you fall into this category.

-------------------

Darren Ricciardi
OneWorld Whipping Boy



Darren Ricciardi - OneWorld Whipping Boy
 
Thank you Darren,

Do you think this will work with a ODBC connected to a AS/400 Database.

One question : All the SQL... fonction, what is the library for this?

I will test your code I hope tommorow and I will give you some new about it.


Christian Audet



Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Here's another solution to your problem. It involves slightly more prep work, but if you make it work, it'll be neat and simple. (I am not logged in to OneWorld currently so I can't vouch for the accuracy of the SQL statements.)

Create a database view on F0911. Include the GLALTT column TWICE in that view. This could be a simple statement as "create view FV0911 as select GLALTT, GLALTT as GLALTT2 from F0911". (Replace GLALTT2 with another data dictionary alias that has the same type as GLALTT). In OneWorld, create a table with the same name (FV0911) having the same columns as in the database view. DO NOT generate the table. Now you should be able to use this table in your Update statement. Use GLALTT as criteria and GLALTT2 as the field to be updated. It would be something like this:

FV0911.Update
x = GLALTT
2 -> GLALTT2

You might get an error in jde.log but it still works.

Muneeb Ahmad
Oracular Systems & Software Inc.
[email protected]
 
Yes, this works on clients connected to an AS/400. That's what I'm hooked up to. In fact, IBM claims to support ODBC 1 functionality. This means that with some minor changes you can run this in BSFNs compiled on the AS/400. Unfortunately, we're behind in our system upgrade and it doesn't work with the version we're on. The compile is fine, and data is *mostly* correct, but not dependable. So, for now we're only using this 'client side'.

Here is some more information you may find useful. . .Note the inclusions.


#include <Sql.h>
#include <Sqltypes.h>

/**************************************************************************
* Business Function: test
*
* Description: test
*
* Parameters:
* LPBHVRCOM lpBhvrCom Business Function Communications
* LPVOID lpVoid Void Parameter - DO NOT USE!
* LPDSD5500220 lpDS Parameter Data Structure Pointer
*
*************************************************************************/

JDEBFRTN (ID) JDEBFWINAPI test (LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSD5500220 lpDS)
{
/************************************************************************
* Variable declarations
************************************************************************/
SQLHENV EnvironmentHandle = '\0';
SQLHSTMT StatementHandle = '\0';
SQLHDBC ConnectionHandle;
char ServerName[30];
char UserName[15];
char Authentication[25];
char StatementText[200];
char Connection[50];
char szColumnBuffer[50];
char ColumnName[50];
unsigned char Sqlstate[5];
unsigned char messageBuffer[100];
short int ColumnCountPtr;
int iCounter;
SQLINTEGER StrLen_or_Ind;
SQLSMALLINT BufferLengthSm=0;
SQLSMALLINT NameLengthPtr;
SQLSMALLINT TextLengthPtr;
SQLSMALLINT DataTypePtr;
SQLSMALLINT DecimalDigitsPtr;
SQLSMALLINT NullablePtr;
SQLUINTEGER ColumnSizePtr;
SQLRETURN retcode;
SQLINTEGER NativeErrorPtr;
ID idReturn = ER_SUCCESS;


Again. .from my test code. . .no guarantees expressed, or implied ;)


Darren Ricciardi - OneWorld Whipping Boy
 
Hi Christian,

Thanks for your update.
I also tried this type of join on B7331 and this STUPID limitation also exists there.
Unfortunately F0911 hasn't any single field unique index neither as declared nor as un-declared. If one would be exists then would be possible to try my suggested method using that one.
(please, if somebody have a bit time /or much curiousity :) to try the method on a table where exists one single field unique index then please, share the results with us.)

Does anybody know any good reason why does this limitation exist in the Business View Design Aid? I was not able to figure it out.

Christian, you haven't mentioned your new index in your reply.
Your attached solution is OK except performance.
About your performance: If your selection/total ratio is low enough on the records in F0911 then try to create the index that I suggested in my previous reply and use it in the Select/FetchNext but not in Update instead of the primary.
I suppose you very well know, what kind of considerations do you have to make before you create a new index, mainly on a table with a large number of records like F0911 (storage space, extra maintenace time at insert/update/delete, etc.)

Good luck,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Hi Muneeb and Christian,

GLALTT have to be one of the fields of the index which was selected for the Update statement to be able to use it for selection in a single OneWorld Upadte ER statement.
The problem is that if you declare the index for FV0911 in OW Table Design then would it be work correctly if you do not generate the index and table? I am curious to it too.

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Christian,

I don't know if this is neccessary under the SQL Server Version you are under but here goes.

After creating the new index for F0911 did you update the Table Index Statistics?

regards,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15.1
 
Hi Zoltan,
I don't remember off the top of my head but you can probably still generate just the index.

Muneeb
 
Hi Everybody,

Thank to all for your solution.

A little fact about my F0911 and my new index : we are actually using 5GB only for F0911 (+- 5 million records), so everyting we do on it is VERY LONG.

My new index is on GLALTT only.

The process I have to do will process 10000 records on a daily basis and I'm doing it from OneWorld to World (Updating World Table).

For your information, the update of 7000 records is taking 14 minutes.

I apreciate all the solution that you are giving me and I have to consider one that will have to run every night automatically.

For up to now it's working with my solution (see earlier message) but it's slow. I plan to test the SQL in C next week, I will keep you updated.

Christian
P.S. Idid't have enough time to test the SQL Select in C yet.

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Hi Christian,

This is just silly idea but if it works it has to be really fast. Make a dummy table in JDE with just one field and make a SQL trigger on that table. Trigger can run update query for F0911. Somehow update that record (UBE or APPL) and trigger will run SQL update on server. That is bulk update and should be much faster then any fetch/update.

Hope this helps.

Bojan.
 
Christian,

Bojan's suggestion make me an other idea. Possible, it isn't fit to your situation but I put it here, who knows?
Have you considered to use one of the OneWorld Table Triggers?

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Hi Bojan & Zoltan,

The Table Trigger Idea is a "Great One" it's working fine, since my primary concern was to speed up the update (It was 13min.) we decided to have a trigger on a dummy table on the AS/400. So we are updating from One World a World Dummy Table a the Summy Table Trigger Update the F0911 table. The time to update is now 2min. (Great !)

Thank's to all

Christian Audet



Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Back
Top