[Solved] Crosstab Query Help with Error

[gkick]

Hello,

Needing some help with a crosstab/pivotquery for HSQL2.5 backend. In my research I only found a single example in the Northwind.odb of Access2Base.
Now I need to create a form/report listing Sales by Customer by Month in generic Pivot format.
What I have so far:

qryInvoiceStatus

Code: Select all

SELECT "tblCustomer"."customer_name" "Client", "tblJob"."jobid" "InvNumber", "tblJob"."Date" "InvDate", "qryBaseCalcsForInvoiceAccounts"."Payable", "tblJob"."terms" "Terms", "tblPmtmethod"."method" "Payment Method", "tblJob"."paidamount" "Payment", "tblJob"."paydate" "Paydate", "tblJob"."paid" "Paid", TO_CHAR( DATEADD( 'day', "terms", "Date" ), 'DD/MM/YYYY' ) "Due", DATEDIFF( 'dd', "paydate", DATEADD( 'day', "terms", "Date" ) ) "DPV", DATEDIFF( 'dd', DATEADD( 'day', "terms", "Date" ), TODAY( ) ) * - 1 "DCV", NVL2( "paydate", DATEDIFF( 'dd', "paydate", DATEADD( 'day', "terms", "Date" ) ), DATEDIFF( 'dd', DATEADD( 'day', "terms", "Date" ), TODAY( ) ) * - 1 ) "DCV_DPV", "tblSalesRep"."sales_rep" "SalesPerson", "qryBaseCalcsForInvoiceAccounts"."acttax", "paidamount" - "Payable" "Variance", "qryBaseCalcsForInvoiceAccounts"."TotalTaxInclusive", ( "TotalTaxInclusive" - "actdiscount" ) * "commission" "Commission", "TotalTaxInclusive" - "actdiscount" "CommBase", "tblJob"."cancelled", "tblTaxRates"."rate", "tblJob"."Job Number" "PurchaseOrder" FROM "tblJob", "tblPmtmethod", "tblCustomer", "qryBaseCalcsForInvoiceAccounts", "tblSalesRep", "tblTaxRates" WHERE "tblJob"."paymentmethod" = "tblPmtmethod"."pmid" AND "tblJob"."customer_id" = "tblCustomer"."customer_id" AND "tblJob"."jobid" = "qryBaseCalcsForInvoiceAccounts"."jobid" AND "tblSalesRep"."sales_rep" = "tblSalesRep"."sales_rep" AND "tblJob"."salesrep" = "tblSalesRep"."srid" AND "tblJob"."tax" = "tblTaxRates"."sid" AND "tblJob"."paid" = FALSE AND "tblJob"."cancelled" = FALSE ORDER BY "InvNumber" DESC, "InvDate" DESC

qryPivotSource

SELECT "Client", "InvDate", "Payable", "SalesPerson", "acttax" FROM "qryInvoiceStatus"


third attempt to create the crosstab as per Northwind example to be modified to display month rather then quarters

Code: Select all

SELECT 
        "Client",        
        SUM(
                CASE "Quarter" WHEN '2020Q1' THEN "Data" ELSE 0 END
        ) As "2020Q1", 
        SUM(
                CASE "Quarter" WHEN '2020Q2' THEN "Data" ELSE 0 END
        ) As "2020Q2", 
        SUM(
                CASE "Quarter" WHEN '2020Q3' THEN "Data" ELSE 0 END
        ) As "2020Q3", 
        SUM(
                CASE "Quarter" WHEN '2020Q4' THEN "Data" ELSE 0 END
        ) As "2020Q4", 
        SUM("Data") As "Total" 
FROM 
        (
                SELECT 
                        "qryPivotSource"."Client" As "Client", 
                         YEAR("InvDate") || 'Q' || QUARTER("InvDate") As "Quarter", 
                        SUM(
                                "qryPivotSource"."Payable" AS "Data" 
                FROM 
                        "qryPivotSource”, 
                        
                WHERE 
                         YEAR("qryPivotSource"."InvDate") = 2020
                GROUP BY 
                        "Client", 
                        "Quarter") 
GROUP BY 
        "Client", 
         
ORDER BY 
        "Total"

running this query I end up with this error.

Any ideas where I am wrong or whats missing ?

Thanks for your thoughts

[Sliderule]

I see, at least three issues from your Graphic:

1. Code: Select all

   SUM("qryPivotSource"."Payable" AS "Data"

   should be:
   

   Code: Select all

   SUM("qryPivotSource"."Payable") AS "Data"

2. Code: Select all

   FROM "qryPivotSource",

   should be:
   

   Code: Select all

   FROM "qryPivotSource"

3. Code: Select all

   GROUP BY "Client",

   should be:
   

   Code: Select all

   GROUP BY "Client"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[robleyd]

You seem to be missing a closing parenthesis

Code: Select all

SUM(
                                "qryPivotSource"."Payable" AS "Data" 

[gkick]

Thank you gentlemen, fixed those mistakes, almost there.