Re: Access and ITHL01

I tried the DISTINCT keyword, but it brings less than half of the records, meaning that it also takes out valid records. I would have to add another column that makes valid equal records different for the DISTINCT keyword. I noticed it because we have 40 records on SQL server and access returns 80 records. After using the DISTINCT keyword I get 32 or so records.

I will check the [Code Desc] in Access. How do I check it on BPCS? I can access BPCS through the IBM System Navigator, using the Personal Communicator.

Thanks for your help

Date: Wed, 13 Mar 2013 09:28:00 -0700
From: brobins3d@xxxxxxxxx
To: bpcs-l@xxxxxxxxxxxx
Subject: Re: [BPCS-L] Access and ITHL01

On 3/12/2013 8:09 AM, Adalberto B.C. wrote:

Thanks for the reply. The BPCS version is 6.0 Release 02.
Here it's the SQL:

SELECT BPCSDB_ITHL01.TWHS, BPCSDB_ITHL01.TPFDV, BPCSDB_ITHL01.TPROD, BPCSDB_ITHL01.TTYPE, BPCSDB_ITHL01.TQTY, BPCSDB_ITHL01.TLOT, BPCSDB_ITHL01.TSCST, CALENFY06A09.FYEAR, CALENFY06A09.FPERIOD, [TQTY]*[TSCST] AS MULTIPLICA, [Code Desc].BU, BPCSDB_ITHL01.TRES, [Code Desc].Desc
FROM CALENFY06A09 INNER JOIN ([Code Desc] INNER JOIN BPCSDB_ITHL01 ON [Code Desc].IClass = BPCSDB_ITHL01.TCLAS) ON CALENFY06A09.TTDTE = BPCSDB_ITHL01.TTDTE
WHERE (((BPCSDB_ITHL01.TWHS)="02") AND ((BPCSDB_ITHL01.TTYPE)="R") AND ((CALENFY06A09.FYEAR)=2013) AND ((CALENFY06A09.FPERIOD)=5));

To straight up answer your original question, add the word DISTINCT
after the SELECT. Example "Select Distinct BPCSDB_ITHL01".

The downside to this approach is it would hide truly duplicative data.

Have you double-checked the [Code Desc] table to make sure there's no
duplicates there? I assume that it's a logical based on the IICL01. I
don't know if 6.0.2 added another key field such as a company number.

Bill
--
This is the BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.