in de sql van document kop zal een extra join nodig zijn.
left join countries cr on cr.countries_id = WPosCountries_id
left join countries crd on crd.countries_id = WDelCountries_id
Nu kan je in de select de land velden toevoegen.
cr.countries_name Land,
crd.countries_name AfleverLand,
Voorbeeld van de hele sql
SELECT
dochead.WDocID DocID,
dochead.SDocNo DocNummer,
dochead.DDate Datum,
Account.SMainAccountCode HoofdRekeningCode,
Account.SSubAccountCode SubRekeningCode,
Account.SDescription Omschrijving,
Account.SAccountCode RekeningCode,
dochead.FInvoiceDiscount FactuurKorting,
Account.WAccountID RekeningId,
dochead.SReference UWreferentie,
dochead.DDueDate BetaalDatum,
Docgroup1.SDescription DocGroep1,
Docgroup2.SDescription DocGroep2,
Postal1.SDescription StraatHuisNr,
Postal2.SDescription Plaats,
cr.countries_name Land,
Postalcode.SDescription Postcode,
Delpostal1.SDescription AfleverStraatHuisnr,
Delpostal2.SDescription AfleverPlaats,
crd.countries_name AfleverLand,
Delpostalcode.SDescription AfleverPostcode,
Message1.SDescription Bericht1,
Message2.SDescription Bericht2,
Message3.SDescription Bericht3,
Debgroup1.SDescription RekeningGroep1,
Debgroup2.SDescription RekeningGroep2,
Salesman.SDescription Verkoper,
Doctype.WTypeID DocumentTypeID,
Doctype.SDescription DocumentType,
Postname.SDescription as Postnaam,
PostContact.SDescription as PostContact,
DelName.SDescription as AfleverNaam,
DelContact.SDescription as AfleverContact
FROM dochead
LEFT OUTER JOIN Account Account
ON (dochead.WAccountID = Account.WAccountID)
LEFT OUTER JOIN Groups Docgroup1
ON (dochead.WReportingGroup1ID = Docgroup1.WGroupID)
LEFT OUTER JOIN Groups Docgroup2
ON (dochead.WReportingGroup2ID = Docgroup2.WGroupID)
LEFT JOIN messages Postal1
ON (dochead.WPostal1ID = Postal1.WMessageID)
LEFT JOIN messages Postal2
ON (dochead.WPostal2ID = Postal2.WMessageID)
LEFT JOIN messages Postal3
ON (dochead.WPostal3ID = Postal3.WMessageID)
LEFT JOIN messages Postalcode
ON (dochead.WPostalCodeID = Postalcode.WMessageID)
LEFT JOIN messages Delpostal1
ON (dochead.WDelAddress1ID = Delpostal1.WMessageID)
LEFT JOIN messages Delpostal2
ON (dochead.WDelAddress2ID = Delpostal2.WMessageID)
LEFT JOIN messages Delpostal3
ON (dochead.WDelAddress3ID = Delpostal3.WMessageID)
LEFT JOIN messages Delpostalcode
ON (dochead.WDelCodeID = Delpostalcode.WMessageID)
LEFT JOIN messages Message1
ON (dochead.WMessage1ID = Message1.WMessageID)
LEFT JOIN messages Message2
ON (dochead.WMessage2ID = Message2.WMessageID)
LEFT JOIN messages Message3
ON (dochead.WMessage3ID = Message3.WMessageID)
LEFT OUTER JOIN Groups Salesman
ON (dochead.WSalesmanID = Salesman.WGroupID)
left join Messages Postname on (dochead.WPostalname = Postname.WMessageID)
left join Messages PostContact on (dochead.WPostalContact = PostContact.WMessageID)
left join Messages DelName on (dochead.WDelname = DelName.WMessageID)
left join Messages DelContact on (dochead.WDelContact = DelContact.WMessageID)
left join countries cr on cr.countries_id = WPosCountries_id
left join countries crd on crd.countries_id = WDelCountries_id
LEFT JOIN Types Doctype
ON (dochead.WTypeID = Doctype.WTypeID)
LEFT OUTER JOIN Groups Debgroup1
ON (Account.WReportingGroup1ID = Debgroup1.WGroupID)
LEFT OUTER JOIN Groups Debgroup2
ON (Account.WReportingGroup2ID = Debgroup2.WGroupID)
where WDocId=:TCVAR_DocID
