CODEWITHSUNDEEP
sqlrsqldf
Chardiezard Gaming
Chardiezard Gaming

Reputation: 33

sqldf only returning one row, same query used in SQL

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!

Upvotes: 1

Views: 192

Answers (1)

Parfait
Parfait

Reputation: 107567

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]

Upvotes: 3

Related Questions