andy
Posts: 183
|
| Posted: 02/26/2010, 1:33 PM |
|
I am trying to create a report based on the following sql query. It works fine without the ORDER BY clause at the end.
It doesn't work with it!
This sql query works fine in Navicat on my mysql database.
Anyone know how to hardcode a custom (non-alphabetical) sort order using an expression that will work with CCS reports/grids etc.
(N.B. without the ORDER BY CASE... clause it orders alphabetically)
SELECT
(CASE when estimate.estimate_id is not null then 'estimate' end) AS Activity,
entity.DisplayName,
estimate.Subtotal,
estimate.estimate_id AS TransactionID
FROM
estimate
Inner Join entity ON estimate.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when salesorder.salesorder_id is not null then 'sales order' end) AS Activity,
entity.DisplayName,
salesorder.Subtotal,
salesorder.salesorder_id AS TransactionID
FROM
salesorder
Inner Join entity ON salesorder.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when invoice.invoice_id is not null then 'invoice' end) AS Activity,
entity.DisplayName,
invoice.Subtotal,
invoice.invoice_id AS TransactionID
FROM
invoice
Inner Join entity ON invoice.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when creditnote.creditnote_id is not null then 'credit note' end) AS Activity,
entity.DisplayName,
creditnote.Subtotal,
creditnote.creditnote_id AS TransactionID
FROM
creditnote
Inner Join entity ON creditnote.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when purchaseorder.purchaseorder_id is not null then 'purchase order' end) AS Activity,
entity.DisplayName,
purchaseorder.TotalAmount,
purchaseorder.purchaseorder_id AS TransactionID
FROM
purchaseorder
Inner Join entity ON purchaseorder.VendorRef = entity.entity_id
ORDER BY (CASE
Activity when 'estimate' then 1
when 'sales order' then 2
when 'invoice' then 3
when 'credit note' then 4
when 'purchase order' then 5 end)
Thanks for any advice 
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
 |
 |
Oper
Posts: 1195
|
| Posted: 02/26/2010, 2:24 PM |
|
what you are trying is to order
all the Union by the type of Activity
i will add a new Field to every UNION at the end
1 as OrderActivity for first UNION
2 as OrderActivity for Second UNION
and then teh order By user
ORDER BY OrderActivity
also if this is a Big File i will Create a File and Sort/ Order After.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
andy
Posts: 183
|
| Posted: 02/28/2010, 3:59 PM |
|
Thanks Oper
That did the trick. Here's the updated query:
SELECT
(CASE when estimate.estimate_id is not null then 'estimate' end) AS Activity,
(CASE when estimate.estimate_id is not null then '4' end) AS OrderActivity,
entity.DisplayName,
estimate.Subtotal,
estimate.estimate_id AS TransactionID
FROM
estimate
Left Outer Join entity ON estimate.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when invoice.invoice_id is not null then 'invoice' end) AS Activity,
(CASE when invoice.invoice_id is not null then '3' end) AS OrderActivity,
entity.DisplayName,
invoice.Subtotal,
invoice.invoice_id AS TransactionID
FROM
invoice
Left Outer Join entity ON invoice.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when creditnote.creditnote_id is not null then 'credit note' end) AS Activity,
(CASE when creditnote.creditnote_id is not null then '2' end) AS OrderActivity,
entity.DisplayName,
creditnote.Subtotal,
creditnote.creditnote_id AS TransactionID
FROM
creditnote
Left Outer Join entity ON creditnote.CustomerRef = entity.entity_id
UNION ALL
SELECT
(CASE when purchaseorder.purchaseorder_id is not null then 'purchase order' end) AS Activity,
(CASE when purchaseorder.purchaseorder_id is not null then '1' end) AS OrderActivity,
entity.DisplayName,
purchaseorder.TotalAmount,
purchaseorder.purchaseorder_id AS TransactionID
FROM
purchaseorder
Left Outer Join entity ON purchaseorder.VendorRef = entity.entity_id
order BY OrderActivity asc
One word of WARNING !
As the SQL query is effectively doing the grouping, remove any Group by on the report!!
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
mrachow
Posts: 509
|
| Posted: 03/04/2010, 3:38 PM |
|
Hi oper,
would like to give you the nickname Joe
(denoting Joe Celko) 
_________________
Best regards,
Michael |
|
|
|
|
