Unifying SQL between SQL Server and Access

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Unifying SQL between SQL Server and Access

Post by ertank » Thu 13 Feb 2020 18:37

Hello,

I am trying to unify below two SQL statements into a single one with the help of TUniConnection Macros.

Access:

Code: Select all

SELECT
  TARIH AS TARIH2,
  Sum(IIf([TUR]='Satış', BORC, 0)) as GUNLUK_SATIS,
  Sum(IIf([TUR]='Satış', BORC, 0)) - Sum(IIf([TUR]="Satış", ALACAK)) as VERESIYE,
  Sum(IIf([TUR]='Satış', ALACAK, 0)) as SATIS_TAHSILAT,
  Sum(IIf([TUR]='Tahsilat', BORC, 0)) as EK_TAHSILAT,
  Sum(IIf([TUR]='Gelir', BORC, 0)) as EK_GELIR,
  Sum(IIf([TUR]='Satış', ALACAK, 0)) + Sum(IIf([TUR]='Tahsilat', ALACAK, 0)) + Sum(IIf([TUR]='Gelir', ALACAK, 0)) as GELIRLER_TOPLAM,
  Sum(IIf([TUR]='Gider', BORC, 0)) as GIDERLER_TOPLAM,
  Sum(IIf([TUR]='Alış', ALACAK, 0)) as URUN_GIRISI,
  Sum((IIf([TUR]='Satış', ALACAK, 0))) + Sum(IIf([TUR]='Tahsilat', ALACAK, 0)) + Sum(IIf([TUR]='Gelir', ALACAK, 0)) - (Sum(IIf([TUR]='Alış', ALACAK, 0)) + Sum(IIf([TUR]='Gider', BORC, 0))) as KASA_TOPLAM
FROM 
  SATISLAR
GROUP BY
  TARIH
SQL Server:

Code: Select all

SELECT
  TARIH AS TARIH2,
  SUM(CASE WHEN TUR=('Satış') then BORC ELSE 0 END) GUNLUK_SATIS,
  SUM(CASE WHEN TUR=('Satış') then BORC ELSE 0 END) - SUM(CASE WHEN TUR=('Satış') then ALACAK ELSE 0 END) VERESIYE,
  SUM(CASE WHEN TUR=('Satış') then ALACAK ELSE 0 END) SATIS_TAHSILAT,
  SUM(CASE WHEN TUR=('Tahsilat') then ALACAK ELSE 0 END) EK_TAHSILAT,
  SUM(CASE WHEN TUR=('Gelir') then ALACAK ELSE 0 END) EK_GELIR,
  SUM(CASE WHEN TUR=('Satış') then ALACAK ELSE 0 END) + SUM(CASE WHEN TUR=('Tahsilat') then ALACAK ELSE 0 END) + 
  SUM(CASE WHEN TUR=('Gelir') then ALACAK ELSE 0 END) as GELIRLER_TOPLAM,
  SUM(CASE WHEN TUR=('Gider') then BORC ELSE 0 END) GIDERLER_TOPLAM,
  SUM(CASE WHEN TUR=('Alış') then ALACAK ELSE 0 END) URUN_GIRISI,
  SUM(CASE WHEN TUR=('Satış') then ALACAK ELSE 0 END) + SUM(CASE WHEN TUR=('Tahsilat') then ALACAK ELSE 0 END) + SUM(CASE WHEN TUR=('Gelir') then ALACAK ELSE 0 END)) - (SUM(CASE WHEN TUR=('Gider') then BORC ELSE 0 END) + SUM(CASE WHEN TUR=('Alış') then ALACAK ELSE 0 END) KASA_TOPLAM
FROM
  SATISLAR
GROUP BY
  TARIH
I simply failed to see how I can use Provider specific macros here in order to get rid of a lot of different queries.

Thanks & regards,
Ertan

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Unifying SQL between SQL Server and Access

Post by Stellar » Mon 16 Mar 2020 15:45

Unfortunately, UniDAC doesn't have unified statements for SQL clauses (CASE, IIf).
If you want us to implement the feature, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... y_id=18939. If the suggestion gets a lot of votes, we will consider the possibility to implement it.

Post Reply