Query with temporary table and variables

Ronald van Amelsvoort

I have a query which gets data from different sbo database. On this query there is now an PLD report. I want to transfer this report to the Coresuite designer. But when I run the designer, I get the message that that the datasource is empty
What to do?

Philipp Knecht

Hi Ronald

Which coresuite designer Version are you using?
You can also check "Log Queries" in the config tab and the move to the %temp%/swald/company/db/queries folder after executing the designer.

Then you can see which queries really were executed.

hth

Ronald van Amelsvoort

Hi Philipp

The version of the designer is 1.67
The Query is executed (which I can see in the log file). When I Take an advanced data band in the designer, and assign the query as data source, I dont see any fields. In the screenshot, you can see that the query works within SBO.

Ronald

Here is the query I already have (I changed the name of the databases):
/* SELECT FROM [dbo].[OCRY] T0 */
DECLARE @Landcode AS VARCHAR(30)
/* WHERE */
SET @Landcode = /* T0.Code */ '[%Landcode]'

/* SELECT FROM [dbo].[OCRD] T1 */
DECLARE @KlantCode AS VARCHAR(10)
/* WHERE */
SET @KlantCode = /* T1.CardCode */ '[%KlantCode]'

/* SELECT FROM [dbo].[@_BV] T3 */
DECLARE @BVK AS VARCHAR(8)
/* WHERE */
SET @BVK = /* T3.Name */ '[%BVK]'

/* SELECT FROM [dbo].[@SERPARAM] T4 */
DECLARE @ProForm AS VARCHAR(12)
/* WHERE */
SET @ProForm = /* T4.Name */ '[%ProForm]'


DECLARE @Cessielijst TABLE (Bedrijf Char(6), Proforma Char(12), TransId Char(10), Account Char(15), Klantnummer char(10), Naam char(100), Adres char(100), Postcode char(20), Plaats char(100), Land Char(6), Telefoon Char(20), Limiet decimal(10,2), IntrnMatch Char(10), ExtrMatch Char(10), MthDate Char(11), ToMthSum Decimal(11,2), BaseRef Char(10), Factuurdatum char(11), Leverdatum char(11), FactBedrD decimal(10,2), FactBedrSD decimal(10,2), FactBedrC decimal(10,2), FactBedrSC decimal(10,2), ExtraDays Char(5), Dgn char(5))

DECLARE @BV char(6)

/* BV */
SET @BV= 'BV'
INSERT INTO @Cessielijst
SELECT @BV, '', T0.TransId, T0.Account, T0.ShortName, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, T0.IntrnMatch, T0.ExtrMatch, T0.MthDate, T0.ToMthSum, T0.BaseRef, T0.RefDate, T02.U_LeverDat, T0.Debit, T0.SysDeb, T0.Credit, T0.SysCred, T10.ExtraDays, 0
FROM Company_NL..JDT1 T0 INNER JOIN Company_NL..OCRD T00 ON T0.ShortName = T00.CardCode
INNER JOIN Company_NL..OCTG T10 ON T00.GroupNum = T10.GroupNum
LEFT OUTER JOIN Company_NL..OINV T02 ON T0.BaseRef = T02.DocNum

WHERE T00.CardType='C' AND T0.IntrnMatch = 0 AND (T00.Country = @Landcode or @Landcode = '') and (T0.Shortname = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '')

/* GMBH */

SET @BV= 'GMBH'
INSERT INTO @Cessielijst
SELECT @BV, '', T0.TransId, T0.Account, T0.ShortName, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, T0.IntrnMatch, T0.ExtrMatch, T0.MthDate, T0.ToMthSum, T0.BaseRef, T0.RefDate, T02.U_LeverDat, T0.Debit, T0.SysDeb, T0.Credit, T0.SysCred, T10.ExtraDays, 0
FROM Company_DE..JDT1 T0 INNER JOIN Company_DE..OCRD T00 ON T0.ShortName = T00.CardCode
INNER JOIN Company_DE..OCTG T10 ON T00.GroupNum = T10.GroupNum
LEFT OUTER JOIN Company_DE..OINV T02 ON T0.BaseRef = T02.DocNum
WHERE T00.CardType='C' AND T0.IntrnMatch = 0 AND (T00.Country = @Landcode or @Landcode = '') and (T0.Shortname = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '')

/* SL */

SET @BV= 'SL'
INSERT INTO @Cessielijst
SELECT @BV, '', T0.TransId, T0.Account, T0.ShortName, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, T0.IntrnMatch, T0.ExtrMatch, T0.MthDate, T0.ToMthSum, T0.BaseRef, T0.RefDate, T02.U_LeverDat, T0.Debit, T0.SysDeb, T0.Credit, T0.SysCred, T10.ExtraDays, 0
FROM Company_ES..JDT1 T0 INNER JOIN Company_ES..OCRD T00 ON T0.ShortName = T00.CardCode
INNER JOIN Company_ES..OCTG T10 ON T00.GroupNum = T10.GroupNum
LEFT OUTER JOIN Company_ES..OINV T02 ON T0.BaseRef = T02.DocNum
WHERE T00.CardType='C' AND T0.IntrnMatch = 0 AND (T00.Country = @Landcode or @Landcode = '') and (T0.Shortname = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '')

/* BVBA */

