Simple view over JIWA custom data.

[pricerc]

A couple of Advanced ERP's clients make use of substantial quantities of custom fields, and querying the data for use in reports can be a bit of a challenge.

Until fairly recently, when I came across an interesting 'pivot' query methodology for SQL. It's been around for a while, but I only discovered it recently.

So I thought I'd share an example. This one is for custom task details in Service Manager, but the same method will work with any of them.

Code: Select all

ALTER VIEW [dbo].[AERP_SM_TaskDetails] AS
SELECT TaskID
, MAX(CASE WHEN (SettingID = 'Setting-00') THEN Contents ELSE NULL END) AS Condition
, MAX(CASE WHEN (SettingID = 'Setting-01') THEN Contents ELSE NULL END) AS WarrantySeal
, MAX(CASE WHEN (SettingID = 'Setting-02') THEN Contents ELSE NULL END) AS ProofOfPurchase
, MAX(CASE WHEN (SettingID = 'Setting-03') THEN Contents ELSE NULL END) AS DateOfSale
, MAX(CASE WHEN (SettingID = 'Setting-04') THEN Contents ELSE NULL END) AS Notes
, MAX(CASE WHEN (SettingID = 'Setting-05') THEN Contents ELSE NULL END) AS Replaced
, MAX(CASE WHEN (SettingID = 'Setting-06') THEN Contents ELSE NULL END) AS TBR
, MAX(CASE WHEN (SettingID = 'Setting-07') THEN Contents ELSE NULL END) AS Abused
, MAX(CASE WHEN (SettingID = 'Setting-08') THEN Contents ELSE NULL END) AS BER
, MAX(CASE WHEN (SettingID = 'Setting-09') THEN Contents ELSE NULL END) AS NFF
FROM SM_TaskCustomSettingValues
GROUP BY TaskID


You need to replace the values 'Setting-00' to 'Setting-09' with the actual SetttingID value from your custom field definition table. Adding additional columns is really trivial, just copy & paste one of the lines and replace the relevant bits.

Technically, it should be possible to join on to the field definition table and use the names of the fields, but I figured for most people there'd be little benefit, and this way works faster too.

In practice, I've also added 'outer' tables and fields to the views to make them a bit more useful, but this logic remains the same.

You could also fairly easily add data type conversions into the view if necessary.

/Ryan

[Mike.Sheen]

Very nice !

For some reason I'd missed this post before!

[pricerc]

Very nice !

For some reason I'd missed this post before!

Thanks Mike.

btw, credit for me discovering the technique goes to the folk over at http://www.sqlservercentral.com, recommended reading for anyone doing SQL Server stuff.

[pricerc]

Just needed this for another client, and decided I didn't want to copy and paste 21 setting IDs and names.

This query generates the query code for Sales Order custom data, using the SettingName field as a column name.

Very easy to adapt for other custom data - just change the table name and 'Primary Key'/'GROUP BY' column.

Code: Select all

DECLARE @query nvarchar(4000)
SELECT @query = 'SELECT InvoiceID'
 
SELECT @query = @query + char(13) + char(10) +
   ',MAX(CASE WHEN (SettingID = ' + QUOTENAME([SettingID],'''') +
   ') THEN Contents ELSE NULL END) AS ' + QUOTENAME(SettingName)
  FROM [SO_CustomSetting]
 
SELECT @query = @query + char(13) + char(10)
+ ' FROM SO_CustomSettingValues
   WHERE COALESCE(Contents,'''') <> ''''
   GROUP BY InvoiceID'

SELECT @query


Generates this query in JiwaDemo:

Code: Select all

SELECT InvoiceID
,MAX(CASE WHEN (SettingID = '000000002A0000000308') THEN Contents ELSE NULL END) AS [AutomaticallyProcessThisInvoice]
,MAX(CASE WHEN (SettingID = '000000001V000000000R') THEN Contents ELSE NULL END) AS [Delivery Run]
,MAX(CASE WHEN (SettingID = '000000001V000000000Z') THEN Contents ELSE NULL END) AS [Gift Product]
,MAX(CASE WHEN (SettingID = '000000001V000000000V') THEN Contents ELSE NULL END) AS [Grading]
,MAX(CASE WHEN (SettingID = '000000001V0000000010') THEN Contents ELSE NULL END) AS [Handling Notes]
,MAX(CASE WHEN (SettingID = '000000001V0000000011') THEN Contents ELSE NULL END) AS [Handling Time]
,MAX(CASE WHEN (SettingID = '000000001V000000000Y') THEN Contents ELSE NULL END) AS [HAZCHem]
,MAX(CASE WHEN (SettingID = '000000001V000000000S') THEN Contents ELSE NULL END) AS [Invoice Format Type]
,MAX(CASE WHEN (SettingID = '000000001V000000000U') THEN Contents ELSE NULL END) AS [Priority]
,MAX(CASE WHEN (SettingID = '000000001V0000000012') THEN Contents ELSE NULL END) AS [Shipping Cubic Volume]
,MAX(CASE WHEN (SettingID = '000000001V000000000X') THEN Contents ELSE NULL END) AS [Warranty Date]
,MAX(CASE WHEN (SettingID = '000000001V000000000T') THEN Contents ELSE NULL END) AS [Workflow Status]
,MAX(CASE WHEN (SettingID = '000000001V000000000W') THEN Contents ELSE NULL END) AS [Works Schedule]
 FROM SO_CustomSettingValues
   WHERE COALESCE(Contents,'') <> ''
   GROUP BY InvoiceID

[Mike.Sheen]

Just needed this for another client, and decided I didn't want to copy and paste 21 setting IDs and names.

This query generates the query code for Sales Order custom data, using the SettingName field as a column name.

Very easy to adapt for other custom data - just change the table name and 'Primary Key'/'GROUP BY' column.

Again, thanks Ryan - your contributions are most welcome... I hope some others come across this and find it of use.