SmartFetch Sql Server View wrong keyfieldname

[aliemrei]

Hello,

when use the smartfetch options for a sql server's view. Unidac is getting key field name with tablename prefix.

View Sql:

Code: Select all

CREATE VIEW [dbo].[VWHKANBILESENIISTEM]
AS
SELECT     HKANBILESENIISTEM.HKANBILESENIISTEMID, HKANBILESENIISTEM.ISTEMTARIHI, HKANBILESENIISTEM.HDOSYAID, HKANBILESENIISTEM.DRID, TDRKIMLIK.DRADI, 
                      HDOSYA.HKIMLIKID, HDOSYA.HDTARIH, HDOSYA.BOLUMID AS HDBOLUMID, TBOLUM.BOLUMADI AS HDBOLUMADI, HDOSYA.DRID AS HDDRID, 
                      HDDRKIMLIK.DRADI AS HDDRADI, HDOSYA.HKCARIID, CARI.KISAADI AS KRMKISAADI, CARI.UNVANI AS KRMUNVANI, HDOSYA.HDYATIYOR, 
                      HDOSYA.MDLTEDAVITURU, HKIMLIK.HADI, HKIMLIK.HSOYADI, HKIMLIK.HADI AS HKARTNO, HKIMLIK.HADI + ' ' + HKIMLIK.HSOYADI AS HADISOYADI, 
                      HKIMLIK.HTCKIMLIKNO, HKIMLIK.HBABAADI, HKIMLIK.HANAADI, HKIMLIK.HDYERI, HKIMLIK.HDTARIHI, HKIMLIK.HYASI, HKIMLIK.HTEL, HKIMLIK.HCEP, 
                      HKIMLIK.HCINSIYETI, HKIMLIK.UYRUKKODU, HKIMLIK.KYTTARIH, TUYRUK.UYRUKADI, TMDLTEDAVITURU.MDLTEDAVITURUADI, 
                      HKANBILESENIISTEM.ISTEMNEDENIID, TKANBILESENIISTEMNEDENI.KANBILESENIISTEMNEDENI, HKANBILESENIISTEM.DIGERISTEMNEDENI, 
                      HKANBILESENIISTEM.ISTEMICDKOD, TICD.ICDADI AS ISTEMICDADI, HKANBILESENIISTEM.ENDIKASYONU, HKANBILESENIISTEM.PLANVERTARIHI, 
                      HKANBILESENIISTEM.PLANVERSURE, HKIMLIK.HKANGRUBUID AS KANGRUBUID, TKANGRUBU.KANGRUBU, HKANBILESENIISTEM.TOPUNITEADEDI, 
                      HKANBILESENIISTEM.LOKOSITFILTRASYON, HKANBILESENIISTEM.ISINLAMA, HKANBILESENIISTEM.YIKAMA, HKANBILESENIISTEM.DIGEREKISLEM, 
                      HKANBILESENIISTEM.ACIL, HKANBILESENIISTEM.ACILISTEMGEREKCESI, HKANBILESENIISTEM.ACILCROSSMATCHISLEMI, 
                      HKANBILESENIISTEM.HBSAG_ANTIHCV_ANTIHIV_VDRL_ISLEMI, HKANBILESENIISTEM.ANTIKORTANIMLANMASIVAR, HKANBILESENIISTEM.ANTIKORTIPI, 
                      HKANBILESENIISTEM.ANTIKORTARIHI, HKANBILESENIISTEM.TRANSPLANTASYONVAR, HKANBILESENIISTEM.TRANSPLANTASYONOYKU, 
                      HKANBILESENIISTEM.ONCEKITRANSFUZYONVAR, HKANBILESENIISTEM.ONCEKITRANSFUZYONOYKU, HKANBILESENIISTEM.TRANSFUZYONREAKSIYONUVAR, 
                      HKANBILESENIISTEM.TRANSFUZYONREAKSIYONUOYKU, HKANBILESENIISTEM.SON3AYGEBELIKVAR, HKANBILESENIISTEM.SON3AYGEBELIKOYKU, 
                      HKANBILESENIISTEM.FEOMATERNALUYUSMAZLIKVAR, HKANBILESENIISTEM.FEOMATERNALUYUSMAZLIKOYKU, HKANBILESENIISTEM.DIGEROYKU, 
                      HKANBILESENIISTEM.HASTAONAMI, HKANBILESENIISTEM.HASTAYAKINIADI, HKANBILESENIISTEM.HASTAYAKINIYAKINLIKDERECE, 
                      HKANBILESENIISTEM.HASTAYAKINIONAMINEDENIID, HKANBILESENIISTEM.ONAMTARIHI, HKANBILESENIISTEM.DURUM, HKANBILESENIISTEM.KYKULLANICI, 
                      HKANBILESENIISTEM.KYTARIH, HKANBILESENIISTEM.SGKULLANICI, HKANBILESENIISTEM.SGTARIHI, HKANBILESENIISTEM.SUBEID
