Grid Configurator does not show results for query with UNION

Manuel Kuebler

I have a Query with UNION and two parameter Date01 ans Date02. But the grid configurator does not show the results. The grid is empty.

SELECT     OCRD.CardCode, OCRD.CardName, OCRD.GroupCode, OCRG.GroupName, OCRD.validFor, CRD1.County, CRD1.Address2, CRD1.Address3, CRD1.Street, 
                      CRD1.ZipCode, CRD1.City
FROM         OCRD INNER JOIN
                      OINV ON OCRD.CardCode = OINV.CardCode INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
                      CRD1 ON OCRD.CardCode = CRD1.CardCode
WHERE     (OCRD.validFor = 'Y') AND (OINV.DocDate BETWEEN '[%Date01]' AND '[%Date02]') AND 
                      (CRD1.AdresType = 'B')
GROUP BY OCRD.CardCode, OCRD.CardName, OCRD.GroupCode, OCRG.GroupName, OCRD.validFor, CRD1.County, CRD1.Address2, CRD1.Address3, CRD1.Street, 
                      CRD1.ZipCode, CRD1.City, CRD1.AdresType
UNION
SELECT     OCRD.CardCode, OCRD.CardName, OCRD.GroupCode, OCRG.GroupName, OCRD.validFor, CRD1.County, CRD1.Address2, CRD1.Address3, CRD1.Street, 
                      CRD1.ZipCode, CRD1.City
FROM         OCRD INNER JOIN
                      OINV ON OCRD.CardCode = OINV.CardCode INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
                      CRD1 ON OCRD.CardCode = CRD1.CardCode
WHERE     (OCRD.validFor = 'Y') AND (OINV.DocDate BETWEEN '[%Date01]' AND '[%Date02]') AND 
                      (CRD1.AdresType = 'S')
GROUP BY OCRD.CardCode, OCRD.CardName, OCRD.GroupCode, OCRG.GroupName, OCRD.validFor, CRD1.County, CRD1.Address2, CRD1.Address3, CRD1.Street, 
                      CRD1.ZipCode, CRD1.City, CRD1.AdresType

SAP Business One 8.82

coresuite customize 4.35

ChristianB

Hi,

did you Setup "4. Column Configuration" allready?

Kind Regards

Christian

Paolo Manfrin

Hi Manuel,

seems like your query is returning NULL in some of the column which is not well digested (need to investigate if grid configurator or SAP fault).

I adjusted the query adding e,,g, ISNULL(CRD1.County,'') AS County and this did solve the issue.

Please check the column which are returning NULL and wrap them around with ISNULL.

This should solve the problem.

hth

paolo