sqldf only returning one row, same query used in SQL

Question

For some reason I'm only returning one row when it comes to R while at SQL Server, I'm returning the correct number of rows. SQLDF:

CustomerCodingChangesT <- sqldf("
        SELECT c.CustID as ID 
             , c.ReverseSupplier as Supplier
             , c.ReverseCustomerCode as Code
             , c.Name
             , c.Address
             , c.[From PostCode]
             , c.[From Outlet]
             , c.[From OutletName] 
             , o.FullAddress AS [From Address]
             , c.[To PostCode]
             , c.[To Outlet]
             , c.[To OutletName]
             , o1.FullAddress AS [To Address]
             , MAX(CAST(c.TotalUnits AS VARCHAR)) AS [Total Units]
             , '$'+MAX(CAST(c.TotalValue AS VARCHAR)) AS [Total Value]
             , '' AS Checked
             , c.CustRecActive as Active

        FROM CustomerCorrectionSummaryT AS c
        LEFT JOIN OutletMasterT AS o 
             ON  c.[From PostCode] = o.Postcode 
             AND c.[From Outlet] = o.Outlet 
        LEFT JOIN OutletMasterT AS o1 
             ON  c.[To PostCode] = o1.Postcode 
             AND c.[To Outlet] = o1.Outlet
        ORDER BY c.totalvalue DESC;")

SQL:

if object_id ('tempdb..#CustomerCodingChanges') is not null drop table #CustomerCodingChanges

SELECT c.CustID as ID
     , c.ReverseSupplier as Supplier
     , c.ReverseCustomerCode as Code
     , c.Name
     , c.Address
     , c.[From Postcode]
     , c.[From Outlet]
     , c.[From OutletName]
     , o.FullAddress AS [From Address]
     , c.[To Postcode]
     , c.[To Outlet]
     , c.[To OutletName]
     , o1.FullAddress AS [To Address]
     , CAST(c.TotalUnits AS VARCHAR(MAX)) AS [Total Units]
     , '$'+CAST(c.TotalValue AS VARCHAR(MAX)) AS [Total Value]
     , '' AS Checked 
     , c.CustRecActive as Active

INTO #CustomerCodingChanges
FROM #CustomerCorrectionSummary AS c
LEFT JOIN ndf_061.IRGMaster.dbo.OutletMaster AS o 
     ON  c.[From Postcode] = o.postcode 
     AND c.[From Outlet] = o.outlet
LEFT JOIN ndf_061.IRGMaster.dbo.OutletMaster AS o1 
     ON  c.[To Postcode] = o1.postcode 
     AND c.[To Outlet] = o1.outlet
ORDER BY c.totalvalue DESC;

Both dataframes for CustomerCorrectionSummaryT and OutletMasterT have the same number of results in R and in SQL Server so I don't know why it won't also show the same number of results in R versus in SQL Server. In SQL Server query returns 22 rows while in R I'm only getting one row which both are correct. R's sqldf just doesn't show all of it. I'm thinking it has something to do with my left join function but I don't really know. Let me know if you need more information!

Parfait · Accepted Answer · 2021-05-31 02:20:32Z

3

If you carefully review, those two queries are not the same. Notably, you call an aggregate in first query, MAX, that you do not call in second query. Hence, first query is an aggregate query which collapses unit-level rows by various groupings returning aggregates (SUM, COUNT, AVG , MIN, MAX, etc.) on specified columns.

Additionally, because you do not include a GROUP BY clause, in standard ANSI SQL the first query should have raised an error. Unfortunately for newcomers of SQL some dialects like SQLite and MySQL allow such syntax. And R's sqldf runs SQLite by default. In fact, had you run that first query in SQL Server, it would err out since you run an incorrect aggregate query by not included non-aggregated columns in GROUP BY clause though they are specified in SELECT clause.

Looking closely, you appear to be trying to translate the below expression in SQL Server

CAST(c.TotalUnits AS VARCHAR(MAX)) AS [Total Units]

which is not the same as this attempted line in first query:

MAX(CAST(c.TotalUnits AS VARCHAR)) AS [Total Units]

SQLite actually does not maintain a maximum length in VARCHAR types. So simply keep the original line without aggregation or size limit:

CAST(c.TotalUnits AS VARCHAR) AS [Total Units]

answered May 31, 2021 at 2:20

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chardiezard Gaming Over a year ago

Oh! Thank you for this! I was losing hope that no one would answer this when it was downvoted to -1. I'm just new in stackoverflow and I really tried my best to look for similar issues that caused my error but I guess I didnt look at the MAX functionality very well. Thank you again!

Parfait Over a year ago

Great to hear and glad to help! Happy coding!

Collectives™ on Stack Overflow

sqldf only returning one row, same query used in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related