SQL command in IQD file

[mewyeen]

I have IQD file contain querying a progress database using the ODBC OpenLink Generic ODBC Driver.  Below is the iqd file.  This iqd is work in the transformer but when I open the catalog to link to this database and search the table, I can't find the field T1.ih_slspsn[1] (6th item in the select statement) in the ih_hist table.  I have confirm that the field is exist in the progress database but why it can't be view in the inpromptu?

COGNOS QUERY
STRUCTURE,1,1
DATABASE,MFG Pro QAD (China)
BEGIN SQL
{select
        T1.ih_inv_date,
        T2.pt_abc,
        UPPER(TRIM(T1.ih_cust)),
        T1.ih_inv_nbr,
        UPPER(TRIM(T4.idh_part)),
        T1.ih_slspsn[1],
       Int(T4.idh_qty_inv * ((T4.idh_price / T1.ih_ex_rate) * T1.ih_ex_rate2)),
     Int(T4.idh_qty_inv * T4.idh_std_cost),     
      Int((((T4.idh_price / T1.ih_ex_rate) * T1.ih_ex_rate2) - T4.idh_std_cost) * T4.idh_qty_inv),
      Int((T4.idh_qty_inv * (T4.idh_price / T1.ih_ex_rate) * T1.ih_ex_rate2)/ (T7.exr_rate2 / T7.exr_rate)),
       Int((T4.idh_qty_inv * T4.idh_std_cost ) / (T7.exr_rate2 / T7.exr_rate)),
       Int(((((T4.idh_price / T1.ih_ex_rate)  * T1.ih_ex_rate2) - T4.idh_std_cost) * T4.idh_qty_inv) / (T7.exr_rate2 / T7.exr_rate)),
        int(T4.idh_qty_inv),
        'WGQPROD',
        date(T8.glc_per, 1, T8.glc_year),
        UPPER(TRIM(T3.cm_region)),
        UPPER(TRIM(T9.ad_ctry)),
        TRIM(T3.cm_sort),
        UPPER(TRIM(T2.pt_prod_line)),
        UPPER(TRIM(T2.pt_group)),
        TRIM(T2.pt_desc1)
        from pro1.idh_hist T4
             JOIN pro1.pt_mstr T2 ON T4.idh_part = T2.pt_part   
             JOIN pro1.sct_det T6 on ((upper(T6.sct_sim) = 'AVG' ) and (T4.idh_site = T6.sct_site) and (T4.idh_part = T6.sct_part))             
             JOIN pro1.ih_hist T1 ON ((T4.idh_inv_nbr = T1.ih_inv_nbr) and (T4.idh_nbr = T1.ih_nbr))
             JOIN pro1.exr_rate T7 on ((upper(T7.exr_curr1) = 'USD') and (upper(T7.exr_curr2) = 'CNY') and
                      (T7.exr_start_date <= T1.ih_inv_date) and (T7.exr_end_date >= T1.ih_inv_date))
             JOIN pro1.glc_cal T8 on (T1.ih_inv_date >= T8.glc_start and T1.ih_inv_date <= T8.glc_end)
             JOIN pro1.cm_mstr T3 on (T1.ih_cust = T3.cm_addr)
             JOIN pro1.ad_mstr T9 ON (T3.cm_addr = T9.ad_addr and T9.ad_type = 'customer')
             JOIN pro1.tx2d_det T10 ON (T4.idh_inv_nbr = T10.tx2d_ref and T4.idh_nbr = T10.tx2d_nbr and
                                        T4.idh_line = T10.tx2d_line and T10.tx2d_tr_type = '16')
             }
END SQL
COLUMN,0,Date
COLUMN,1,ABC Class Code
COLUMN,2,Customer Code
COLUMN,3,Invoice #
COLUMN,4,Part No
COLUMN,5,Salesman Code
COLUMN,6,Total Sales (RMB)
COLUMN,7,Total Average Cost (RMB)
COLUMN,8,Total Sales Margin (RMB)
COLUMN,9,Total Sales (USD)
COLUMN,10,Total Average Cost (USD)
COLUMN,11,Total Sales Margin (USD)
COLUMN,12,Invoice Qty
COLUMN,13,Site
COLUMN,14,Fiscal Date
COLUMN,15,Customer Region Code
COLUMN,16,Customer Country Code
COLUMN,17,Customer Name
COLUMN,18,Product Line Code
COLUMN,19,Item Group Code
COLUMN,20,Item Desc

[openlink]

Hi

Looking at the field T1.ih_slspsn[1] this would appear to be a progress array field, in which case you need to have the OpenLink ODBC Driver configured to use tableview which is required for array fields to be visible to ODBC applications as detailed at:

http://docs.openlinksw.com/st/dsnconfwin.html#progview

If you have not configured tableview then this column would not be visible via metadata in your ODBC application (cognos).

One other point, specifically which OpenLink ODBC Driver are you using as Tableview is only support in our SQL-89 and not SQL-92 Progress drivers ?

Note if you have a support and maintenance contract with us you can log a support case at:

http://support.openlinksw.com

and one of our consultants will be assigned to assist in resolving your issue.

Best Regards
Hugh Williams
Professional Services
OpenLink Software
Web: http://www.openlinksw.com
Support: http://support.openlinksw.com
Forums: http://boards.openlinksw.com/support

[mewyeen]

Hi,

I'm using the Multi Tier ODBC Setup Version 5.00.

Regards,
Mew Yeen