Category Archives: T-SQL

Changing a Smartlist Favorite’s visibility thru’ SQL

Posted on by

Warning: Method shared in this post is NOT supported or advised by Microsoft team. This article explains a SQL method and therefore the intended audience is a GP and SQL Consultant / Developer / Administrator.

Couple of Smartlist Favorites created by an end user, but created it with a wrong visibility: Visible by User ID. What this user should have selected is: Visible by System. Since these favorites are suppose to be accessed by all Users across all Companies.

This end user is currently out and even as SA I could not access the Smartlist Favorites. Easiest and direct approach is to reset this user’s password, modify the smartlist favorite and notify him of the new password. Once this user is back, he/she would change the password.

But, is there any other simplest method for someone like me? A GP & SQL Administrator? Of course, I do.

I can modify a Smartlist Favorite using a simple SQL UPDATE statement, targeting the Smartlist Favorite tables ASIEXP81 and ASIEXP86. Display Name for these tables are Smartlist Favorite Master and Smartlist Favorite Columns Master respectively. Both tables are in DYNAMICS database.

Below are SQL statements that I used to change the visibility:

UPDATE DYNAMICS..ASIEXP81 SET ASI_Favorite_Save_Level = 1, USERID = ”, CMPANYID = 0 WHERE ASI_Favorite_Name = [Favorite_Name_Given_By_That_End_User]

UPDATE DYNAMICS..ASIEXP86 SET ASI_Favorite_Save_Level = 1, USERID = ”, CMPANYID = 0 WHERE ASI_Favorite_Name = [Favorite_Name_Given_By_That_End_User]

In above statements, field ASI_Favorite_Save_Level is the Visibility Type that we set up when we create a Smartlist. Value 1 denotes the first option in that Drop Down, which is SYSTEM. Since I have to set visibility to all users across all companies, all I have to do is to set USERID as an Empty String and Company ID as ZERO.

This will change a particular Smartlist Favorite’s visibility as required.

Simple, isn’t it?

VAIDY

Last Sale By a Customer & Item – Victoria’s SQL View

Posted on by

Victoria‘s latest SQL view retrieves the last sale by customer and an item. With little tweaks here and there, this SQL view will serve us immensely to show various information pertaining to Customer and/or Item sales.

Thanks Victoria for this view. This has come especially when I am working on some dashboards and reporting. It’s surely going to help me deriving some important reports.

VAIDY

How to simulate TOP 100 PERCENT in SQL 2005/2008 – Steve Endow

Posted on by

This one’s very good trick to Force SQL Server 2005 / 2008 to sort of use ORDER BY in Views.

Never knew this till Steve’s article. Good one for all SQL Developers (also for Part Time ones).

VAIDY

SQL Query Sorting Order – How to Define?

Posted on by

I learned it only today, that too after 2 hours of thinking. This probably is a readily available answer on Internet or Forums or Manuals, but it never struck my mind to check all those sources.

Issue: I wrote a SQL query and wanted to process the records in a specific sort order. Let’s say for instance, my query is:

SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY 
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE, DOCDATE DESC

Please note that I have used DESC for my sorting order. Basically I wanted to sort the records based on:

1. Item Number (to be in Ascending Order)
2. Document Type (to be in Descending Order)
3. Document Date (to be in Descending Order)

The above query for my requirement, always returned records in a wrong sort order. The Sort Order was like this:

1. Item Number (Ascending Order) – which is Correct
2. Document Type (Ascending Order) – which is Wrong
3. Document Date (Descending Order) – which is Correct

The reason I learned for this, is a trivial SQL concept, which I never knew till today.

Let’s revisit my query, which is now corrected one:

SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE DESC, DOCDATE DESC

The above, corrected, query shows one more DESC added to column DOCTYPE. That means, if we want to sort records in Descending Order based on more than one column, we must specify DESC on each relevant column. Otherwise, it will take only the last column.
 
Till now, I was under the impression that specifying DESC in the last (once) is more than enough.
 
I thought I would share this with people, in case anyone else overlooked this simple concept, like me.
 
VAIDY

Data Types Matter – Steve Jones (SQL Musings)

Posted on by

Another article which stresses on using relevant and optimal Datatypes instead of regular and unnecessary ones. There is an illustration which explains realtime.

The article, Data Types Matter, is written by Steve Jones on his blog SQL Musings.

Must read for all Developers out there.

VAIDY