7

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
10

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.

  • it's very simple and useful.. and perfectly working.. thanks.. – deavega66 Feb 12 '16 at 7:16
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.