Comparing two fields..

[#1]

I have created a simple app and need to compere the two fields OrderID (amos db) and order_id (agresso db). What I want is a list of values who excist in amos (OrderID)  but not in agresso (order_id)

[#2]

Check the Exist() funcion in the reference manual.

Cheers

Juan Pedro

[#3]

Hi Oivind,

  Use the Exist/Not Exist Functions in where class. See the help for syntax.

[#4]

Thanks, but I still have a problem with the syntax because the two fields is in two different databases:

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='kr # ##0,00;kr -# ##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;mai;jun;jul;aug;sep;okt;nov;des';

SET DayNames='ma;ti;on;to;fr;lø;sø';

ODBC CONNECT TO amos (XUserId is fLTccQVNPbcUE, XPassword is IJNXKbdNZLYOWSJOFE);

LOAD OrderID as AMOSID,

    LayoutID,

    CreatedBy,

    SentBy,

    ApprovedBy,

    DeliveryTermID,

    PaymentTermID,

    OrderPriorityID,

    StockClassID,

    CostCentreID,

    AccountCodeID,

    OfOrign,

    CurrencyCode,

    VendorID,

    BudgetCodeID,

    ContractID,

    DeliveryPlaceID,

    FormNo,

    FormType,

    FormStatus,

    BudgetAtLineItem,

    Title,

    CreatedDate,

    ApprovedDate,

    OrderedDate,

    ReqnApprovedDate,

    ReqnApprovedBy,

    LatestDeliveryDate,

    ConfirmationDate,

    ConfirmationRef,

    ReceivedDate,

    UserDefDate1,

    UserDefDate2,

    UserDefDate3,

    UserDefText,

    UserAddressID,

    BudgetDate,

    DeliveryID,

    Comment1,

    Comment2,

    EstimatedTotal,

    Shipping,

    PartPayment,

    ActualTotal,

    VendorAdvisedTotal,

    VendorDeliveryDate,

    LatestDeliveryDateForVendor,

    QueuedForTransfer,

    QueuedForBusinessToBusiness,

    Notes,

    WorkFlowStatusID,

    DeptID,

    ExportMarker,

    LastUpdated,

    ConvertedFrom,

    CustomClearanceContractID,

    QueuedForRemoteWorkFlow,

    AddressContactID,

    UserTableID,

    Criticality;

SQL SELECT *

FROM amos.amos.OrderForm;

ODBC CONNECT TO agrfom (XUserId is VGHFQQdMSTZeWWdd, XPassword is YfRbPacMSTRATWRV);

LOAD account,

    "allocation_key",

    "amend_no",

    amount,

    "amount_set",

    "apar_id",

    "apar_id2",

    "apar_type",

    "apar_type2",

    "arr_amount",

    "arr_val",

    "art_descr",

    "art_gr_id",

    article,

    "article_id",

    "att_1_id",

    "att_2_id",

    "att_3_id",

    "att_4_id",

    "att_5_id",

    "att_6_id",

    "att_7_id",

    "back_flag",

    "back_value",

    "bonus_type",

    "break_flag",

    "change_flag",

    client,

    "com_amount",

    "com_del_date",

    "com_val",

    "contract_id",

    "contract_ref",

    "cost_amount",

    "cur_amount",

    "currency",

    "deliv_date",

    "delivery_descr",

    "delivery_flag",

    "dim_1",

    "dim_2",

    "dim_3",

    "dim_4",

    "dim_5",

    "dim_6",

    "dim_7",

    "disc_code",

    "disc_percent",

    "disc_type",

    discount,

    "exch_rate",

    flag,

    forecast,

    "fund_chk",

    guarantee,

    invoiced,

    "kit_type",

    "last_update",

    "line_no",

    "line_no2",

    location,

    lot,

    "main_unit",

    multiplier,

    "old_forecast",

   "open_flag",

    "order_discount",

    "order_id" as AGRESSOID,

    "order_id2",

    period,

    "price_gr",

    "print_flag",

    "print_status",

    priority,

    "real_amount",

    "reserved_val",

    "ret_amount",

    "ret_val",

    "rev_del_date",

    "rev_price",

    "rev_status",

    "rev_val",

    "sales_amt",

    "sequence_no",

    "serial_no",

    status,

    "struct_flag",

    "sup_article",

    "tax_amount",

    "tax_code",

    "tax_cur_amt",

    "tax_percent",

    "tax_system",

    "temp_code",

    "template_id",

    "test_amount",

    "test_val",

    "unit_code",

    "unit_descr",

    "unit_price",

    "user_id",

    "value_1",

    "voucher_no",

    "voucher_type",

    "vow_amount",

    "vow_val",

    warehouse,

    agrtid;

SQL SELECT *

FROM agrfom.dbo.apodetail;

diff:

load AMOSID,

       AGRESSOID,

       LastUpdate;

where NOT Excist(AMOSID);