Converting outer apply with Correlated subquery in SQL Server to SQLite

Question

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

Answer 1

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name. Here's one approach that will work in SQLite:

select *
from t1
left join
 t2
on t2.id =
 (
 select id
 from t2
 where t1.id = t2.t1id
 order by
 name desc
 limit 1
 )

Example at SQL Fiddle.

• deavega66: it's very simple and useful.. and perfectly working.. thanks..

Answer 2

To limit the number of rows returned from a (sub)query, add a LIMIT clause. But this is not needed here because in SQLite, scalar subqueries have an implicit LIMIT 1.

Column values from a are not available inside InvoiceRevised because that is an independent table. You have to replace the outer join with correlated subqueries, one for each column:

SELECT id,
 CodeValue,
 TotalAmount,
 (SELECT Id
 FROM Invoices
 JOIN SCSInvoiceRels ON Invoices.Id = SCSInvoiceRels.InvoiceId
 WHERE SCSInvoiceRels.RecordStatus = 1
 AND Invoices.RecordStatus = 1
 AND SCSInvoiceRels.SCDetailId IN (SELECT SCDetailId
 FROM SCSInvoiceRels
 WHERE InvoiceId = a.id)
 ) AS RevisedInvoiceId,
 (SELECT /* there is no BillNumber in your InvoiceRevised ??? */
 FROM Invoices
 JOIN SCSInvoiceRels ON Invoices.Id = SCSInvoiceRels.InvoiceId
 WHERE SCSInvoiceRels.RecordStatus = 1
 AND Invoices.RecordStatus = 1
 AND SCSInvoiceRels.SCDetailId IN (SELECT SCDetailId
 FROM SCSInvoiceRels
 WHERE InvoiceId = a.id)
 ) AS RevisedInvoice,
 (SELECT pr.CodeValue
 FROM PatientRegs AS pr
 JOIN Patients AS pa ON pa.id = pr.PatientId
 JOIN SCDetails AS SC ON pr.id = SC.RegId
 JOIN SCSInvoiceRels AS SCb ON SC.id = SCb.SCDetailId
 WHERE SCb.InvoiceId = a.Id
 ) AS RegistrationNumber,
 (SELECT pa.Name
 FROM PatientRegs AS pr
 JOIN Patients AS pa ON pa.id = pr.PatientId
 JOIN SCDetails AS SC ON pr.id = SC.RegId
 JOIN SCSInvoiceRels AS SCb ON SC.id = SCb.SCDetailId
 WHERE SCb.InvoiceId = a.Id
 ) AS PatientName
FROM Invoices AS a
WHERE RecordStatus = 0
 AND UpdateOn IS NOT NULL