[WD21] Query returns code instead of file field value

Startbeitrag von Al am 31.01.2017 19:28

Hello

My run of bad luck with queries continues
I used the wizard to create a query involving 7 files.
The main file has joins to the other 6 to bring back other info and in two cases files that are joined to the main file have other files joined to them

In the instance where the join uses the primary key of the transaction record to retrieve the creditor code from the joined creditor file,instead of returning the creditors code string it returns "middle(creditorcode," It does this for every row it returns.

Presumably this is something the SQL process generates ?
There is no instance of the middle() function anywhere in the window that initiates the queries or in any of the queries so I am baffled as to it's origin.

Of course, with the other bug in the wizard, I can't remove the reference to the creditor file because that corrupts to query as the wizard process is incapable of deleting a file without corrupting the query.

I have "fixed" the problem by doing a separate lookup to get the code and insert it into the table

This is the SQL code from the wizard

SELECT
Crtrans.CTCLEUNIK AS CTCLEUNIK,
Crtrans.InvoiceNo AS InvoiceNo,
Crtrans.ActualCost AS ActualCost,
Crtrans.JobCost AS JobCost,
Crtrans.ClientCost AS ClientCost,
Crtrans.TransactionDate AS TransactionDate,
Crtrans.DataEntryDate AS DataEntryDate,
Crtrans.DataEntryTime AS DataEntryTime,
Crtrans.TransactionReference AS TransactionReference,
Crtrans.TransactionType AS TransactionType,
Crtrans.ClaimFinPeriod AS ClaimFinPeriod,
Crtrans.FinPeriod AS FinPeriod,
Crtrans.Quantity AS Quantity,
Crtrans.ProFormaEntry AS ProFormaEntry,
Crtrans.UOM AS UOM,
Crtrans.GstAmount AS GstAmount,
Crtrans.ActualRate AS ActualRate,
Crtrans.JobRate AS JobRate,
Crtrans.ClientRate AS ClientRate,
Crtrans.TransactionLinkNo AS TransactionLinkNo,
Crtrans.BHCLEUNIK AS BHCLEUNIK,
Crtrans.CRCLEUNIK AS CRCLEUNIK,
Crtrans.DrGlAccount AS DrGlAccount,
Crtrans.CrGlAccount AS CrGlAccount,
Crtrans.JCSourceCleunik AS JCSourceCleunik,
Crtrans.JCSourceFile AS JCSourceFile,
Crtrans.JOCLEUNIK AS JOCLEUNIK,
Crtrans.FACLEUNIK AS FACLEUNIK,
Crtrans.OBStatus AS OBStatus,
Crtrans.YearFlag AS YearFlag,
Crtrans.YearEndNumber AS YearEndNumber,
Crtrans.GLJournal AS GLJournal,
Crtrans.ChqNo AS ChqNo,
Crtrans.FAJournalType AS FAJournalType,
Crtrans.EMCLEUNIK AS EMCLEUNIK,
Crtrans.DJCLEUNIK AS DJCLEUNIK,
Crtrans.EXCLEUNIK AS EXCLEUNIK,
Crtrans.ReversingTransaction AS ReversingTransaction,
Crtrans.MatchedTransaction AS MatchedTransaction,
Crtrans.GDCLEUNIK AS GDCLEUNIK,
Glacc.GLAccountNo AS GLAccountNo,
Glacc.AccountName AS AccountName,
Glacc.PlantWBType AS PlantWBType,
Glacc2.GLAccountNo AS GLAccountNo_Gl,
Glacc2.AccountName AS AccountName_Gl,
Glacc2.PlantWBType AS PlantWBType_Gl,
Lookups.ReturnString AS ReturnString,
Lookups2.ReturnString AS ReturnString_Lo,
Glacc.DebitAccount AS DebitAccount,
Glacc2.DebitAccount AS DebitAccount_Gl,
Creditor.CreditorCode AS CreditorCode, //////////////////////////////////
Crtrans.TransLinkSrc AS TransLinkSrc,
Crtrans.UserId AS UserId,
Crtrans.NWrkUserId AS NWrkUserId,
Crtrans.ViewNum AS ViewNum,
Lookups.Description AS Description,
Lookups2.Description AS Description_Lo
FROM
(
(
(
(
Crtrans
LEFT OUTER JOIN
(
Glacc Glacc2
LEFT OUTER JOIN
Lookups Lookups2
ON Glacc2.PlantWBType = Lookups2.LUCLEUNIK
)
ON Crtrans.CrGlAccount = Glacc2.GLCLEUNIK
)
LEFT OUTER JOIN
Glacc
ON Crtrans.DrGlAccount = Glacc.GLCLEUNIK
)
LEFT OUTER JOIN
Lookups
ON Glacc.PlantWBType = Lookups.LUCLEUNIK
)
LEFT OUTER JOIN
Creditor
ON Creditor.CRCLEUNIK = Crtrans.CRCLEUNIK //////////////////////////////
)
INNER JOIN
FAssets
ON FAssets.FACLEUNIK = Crtrans.FACLEUNIK
WHERE
(
Crtrans.FACLEUNIK = {pFACLEUNIK}
AND Crtrans.YearEndNumber = {pYearEndNumber}
)




Regards
Al

Antworten:

Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.