This entry is part 3 of 5 in the series Absolute beginner Guide To TSQL - Querying
Hi all, My fellow Sohail Shaikh wrote an excellent blog Post, I used it in my customization to avoid overlay methods in some of out of the box table.
That article made my day and worked perfectly fine. But here one thing that article missed. How we have to set form designer the custom workflow in list page . Something like If I copied from Sheiks sabs article.
There is error when I created on initialized of custom table in a extension class for list page . I got following error.
So it is not possible, I still not figure out. Later possible I will got they way to do so. Any how I achieved this in extension for interaction class. I copied the post event of initializeQuery method and copied in my class and following code will work for me. To enable or disabled workflow in list page
[PostHandlerFor(classStr(TableListPageInteraction), methodStr(ListPageInteraction, initializeQuery))]
public static void TableListPageInteraction_Post_initializeQuery(XppPrePostArgs args)
TableListPageInteraction TableListPageInteraction = args.getThis();
StatusTable RFQStatusTable = purchRFQCaseTableListPageInteraction.listPage().activeRecord(queryDataSourceStr(TableListPage, DDSRFQStatus)) as DDSRFQStatus;
FormRun formRun = TableListPageInteraction.listPage().formRun();
It works for me
Today I have to record very small tip. During development of on SSRS report for Dynamics Ax 2012 R3, End user wants a row at the report footer for analysis. he wants some kind of percentage calculation with ‘%’. A For this I have to use CStr ssrs expression function like
Ctr(((sum(DataSet!field.Value) / sum(DataSet!field.Value)))*100)+”%”.
Now it is string and shows number of decimal as it result from division. For example 5.4356333. But end user wants formatting like 5.43 .
It is string or text value and SSRS textbox formatting is not apply on it.
Following SSRS expression works for me.
My Updated expression is something like this
Left(CStr(IIF(Parameters!ProductionReportBM2DS_ItemName.Value = “ITEM-00000420”,((sum(Fields!Scrap.Value)/sum(Fields!TotalConsumption.Value))*100),((sum(Fields!CutLength.Value)/sum(Fields!TotalConsumption.Value))*100))),instr(CStr(IIF(Parameters!ProductionReportBM2DS_ItemName.Value = “ITEM-00000420″,((sum(Fields!Scrap.Value)/sum(Fields!TotalConsumption.Value))*100),((sum(Fields!CutLength.Value)/sum(Fields!TotalConsumption.Value))*100))),”.”)+2) +”%”
Suppose we have to insert data into VendGroup table through TSQL, this scenario usually appear when integration or data migration team inject data into AX using TSQL without using AIF.
There are two tables are important SQLDICTONARY and SYSTEMSEQUENCES.
SQLDictonary table contains the table id for example Vendtable has 490 id.
SYSTEMSEQUENCES table contains the sequence number in nextVal field.
I used following code snippet which helps me to insert into AX through TSQL.
declare @tableId as int = (select tableid from SQLDICTIONARY where name like 'VendGroup' and FIELDID=0); print @tableId; declare @recId as bigint =(select nextval from SYSTEMSEQUENCES where TABID=@tableId and name='seqno'); print @recId; select @recId set @recId =@recId + 1; print @recId; insert into DBO.VENDGROUP ([VENDGROUP],[NAME],[RECID],DATAAREAID,CREATEDBY,DEL_CREATEDTIME,CLEARINGPERIOD,PAYMTERMID,RECVERSION) values ('80','TSQLTest',@recId,'usmf','ALICIA',19372,'Net10','Net10',0 ); update SYSTEMSEQUENCES set NEXTVAL = @recId where TABID=@tableId and name='seqno';
Currently I was searching describe like function, which used in Oracle PLSQL to get the detail of table. I found equvilent function in TSQL “sp_help”.
you can used it as
I am dropping the rows but insertion start with next to last max identity . Something was missing in my knowledge. To reset Identity i have use one extra statement, other option is use trunc statement to drop rows. but if i have too drop rows with delete statement and reset the identity what should do, i have to use
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
This will reset identity back to 1. for example
DELETE from tblstudent; DBCC CHECKIDENT("tblstudent, RESEED, 0)
During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent id this way.
CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey,
The whole Query is as follow.
CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey,
FROM dbo.from Student
🙂 its works for me.
Delete Part of ETL Process
If any row is deleted at source table, but destination table rows exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all the Keys which have null at source table. And call delete at destination on filter keys.
My common table Expression for Delete will be as follow
with DeleteRows as ( SELECT sourceEmp.empid, DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp right join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId ) --select SourceEmpId from DeleteRows where empid is null delete from TSQLFundamentals2008DW.HR.DimEmployees where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId in ( select SourceEmpId from DeleteRows where empid is null)
For update part of ETL Process I have to write another Common table expression to get all records where are changed at source to run update query on destination table
My update part of basic ETL using TSQL as
with ChangedRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp inner join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId where sourceEmp.lastname <> DestEmployee.lastname or sourceEmp.firstname <> DestEmployee.firstname or sourceEmp.title <> DestEmployee.title or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy or sourceEmp.birthdate<>DestEmployee.birthdate or sourceEmp.hiredate <> DestEmployee.hiredate or sourceEmp.address <> DestEmployee.address or sourceEmp.city <> DestEmployee.city or sourceEmp.region <> DestEmployee.region or sourceEmp.postalcode<>DestEmployee.postalcode or sourceEmp.country <>DestEmployee.country or sourceEmp.phone<>DestEmployee.phone or sourceEmp.mgrid <> DestEmployee.mgrid ) update TSQLFundamentals2008DW.HR.DimEmployees set lastname = ChangedRows.lastname , firstname = ChangedRows.firstname , title = ChangedRows.title , titleofcourtesy =ChangedRows.titleofcourtesy , birthdate=ChangedRows.birthdate , hiredate = ChangedRows.hiredate , address = ChangedRows.address , city = ChangedRows.city , region = ChangedRows.region , postalcode=ChangedRows.postalcode , country =ChangedRows.country , phone=ChangedRows.phone , mgrid = ChangedRows.mgrid from ChangedRows where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid
My current assignment was to write ETL Process using TSQL. So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table.
So part of ETL Process using CTE as
with NewRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp left join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId where DestEmployee.SourceEmpId is null ) INSERT INTO [TSQLFundamentals2008DW].[HR].[DimEmployees] ([lastname] ,[firstname] ,[title] ,[titleofcourtesy] ,[birthdate] ,[hiredate] ,[address] ,[city] ,[region] ,[postalcode] ,[country] ,[phone] ,[mgrid] ,[SourceEmpId]) (select NewRows.lastname, NewRows.firstname, NewRows.title, NewRows.titleofcourtesy, NewRows.birthdate, NewRows.hiredate, NewRows.address, NewRows.city, NewRows.region, NewRows.postalcode, NewRows.country, NewRows.phone, NewRows.mgrid, NewRows.empid from NewRows)
DECLARE @PageNum AS INT; DECLARE @PageSize AS INT; SET @PageNum = 2; SET @PageSize = 10; WITH OrdersRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY DimProduct.ProductKey) AS RowNum, DimProduct.ProductKey, DimProduct.EnglishProductName as Product, DimProductSubcategory.ProductSubcategoryKey as SubCategoryKey, DimProductSubcategory.EnglishProductSubcategoryName as SubCategory, DimProductCategory.ProductCategoryKey as CategoryKey, DimProductCategory.EnglishProductCategoryName as Category FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey ) SELECT * FROM OrdersRN WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ORDER BY ProductKey;
You can use the list down all tables in specific database with this query
select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE'
Nolock is T-Sql hint, That used to ignore the locks on table during transactions. It allows retrieving data and did not wait to complete the transaition applied on table. It has some pros can cons
- NoLock provides significant improvements on large table, where upserts commands take time.
- You can retirve data during the same time while others are performing Insert and update on table.
- Possibility of data that was partially updated or inserted, because you retrieve data during update or insert on table.
- It often results in very obscure, hard to reproduce bugs and can cause data to get corrupted.
Main object is sys.tables. you can get all information about tables by query on sys.tables.
Select * from sys.tables;.
SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_Name
SCHEMA_NAME convert schema_id into schema name.
In my seven years of development experience, I read my tutorial and books on programming as well as. But when we talk about sql server from developer ‘s perspective. The following books are best. From novice developer to expert Tsql developer I recommend these books. All these books are from Itzik Ben-gan
From Microsoft learning site
Microsoft® SQL Server® 2008 T-SQL Fundamentals
Master the foundations of T-SQL with the right balance of conceptual and practical content. Get hands-on guidance—including exercises and code samples—that show you how to develop code to query and modify data. You’ll gain a solid understanding of the T-SQL language and good programming practices, and learn to write more efficient and powerful queries.
Discover how to:
- Apply T-SQL fundamentals, create tables, and define data integrity
- Understand logical query processing
- Query multiple tables using joins and subqueries
- Simplify code and improve maintainability with table expressions
- Explore pivoting techniques and how to handle grouping sets
- Write code that modifies data
- Isolate inconsistent data and address deadlock and blocking scenarios
Inside Microsoft® SQL Server® 2008: T-SQL Querying
About The BookMaster the mechanics behind advanced querying and tuning—for faster, more scalable code
Tackle the toughest set-based querying and query tuning problems—guided by an author team with in-depth, inside knowledge of T-SQL. Deepen your understanding of architecture and internals—and gain practical approaches and advanced techniques to optimize your code’s performance.
Discover how to:
- Move from procedural programming to the language of sets and logic
- Optimize query tuning with a top-down methodology
- Assess algorithmic complexity to predict performance
- Compare data-aggregation techniques, including new grouping sets
- Manage data modification—insert, delete, update, merge—for performance
- Write more efficient queries against partitioned tables
- Work with graphs, trees, hierarchies, and recursive queries
- Plus—Use pure-logic puzzles to sharpen your problem-solving skills
Inside Microsoft® SQL Server® 2008: T-SQL Programming
Get a detailed look at the internal architecture of T-SQL with this comprehensive programming reference. Database developers and administrators get best practices, expert techniques, and code samples to master the intricacies of this programming language—solving complex problems with real-world solutions.Discover how to:
- Work with T-SQL and CLR user-defined functions, stored procedures, and triggers.
- Handle transactions, concurrency, and error handling.
- Efficiently use temporary objects, including temporary tables, table variables, and table expressions.
- Evaluate when to use set-based programming techniques and when to use cursors.
- Work with dynamic SQL in an efficient and secure manner.
- Treat date- and time-related data in a robust manner.
- Develop CLR user-defined types and learn about temporal support in the relational model.
- Use XML and XQuery and implement a dynamic schema solution.
- Work with spatial data using the new geometry and geography types and spatial indexes.
- Track access and changes to data using extended events, SQL Server Audit, change tracking, and change data capture.
- Use Service Broker for controlled asynchronous processing in database applications.All the book’s code samples will be available for download from the companion Web site.