MS SQL PIVOT ve UNPIVOT kullanımı, veritabanı uyumluluk sorunları-çözümleri

PIVOT ve UNPIVOT SQL Server 2005 sürümüne veya daha üzeri bir sürüme yükseltilen veritabanlarında kullanılır. Veritabanının uyumluluk düzeyi 90 veya üzeri bir değere ayarlanmalıdır.

 

Msg 325, Level 15, State 1, Line 5

Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

 

Yukardaki hatayı alıyorsanız ve

Microsoft SQL 2005 versiyonunda veritabanı uyumluluğu 90 değerinin altında ise;

Database değeri single moda alınır 

  ALTER DATABASE AdventureWorks   

  SET SINGLE_USER 

  GO

Sonra 

  EXEC sp_dbcmptlevel AdventureWorks, 90;

  GO 

En son ise tekrar multi user moda alınır.

  ALTER DATABASE AdventureWorks 

  SET MULTI_USER 

  GO

 

SQL Server versiyonu 2005 ve üzeri ise 90 değerine ayarlamak için bu sefer

    ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL=90

yapılır.

 

Örnek Pivot uygulaması (Ambar toplamları):

USE XXXX
go  
select stokkodu, stokadi, [ÇİNKO],[FASGEL],[FOSFAT],[MALKABUL],[MANGAN],[REDMK] from XXXX ) tablom
PIVOT (
sum(kalan)
 FOR AmbarNo IN ([ÇİNKO],[FASGEL],[FOSFAT],[MALKABUL],[MANGAN],[REDMK])
) PivotToplam
WHERE StokKodu='732740'
ORDER BY stokkodu

 

 

 

 Unpivot  örneği ise ambarlar satırda gösterilebilir.

farklı bir örnek verelim bunun için;

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

 

Role-Based Security with Forms in Microsoft Dynamics AX 2012 - Role bazlı güvenlik ayarları ax 2012

Başlarken,

1. Microsoft Dynamics AX 2012
2. (System administration à Common à Users à Users)

Önemli kavramlar:

1. Security roles

2. Process cycles

3. Duties

4. Privileges

5. Entry point

• Menu items
• Web menu items
• Service operations

6. Permissions

• Read
• Update
• Create
• Correct
• Delete
• No Access

 

Rol bazında güvenlik yarlrını, Müşteri gruplarına uygulayalım. (Accounts receivable à Setup à Customers à Customer groups).

 

Adımlar

1. First, to create a new privilege go to AOT à Security à Privileges
 
2. Right click on Privileges and select New Privilege

 
3. Name it CustomerGroupView
   Note: It is a best practice to name a Privilege as MenuItemName + View/Maintain depending upon the Access level
 
4. Set the Label of the privilege as Customer group view
   
   
   
 
5. Now expand the newly created privilege and create an entry point by right clicking on Entry Points and selecting New Entry Point

 
6. Name the entry point as CustGroup and set the AccessLevel to Read
 
7. Set the ObjectType to MenuItemDisplay and ObjectName to the menu item of the Customer group form, CustGroup
   
 
8. Save the privilege
 
9. Now create a duty and assign the above created privilege to the duty
 
10. Go to AOT à Security à Duties
 
11. Right click on Duties and select New Duty to create a new duty
    
    
    
 
12. Name the duty as CustomerGroupView and set the Label as Customer group view

 
13. Expand the above created duty and create a new privilege by right clicking on the Privileges node and select New Privilege

 
14. Select the CustomerGroupView privilege in the Name field and save the duty
    Note: You can also drag and drop the privilege on the Privileges node

 
15. Next create a new role by going to AOT à Security à Roles
 
16. Right click on Roles node and select New Role

 
17. Name the role as SecurityDemo and Label it as Security demo

 
18. Expand the above created role and right click on Duties node and select New Duty

 
19. Select the CustomerGroupView duty in the Name field and save the role
Note: You can also drag and drop the duty on the Duties node

 
20. Now assign the above role to a user Dynamics AX. Go to System administration à Setup à Security à Assign users to roles

 
21. On the Assign users to roles form, select the above created role in the left tree and click on Manually assign / exclude users

 
22. In the opened dialog, select the user to which you want to assign the role and press Assign to role

 
23. A green check mark will appear in case of successful assignment. Close the form
 
24. Now login with the selected role
 
25. Only the functions assigned to the user will be visible. In this case only the Customer groups form is visible since the Security Role contains only one duty. Also note that only those Menus will be visible that contains the menu item assigned in the Privilege

kaynak: