I need to convert a query in SQL Server to SQlite and I have replaced the outer apply to left outer join, but how to limit the subquery to return only 1 in this situation? And how to pass the reference value from outer table into sub query?
I have highlight the things that i am not able to convert it into Sqlite, can anybody please give some advise ?
SQL Server:
SELECT a.id,
a.CodeValue ,
a.TotalAmount ,
InvoiceRevised.RevisedInvoiceId,
InvoiceRevised.BillNumber AS RevisedInvoice,
patientlookup.RegistrationNumber AS RegistrationNumber,
patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
OUTER APPLY (
SELECT TOP 1 dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
dbo.Invoices.RoundAmount
FROM Invoices
INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND dbo.SCSInvoiceRels.SCDetailId IN (
SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
OUTER APPLY (
SELECT TOP 1 pr.CodeValue AS RegistrationNumber,pa.Name AS PatientName FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
WHERE SCb.InvoiceId = a.Id
) patientlookup
WHERE RecordStatus = 0 AND UpdateOn IS NOT NULL
SQLite:
SELECT a.id,
a.CodeValue ,
a.TotalAmount ,
InvoiceRevised.RevisedInvoiceId,
InvoiceRevised.BillNumber AS RevisedInvoice,
patientlookup.RegistrationNumber AS RegistrationNumber,
patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
Left outer join (
SELECT dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
dbo.Invoices.RoundAmount
FROM Invoices
INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND dbo.SCSInvoiceRels.SCDetailId IN (
SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
Left outer join(
SELECT pr.CodeValue AS RegistrationNumber,
pa.Name AS PatientName,
SCb.InvoiceId as InvoiceId
FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
) patientlookup on patientlookup.InvoiceId = a.Id
WHERE RecordStatus = 0 AND UpdateOn IS NOT NULL
Best Answer
SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example:
Now we'd like to look up at most one row from
t2
, sorted byname
. Here's one approach that will work in SQLite:Example at SQL Fiddle.