FROM         HKANBILESENIISTEM LEFT OUTER JOIN
                      HDOSYA ON HDOSYA.HDOSYAID = HKANBILESENIISTEM.HDOSYAID LEFT OUTER JOIN
                      HKIMLIK ON HDOSYA.HKIMLIKID = HKIMLIK.HKIMLIKID LEFT OUTER JOIN
                      TKANBILESENIISTEMNEDENI ON HKANBILESENIISTEM.ISTEMNEDENIID = TKANBILESENIISTEMNEDENI.TKANBILESENIISTEMNEDENIID LEFT OUTER JOIN
                      TKANGRUBU ON HKIMLIK.HKANGRUBUID = TKANGRUBU.KANGRUBUID LEFT OUTER JOIN
                      TICD ON HKANBILESENIISTEM.ISTEMICDKOD = TICD.ICDKOD LEFT OUTER JOIN
                      TMDLTEDAVITURU ON HDOSYA.MDLTEDAVITURU = TMDLTEDAVITURU.MDLTEDAVITURU LEFT OUTER JOIN
                      TBOLUM ON HDOSYA.BOLUMID = TBOLUM.BOLUMID LEFT OUTER JOIN
                      CARI ON HDOSYA.HKCARIID = CARI.CARIID LEFT OUTER JOIN
                      TDRKIMLIK ON HKANBILESENIISTEM.DRID = TDRKIMLIK.DRID LEFT OUTER JOIN
                      TDRKIMLIK AS HDDRKIMLIK ON HDOSYA.DRID = HDDRKIMLIK.DRID LEFT OUTER JOIN
                      TUYRUK ON HKIMLIK.UYRUKKODU = TUYRUK.UYRUKKODU

UniDac Generated Sql :

Code: Select all

exec sp_executesql N'SELECT HKANBILESENIISTEM.HKANBILESENIISTEMID FROM dbo.VWHKANBILESENIISTEM
WHERE (ISTEMTARIHI >= @P1
      AND ISTEMTARIHI <@P2 and SUBEID=@P3
and (DURUM = @P4 OR @P5 IS NULL)) AND 1=0 ',N'@P1 datetime2(7),@P2 datetime2(7),@P3 int,@P4 nvarchar(4000),@P5 nvarchar(4000)','2017-11-30 00:00:00','2017-11-30 00:00:00',1,N'0',N'0'

[Stellar]

When SQL query is automatically generated, key fields values are selected, we use metadata about DB object name, which each field belongs to. SQL Server returns only the table name, which the field belongs to and does not return the view name. To solve the issue, when creating view, use the VIEW_METADATA option. For example:

CREATE VIEW [dbo].[VWHKANBILESENIISTEM]
WITH VIEW_METADATA
AS
SELECT HKANBILESENIISTEM.HKANBILESENIISTEMID, HKANBILESENIISTEM.ISTEMTARIHI,
...

Or you can make a query of retrieving key fields values by yourself, for example as follows:

TUniQuery.SmartFetch.SQLGetKeyValues := 'SELECT HKANBILESENIISTEMID FROM VWHKANBILESENIISTEM';