SQL Query Sorting Order - How to Define?

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

Dynamics GP 2010 - International English Version Now Available

Go thru' this article and get registered for Microsoft Dynamics GP 2010 - International English Launch.

More important details on Dynamics Care article.

Let's gear up for an evolved GP 2010.

VAIDY

GP Login Form - Caps Lock Reminder - UPDATE

This post is an update to my previous article, CapsLock Reminder for GP Login.

In my previous article, I had shared a package which would remind you of Caps Lock being switched on.

This warning will be shown as soon as you either launch GP or try to change the User after your first login.

I thought I would tweak it so that it would show this warning when you enter Password Field. And that makes more sense than showing it right after opening the GP Login form.

So please find that updated package here: CapsLockReminder-Update.package.


VAIDY

CBM - User [username] is busy with batch [batchname].

As promised in my previous article, here we go.

This is another frequent error message that Users get when they enter CBM Manual Payments in a CBM Batch:

CAUSE: There will be an Activity record inserted when you open a Batch for Payment Entry, in the table CB300006 (Display Name: Batch Entry Records Locked). This does not get cleared at times.

SOLUTION:

I. Follow the Resolution Steps I & II from this article: http://www.vaidy-dyngp.com/2010/04/cbm-checkbook-checkbook-id-is-already.html.

II. Run the following query once above steps are completed.

DELETE CB300006 WHERE USERID = '[username]' AND BACHNUMB = '[batch number]'


We are good to go now.

VAIDY

CBM - Checkbook [checkbook id] is already in user by user [username].

I often come across this request from GP Users:

This happens when the User try to reconcile from CBM Reconcile. And once he/she selects the Checkbook which needs to be reconciled.

After some SQL Profiling & Dex Script Log, I found the following:

Cause:

The table CB100006 contains the User Checkbook Activity records, as and when a User reconciles a Checkbook. This table for some reason is not cleared properly. Sometimes:

1. If you open the form, enter the Checkbook ID and just close it without any activity, this record is not cleared.

2. If you open the form, enter the Checkbook ID and just close GP directly without any activity, this record is stuck.

The above scenarios are faced by me and have not heard of this from any other consultants, so the above need not be recreated consistently.

Resolution:

I. Consultants must open SQL Management Studio, log on to the Data Server and connect to relevant company DB.

II. Run the following queries against DYNAMICS and the respective companies:

/*
   This below queries will delete all stranded and unwanted SQL Sessions.
*/
DELETE TEMPDB..DEX_LOCK WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)

DELETE TEMPDB..DEX_SESSION WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)

/*
   The below queries will ensure that the respective user, against whom the error message was thrown, would be cleared from GP Application Session(s).
*/
SELECT * FROM DYNAMICS..ACTIVITY WHERE USERID = '[username shown in the CBM error message]'

--Ask that user to log off, if this user has logged on for the day.

III. Run the below query to clear the CBM Checkbook Lock:

DELETE CB100006 WHERE USERID = '[username shown in the CBM error message]'

That's it. We are good to go with our Checkbook Reconciliation in CBM.

NOTE: I will be posing another article on CBM Payments Batch Lock Error (which is identical to this error message).

VAIDY

IM Error - Argument 'Index' is not a valid value.

I have been working on a Go-Live and been really hectic for the past 2 weeks.

Data Uploads, as usual, and suddenly this error was taking my entire energy. I thought I would blog this one issue which has got no mentioning in any of the forum(s) or KB as well.

I mapped and started integrating Sales Order transactions thru' Integration Manager (IM). As soon as I started the integration, it threw the following error message:

Argument 'Index' is not a valid value.

No clue, absolutely. I searched this error message and got very VB.NET and C#.NET related posts. No idea whatsoever.

Verified my mappings, twice thrice, and everything looked OK. Or was that really OK? Nope.

I decided to do a Post-Mortem of my Integration Mapping again and found one silly error:

I forgot to select a Constant value for my "SOP Type" field. I was fiddling with the options, "Use Source Field" & "Use Constant". And in due course somehow it just got cleaned up and no Constant value selected.

I selected the option "Use Source Field" and ran the integration. The error message vanished.

Bottomline:

1. When you face this error for any integration, just make sure that you have selected proper Source Field or Constant Value for all Required and Primary Key field(s).
2. Read this article of mine: Little Bit of Philosophy. I myself falling into the same trap of being ignorant of very trivial things. Should be very careful henceforth.

VAIDY