You’re offline. This is a read only version of the page.
Announcements
5
Hello,
I created a report in SQL server with data from navision.
Now i see that some data is missing. So my query is not 100 correct.
I want to solve that problem ..
In the table job ledger entry is the missing record. There i have for example 4 different records. I want to join that with the purch inv line tabel. Now i join on job no and document no but ofcourse i don't get 4 rows back but a lot more because on the table purch inv line he finds multiple matches on those 2 values.
So now is my question. How do i create a link between these 2 tables so that i will get the 4 rows back with in every row all the data from job ledger entry and purch inv line.
Thanks a lot !
Just add more field link:
2 of the 4 records have the same 'No_' in both tables. Both D003: find a different value filed for same No. records and add that field link
select *
from [COMPANY$Job Ledger Entry Cmfrt]
inner join [COMPANY$Purch_ Inv_ Line]
on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]
and [COMPANY$Job Ledger Entry Cmfrt].[No_] = [COMPANY$Purch_ Inv_ Line].[No_] //try to add this line?
and [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = [COMPANY$Purch_ Inv_ Line].[Job No_] //try to add this line?
and [COMPANY$Job Ledger Entry Cmfrt].[Job Task No.] = [COMPANY$Purch_ Inv_ Line].[Job Task No.] //example filed link?
where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'
select *
from [COMPANY$Job Ledger Entry Cmfrt]
inner join [COMPANY$Purch_ Inv_ Line]
on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]
and [COMPANY$Job Ledger Entry Cmfrt].[No_] = [COMPANY$Purch_ Inv_ Line].[No_] //try to add this line?
and [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = [COMPANY$Purch_ Inv_ Line].[Job No_] //try to add this line?
where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'
Hi Jens,
Thanks for providing a copy of the query. It looks like the JOIN clauses need some tweaking to get the results you are looking for based on the correct relationships between these tables. If you have a Page or existing Report in NAV which already includes these same tables you could use SQL Profiler to trace how the NAV queries are generated when you access the relevant Page or report via the NAV client. You could then copy the way the JOINs are working on the queries from the trace file.
If that does not help you could open an Advisory ticket with Microsoft via your Solution Provider or Dynamics NAV Partner.
Hope that helps.
Yes no problem,
declare @JobNo_ as nvarchar(max)
set @JobNo_ = 'P0002333'
SELECT
[COMPANY$Purchase Line].[Document No_] AS [COMPANY$Purchase Line Document No_] --- Inkoop order lijn, document nummer
,[COMPANY$Purchase Line].Quantity AS [COMPANY$Purchase Line Quantity] --- Inkoop order lijn, aantal
,[COMPANY$Purchase Line].[Unit Cost (LCY)] --- Inkoop order lijn , prijs per stuk
,[COMPANY$Purchase Line].[Job No_] --- Inkoop order lijn, projectnummer
,[COMPANY$Purchase Line].[Quantity Invoiced] --- Inkoop order lijn, gefactureerd aantal
,[COMPANY$Purchase Line].[Blanket Order No_] --- Inkoop order lijn, Externe referentie ???
,[COMPANY$Purchase Line].[Budget Account Code] --- Inkoop order lijn, account code NVT
,[COMPANY$Purchase Line].[Subcontract_ Contract No_] --- Inkoop order lijn, subcontract nr NVT
,[COMPANY$Purch_ Rcpt_ Line].[Document No_] AS [COMPANY$Purch_ Rcpt_ Line Document No_] --- Inkoop ontvanst lijn, document nr
,[COMPANY$Purch_ Rcpt_ Line].[Line No_] --- Inkoop ontvangst lijn, lijn nr
,[COMPANY$Purchase Line].[Buy-from Vendor No_] --- Inkoop order lijn, verkoper nr
,[COMPANY$Purchase Line].[Quantity Received] --- Inkoop order lijn, aantal ontvangen
,[COMPANY$Purch_ Rcpt_ Line].Quantity AS [COMPANY$Purch_ Rcpt_ Line Quantity] --- inkoop ontvangst lijn, aantal
,[COMPANY$Purch_ Inv_ Line].[Document No_] AS [COMPANY$Purch_ Inv_ Line Document No_] --- inkoop factuur lijn, documentnr
,[COMPANY$Purch_ Inv_ Line].Amount --- inkoop factuur lijn, bedrag
,[COMPANY$Purch_ Inv_ Header].[Posting Date] --- inkoop factuur header, datum
,[COMPANY$Purch_ Inv_ Header].[Vendor Invoice No_] --- inkoop factuur header, leveranciers nummer
,[COMPANY$Vendor].Name --- Leveranciers, naam leverancier
FROM
[COMPANY$Purchase Line] --- inkoop order lijn
LEFT OUTER JOIN [COMPANY$Purch_ Rcpt_ Line] --- left join met inkoop ontvangst lijn
--- documentnr van inkoop order lijn moet matchen met het order nr van de inkoop ontvangst lijn AND lijnnr van inkoop order lijn moet matchen met inkoop ontvangst lijn order lijn nr
ON [COMPANY$Purchase Line].[Document No_] = [COMPANY$Purch_ Rcpt_ Line].[Order No_] AND [COMPANY$Purchase Line].[Line No_] = [COMPANY$Purch_ Rcpt_ Line].[Order Line No_]
LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Line] --- left join met inkoop factuur lijn
--- documentnr van inkoop ontvangst lijn matchen met inkoop factuur lijn nr AND lijn nr van inkoop ontvangst lijn matchen met inkoop ontvangst lijn nr
ON [COMPANY$Purch_ Rcpt_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Receipt No_] AND [COMPANY$Purch_ Rcpt_ Line].[Line No_] = [COMPANY$Purch_ Inv_ Line].[Receipt Line No_]
LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Header] --- left join met inkoop factuur header
--- document nr van inkoop factuur lijn matchen met nr van inkoop factuur header
ON [COMPANY$Purch_ Inv_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Header].No_
INNER JOIN [COMPANY$Vendor] --- inner join met alle leveranciers
--- inkoop order lijn leveranciers nr matchen met leveranciers nr
ON [COMPANY$Purchase Line].[Buy-from Vendor No_] = [COMPANY$Vendor].No_
WHERE
[COMPANY$Purchase Line].[Document No_] LIKE N'BB%'
AND [COMPANY$Purchase Line].[Job No_] LIKE @JobNo_
above is the query now without the job ledger entry table. But on the job ledger entry table there is the one record that should also be included in my query.
Now i started my new query:
select *
from [COMPANY$Job Ledger Entry Cmfrt]
inner join [COMPANY$Purch_ Inv_ Line]
on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]
where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'
I started my query to do thing the other way around. First get job ledger entry , then join it with purch inv line then purch inv header , purch rcpt line and at last the purchase line table.
But like you can see the other way around isn't working either. What is normal because on the 2 tables i match on things dat excist in every record. So there is no unique link between them
#1
André Arnaud de Cal...
291,359
Super User 2024 Season 2
#2
Martin Dráb
230,370
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
