small sql issue

[Hubertus]

1.) When creating a SQL-query then the other tabs (Simple Form and Rule Form) are inaccessible. Basically that's ok but the Business Object specified in the Simple Form Tab is still valid. Now I had OrderDetails specified in the (now inaccessible Simple Form tab) and made a query doing some selection on customer data. The query ran fine and gave me 12 results. But what I've seen where 12 (randomly selected ?) OrderDetails To avoid this you simply have to select the appropriate BO in the Simple Form Tab and after that switch to the SQL tab. I mean it works but maybe you could think about that in the next version.

2.) When creating the query I had to use the whole table name like BASTESTDOMAINDISPO_Kunde. This means I have to change the query everytime I publish the Business Space from the testing to the productive version. Is this correct or did I miss something ?

3.) The reason for the query was that I importet a csv file into a temporary table and after that I selected all entries that where new.

Code: Select all

SELECT BASTESTDOMAINDISPO_KUNDENIMPORT . *
FROM BASTESTDOMAINDISPO_KUNDENIMPORT
LEFT JOIN BASTESTDOMAINDISPO_KUNDE ON BASTESTDOMAINDISPO_KUNDENIMPORT.Adresse = BASTESTDOMAINDISPO_KUNDE.KHKID
WHERE BASTESTDOMAINDISPO_KUNDE.ID IS NULL 

Do I really need a SQL query for that or can I manage this situation using the rule language ?


Oh, and did I mention that I fell in love with AwareIM ??

Thanks a lot
Hubertus

[aware_support2]

Hubertus,

I am pretty sure you should be able to do it with the standard Aware IM functionality. Could you describe in more detail the scenario you are trying to implement so I can suggest a solution.

[Hubertus]

I've played with the following rule

Code: Select all

FIND ALL KundenImport 
FIND Kunde WHERE Kunde.KHKID=ThisKundenImport.Adresse 
IF NOT(EXISTS ThisKunde) Then CREATE Kunde WITH Kunde.Firma=ThisKundenImport.Name1,Kunde.KHKID=ThisKundenImport.Adresse 

To my understanding this should mean:
1.) Find all KundenImport
2.) For every record found by 1.) execute FIND Kunde WHERE ....
3.) In case 2.) returns no result then create a new Kunde using data coming from 1.)

Thanks a lot for any hints
Hubertus

[aware_support2]

I understand your task is following (correct me if I am wrong): for each record in an import file you need to create a new object instance if it does not already exist in the database. If this is correct, you can achieve this by adding the following rule to object KundenImport:

If NOT(EXISTS Kunde WHERE (Kunde.KHKID = KundenImport.Adresse)) Then
CREATE Kunde WITH Kunde.Firma = ThisKundenImport.Name1, Kunde.KHKID = ThisKundenImport.Adresse

Make object KundenImport non-persistent (if you do not want the imported data kept in the database) and import the data file into it with Validate Data option enabled.

Let me know how it worked.

[Hubertus]

Oh yes it works !!!!

The only problem is that I need to import WITH VALIDATION causing quite a terrible performance. Saying quite terrible means about less than 1 record per second on a P4/2.4GHz and 1 Gig RAM. At the moment 30 minutes are gone and the import table contains 1050 records. Dealing with about 2000 Records a single check for new Records seems to last about one hour

Isn't there another way where I can for example import all records without validation and afterwards check for new ones that need to be copied ?
Or any other way to speed this up ? The rule as you suggested is the only one (beside one automatically generated for filling a memo field with ' ')

Hubertus

[aware_support2]

Hubertus,

Yes, you can import data without validation (object KundenImport needs to be made persistent) and then run a process like the following to trigger the check/create rule:

FIND ALL KundenImport
UPDATE KundenImport

However, each instance will still be checked to see if there is a corresponding Kunde instance in the database. There is no way around it since the value of KundenImport.Adresse is different in each instance and so needs to be checked individually. This database query may take most of the processing time, which you can verify by inspecting the timestamps of records in the log (the logging should be set to most detailed level). You can compare it with the log when importing data with validation.

Another thing that may slow processing down is the log itself, especially if the Log Viewer is open. Try setting the logging to the minimal level and closing down the Log Viewer to see if it speeds up the processing.

[Hubertus]

The process

Code: Select all

IMPORT KundenImport FROM SystemSettings.ImportDateiKunden
FIND ALL KundenImport
UPDATE KundenImport

and the rule

Code: Select all

IF NOT (EXISTS Kunde WHERE(Kunde.KHKID = ThisKundenImport.Adresse)) THEN
 CREATE Kunde WITH Kunde.Firma=ThisKundenImport.Name1, Kunde.KHKID=ThisKundenImport.Adresse.........

take about HALF A MINUTE to complete on 2000 records !!

Many thanks for that,
Hubertus