SQL to R transition on a query

Question

So I need to transition a query from SQL to R and it just so happens that there are limited functions that can be available in R i think.

SQL:

if object_id ('tempdb..#ProductCodingChanges') is not null drop table #ProductCodingChanges
SELECT p.ProdID as ID, p.ReverseSupplier as Supplier, p.ReverseProductCode as Code, p.Description, p.ReverseNDFProd AS [From NDFProd], p.ReverseNDFPack AS [From NDFPack], p.ReverseQuantityFactor AS [From QtyFct], l.description AS [From Description], p.EditNDFProd AS [To NDFProd], p.EditNDFPack AS [To NDFPack], p.EditQtyFct AS [To QtyFct], l1.description AS [To Description], cast(p.TotalEditUnits as varchar(max)) as [Total Edit Units], '$'+cast(p.EditValue as varchar(max)) as [Edit Value], '$'+cast(Cast(IIF(l.ATC LIKE 'W*',0,(p.TotalReversedUnits*l.price*-1))+IIF(l1.atc LIKE 'w*',0,(p.TotalEditUnits*l.price)) as money) as varchar(max)) AS APIdifference, '' AS Checked, p.ProdRecActive as Active
        INTO #ProductCodingChanges
    FROM (((#ProductCorrection AS p
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUPKey AS k ON (p.ReverseNDFPack = k.pack_cd) AND (p.ReverseNDFProd = k.prod_cd))
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUP AS l ON k.NDF = l.NDF)
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUPKey AS k1 ON (p.EditNDFPack = k1.pack_cd) AND (p.EditNDFProd = k1.prod_cd))
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUP AS l1 ON k1.NDF = l1.NDF
    ORDER BY Abs(IIF(l.ATC LIKE 'W*',0,(p.TotalReversedUnits*l.price*-1))+IIF(l1.atc LIKE 'w*',0,(p.TotalEditUnits*l.price))) DESC;

R:

ProductCodingChangesT <- sqldf("
        SELECT p.ProdID as ID, p.ReverseSupplier as Supplier, p.ReverseProductCode as Code, p.Description, p.ReverseNDFProd AS [From NDFProd], p.ReverseNDFPack AS [From NDFPack], p.ReverseQuantityFactor AS [From QtyFct], 
        l.Description AS [From Description], 
        p.EditNDFProd AS [To NDFProd], p.EditNDFPack AS [To NDFPack], p.EditQtyFct AS [To QtyFct], 
        l1.Description AS [To Description], max(cast(p.TotalEditUnits as varchar)) as [Total Edit Units], 
        '$'+max(cast(p.EditValue as varchar)) as [Edit Value], 
        '$'+max(cast(Cast(IIF(l.ATC LIKE 'W*',0,(p.TotalReversedUnits*l.Price*-1))+IIF(l1.ATC LIKE 'w*',0,(p.TotalEditUnits*l.Price)) as money) as varchar)) AS APIdifference, 
        '' AS Checked, 
        p.ProdRecActive as Active
        FROM (((ProductCorrectionT AS p
        LEFT JOIN NDFLKUPTKeyT AS k ON (p.ReverseNDFPack = k.pack_cd) AND (p.ReverseNDFProd = k.prod_cd))
        LEFT JOIN NDFLKUPT AS l ON k.NDF = l.NDF)
        LEFT JOIN NDFLKUPTKeyT AS k1 ON (p.EditNDFPack = k1.pack_cd) AND (p.EditNDFProd = k1.prod_cd))
        LEFT JOIN NDFLKUPT AS l1 ON k1.NDF = l1.NDF
        ORDER BY Abs(IIF(l.ATC LIKE 'W*',0,(p.TotalReversedUnits*l.Price*-1))+IIF(l1.ATC LIKE 'w*',0,(p.TotalEditUnits*l.Price))) DESC;")

An error appeared saying that there is no function for IIF. I was told to just manipulate it.

You could replace `IIF` with a `case when` I believe. This isn't really converting SQL to R though, it's converting SQL Server to SQLite (the default backend for *sqldf*). — thelatemail, May 25 '21 at 05:34

G. Grothendieck · Answer 1 · 2021-05-25T17:44:36.713

You must be using an old version of RSQLite. The version of RSQLite on CRAN has a later version of SQLite which does support iif.

library(sqldf)

sqldf("select Time, iif(Time < 4, 'a', 'b') Letter, demand from BOD")
##   Time Letter demand
## 1    1      a    8.3
## 2    2      a   10.3
## 3    3      a   19.0
## 4    4      b   16.0
## 5    5      b   15.6
## 6    7      b   19.8

packageVersion("RSQLite")
## [1] ‘2.2.7’

sqldf("select sqlite_version()")
##   sqlite_version()
## 1           3.35.5

score 0 · Answer 2 · answered May 25 '21 at 07:03

Thanks for the comment on using case when. I was now able to change IIF to Case When:

if object_id ('tempdb..#ProductCodingChanges') is not null drop table #ProductCodingChanges
SELECT p.ProdID as ID, p.ReverseSupplier as Supplier, p.ReverseProductCode as Code, p.Description, p.ReverseNDFProd AS [From NDFProd], p.ReverseNDFPack AS [From NDFPack], p.ReverseQuantityFactor AS [From QtyFct], l.description AS [From Description], p.EditNDFProd AS [To NDFProd], p.EditNDFPack AS [To NDFPack], p.EditQtyFct AS [To QtyFct], l1.description AS [To Description], 
cast(p.TotalEditUnits as varchar(max)) as [Total Edit Units], 
'$'+cast(p.EditValue as varchar(max)) as [Edit Value], 
'$'+cast(Cast(CASE l.ATC WHEN 'W*' THEN 0 ELSE (p.TotalReversedUnits*l.price*-1) END + CASE l1.atc WHEN 'w*' THEN 0 ELSE (p.TotalEditUnits*l.price) END as money) as varchar(max)) AS APIdifference, 
'' AS Checked, p.ProdRecActive as Active
        INTO #ProductCodingChangesCase
    FROM (((#ProductCorrection AS p
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUPKey AS k ON (p.ReverseNDFPack = k.pack_cd) AND (p.ReverseNDFProd = k.prod_cd))
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUP AS l ON k.NDF = l.NDF)
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUPKey AS k1 ON (p.EditNDFPack = k1.pack_cd) AND (p.EditNDFProd = k1.prod_cd))
        LEFT JOIN ndf_061.[NDF_061].dbo.NDFLKUP AS l1 ON k1.NDF = l1.NDF
    ORDER BY Abs(CASE l.ATC WHEN 'W*' THEN 0 ELSE (p.TotalReversedUnits*l.price*-1) END + CASE l1.atc WHEN 'w*' THEN 0 ELSE (p.TotalEditUnits*l.price) END) DESC;

Thank guys! Sorry for the irrelevant tags also.

SQL to R transition on a query

2 Answers2