SET @BV= 'BVBA'
INSERT INTO @Cessielijst
SELECT @BV, '', T0.TransId, T0.Account, T0.ShortName, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, T0.IntrnMatch, T0.ExtrMatch, T0.MthDate, T0.ToMthSum, T0.BaseRef, T0.RefDate, T02.U_LeverDat, T0.Debit, T0.SysDeb, T0.Credit, T0.SysCred, T10.ExtraDays, 0
FROM Company_BVBA..JDT1 T0 INNER JOIN Company_BVBA..OCRD T00 ON T0.ShortName = T00.CardCode
INNER JOIN Company_BVBA..OCTG T10 ON T00.GroupNum = T10.GroupNum
LEFT OUTER JOIN Company_BVBA..OINV T02 ON T0.BaseRef = T02.DocNum
WHERE T00.CardType='C' AND T0.IntrnMatch = 0 AND (T00.Country = @Landcode or @Landcode = '') and (T0.Shortname = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '')

/* PROFORMA */
Declare @Proforma AS Char(12)
SET @BV= 'BV'
SET @ProForma = 'Ja'
INSERT INTO @Cessielijst
SELECT @BV, @ProForma, '', '', T0.CardCode, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, '', '', '', 0, T0.DocNum, T0.DocDate, T0.U_LeverDat, T0.DocTotalSy, T0.DocTotalSy, 0, 0, T10.ExtraDays, ''
FROM Company_NL..ORDR T0 INNER JOIN Company_NL..OCRD T00 ON T0.CardCode = T00.CardCode
INNER JOIN Company_NL..OCTG T10 ON T00.GroupNum = T10.GroupNum

WHERE T00.CardType='C' and T0.U_Proforma = 'Y' AND (T00.Country = @Landcode or @Landcode = '') and (T0.CardCode = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '') AND (@ProForma = @ProForm or @ProForm = '')

SET @BV= 'GMBH'
SET @ProForma = 'Ja'
INSERT INTO @Cessielijst
SELECT @BV, @ProForma, '', '', T0.CardCode, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, '', '', '', 0, T0.DocNum, T0.DocDate, T0.U_LeverDat, T0.DocTotalSy, T0.DocTotalSy, 0, 0, T10.ExtraDays, ''
FROM Company_DE..ORDR T0 INNER JOIN Company_DE..OCRD T00 ON T0.CardCode = T00.CardCode
INNER JOIN Company_DE..OCTG T10 ON T00.GroupNum = T10.GroupNum

WHERE T00.CardType='C' and T0.U_Proforma = 'Y' AND (T00.Country = @Landcode or @Landcode = '') and (T0.CardCode = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '') AND (@ProForma = @ProForm or @ProForm = '')

SET @BV= 'SL'
SET @ProForma = 'Ja'
INSERT INTO @Cessielijst
SELECT @BV, @ProForma, '', '', T0.CardCode, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, '', '', '', 0, T0.DocNum, T0.DocDate, T0.U_LeverDat, T0.DocTotalSy, T0.DocTotalSy, 0, 0, T10.ExtraDays, ''
FROM Company_ES..ORDR T0 INNER JOIN Company_ES..OCRD T00 ON T0.CardCode = T00.CardCode
INNER JOIN Company_ES..OCTG T10 ON T00.GroupNum = T10.GroupNum

WHERE T00.CardType='C' and T0.U_Proforma = 'Y' AND (T00.Country = @Landcode or @Landcode = '') and (T0.CardCode = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '') AND (@ProForma = @ProForm or @ProForm = '')

SET @BV= 'BVBA'
SET @ProForma = 'Ja'
INSERT INTO @Cessielijst
SELECT @BV, @ProForma, '', '', T0.CardCode, T00.CardName, T00.Address, T00.ZipCode, T00.City, T00.Country, T00.Phone1, T00.CreditLine, '', '', '', 0, T0.DocNum, T0.DocDate, T0.U_LeverDat, T0.DocTotalSy, T0.DocTotalSy, 0, 0, T10.ExtraDays, ''
FROM Company_BVBA..ORDR T0 INNER JOIN Company_BVBA..OCRD T00 ON T0.CardCode = T00.CardCode
INNER JOIN Company_BVBA..OCTG T10 ON T00.GroupNum = T10.GroupNum

WHERE T00.CardType='C' and T0.U_Proforma = 'Y' AND (T00.Country = @Landcode or @Landcode = '') and (T0.CardCode = @KlantCode or @KlantCode = '') and (@BV = @BVK or @BVK = '') AND (@ProForma = @ProForm or @ProForm = '')

/*WEERGAVELIJST*/
SELECT Land + ' '+ UPPER(klantnummer) + ' ' + Bedrijf AS Sleutel, Bedrijf, ProForma, TransId, Account, UPPER(Klantnummer), Naam, Adres, Postcode, Plaats, Land, Telefoon, Limiet, IntrnMatch, ExtrMatch, MthDate, ToMthSum, BaseRef, Convert(smalldatetime,FactuurDatum,105) AS Factuurdatum, Convert(smalldatetime,LeverDatum,105) AS Leverdatum, FactBedrD, FactBedrSD, FactBedrC, FactBedrSC, ExtraDays, CASE WHEN LEFT(LeverDatum,1) LIKE '_' THEN STR(Datediff(day,LeverDatum,getdate()),4,0) ELSE ' *' + STR(Datediff(day,FactuurDatum,getdate()),4,0) END AS DGN FROM @Cessielijst

ORDER BY Klantnummer, Land, Bedrijf, FactuurDatum



Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/r-amelsvoort/Openfacturen.jpg]Openfacturen.jpg[/url]

Ronald van Amelsvoort

Thanks Philipp for your help.

The problem for the ones interested, was the the query name had a dot in it.

Example Query1.5.

Changed it to Query1 and al wordked.