Jiwa

Hi,
I have added some code to the end of the standard usp_JIWA_BatchPrint_DefaultFilter to extract specific records if DB_category3 condition is meet. So so good but I now want to sort the #CandidatesTempTable records by DB_Category3.Description and DB_Category5.Description.
These 2 fields represent a Delivery run no, And Delivery Order within the Run.
I am having problems with the joins to #CandidatesTempTable - please can you help me.
Added code below :
SET @SP_AppliesTo_IN_PhysicalID = LTRIM(RTRIM(@SP_AppliesTo_IN_PhysicalID))
SET @SP_AppliesTo_IN_LogicalID = LTRIM(RTRIM(@SP_AppliesTo_IN_LogicalID))

DELETE FROM #CandidatesTempTable
WHERE InvoiceNo NOT IN
(
--Sales Orders Status=0,InvTot>0,Cat2(Dlv run=1)
SELECT
InvoiceNo
FROM SO_Main
INNER JOIN
DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID INNER JOIN
DB_Category3 ON DB_Main.Category3ID = DB_Category3.Category3ID
WHERE (SO_Main.Status = 0) AND (SO_Main.InvoiceTotal > 0) AND (DB_Category3.Description = '1')
)

Select * FROM #CandidatesTempTable INNER JOIN
SO_Main ON InvoiceNo = SO_Main.InvoiceNo INNER JOIN
DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID INNER JOIN
DB_Category3 ON DB_Main.Category3ID = DB_Category3.Category3ID INNER JOIN
DB_Category5 ON DB_Main.Category5ID = DB_Category5.Category5ID

ORDER BY DB_Category3.Description, DB_Category5.Description

SET NOCOUNT OFF

GO

2can2 wrote:now want to sort the #CandidatesTempTable records by DB_Category3.Description and DB_Category5.Description

The following

Code: Select all

ORDER BY DB_Category3.Description, DB_Category5.Description

looks correct... what is the issue - is it not ordering by those fields? Are you getting an error?

Also - please provide the Jiwa version and SQL version this applies to!

Mike

Sorry missed those things.
V70115.
The problem is with my link - InvoiceNo. How else can I link the other files need for the sort?

Error it gives is - 'Ambiguous column name - InvoiceNo' - see attached.

Thanks Mike

Hi,
I have managed to sort this syntax out (with some help -code below for info) by using a 2nd table BUT I have one major problem!!


I think you resort the Records into Invoice number order in the Batch Print app !!

How can I override this!! It is urgent so will also email support on this. Thanks.

Cheers

Select #CandidatesTempTable.* INTO #CandidatesTempTable_Sorted
FROM #CandidatesTempTable INNER JOIN
SO_Main ON #CandidatesTempTable.InvoiceNo = SO_Main.InvoiceNo INNER JOIN
DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID INNER JOIN
DB_Category3 ON DB_Main.Category3ID = DB_Category3.Category3ID INNER JOIN
DB_Category5 ON DB_Main.Category5ID = DB_Category5.Category5ID
ORDER BY DB_Category3.Description, DB_Category5.Description
DELETE #CandidatesTempTable

INSERT INTO #CandidatesTempTable
SELECT * FROM #CandidatesTempTable_Sorted

2can2 wrote:I think you resort the Records into Invoice number order in the Batch Print app !!

I've checked and we don't resort - we call the stored procedure and accept them in the order returned

I think this is your problem:

2can2 wrote:INSERT INTO #CandidatesTempTable
SELECT * FROM #CandidatesTempTable_Sorted

You probably need to ORDER BY on the final result set, otherwise the order returned is unknown and probably not the order they were inserted into - best to be explicit - so something like:

Code: Select all

INSERT INTO #CandidatesTempTable
SELECT *
FROM #CandidatesTempTable_Sorted
ORDER BY #CandidatesTempTable_Sorted.DB_Category3_Description, #CandidatesTempTable_Sorted.DB_Category5_Description


Note - you will need to add the DB_Category3_Description and DB_Category5_Description columns to your #CandidatesTempTable_Sorted table

Thanks Mike. Got it and it works.
However I did discover that I was trying to sort in the wrong place. I put the sort code at the end of usp_JIWA_SalesOrders_SOBatchPrintGetActions
which I have renamed and pointed in System to it and it works a treat!!
This software is so bloody powerful we just need to understand how to do things - fabulous.

Cheers