0

I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

WITH CTE_DatesTable 
AS
(
SELECT CAST('20160401' as date) AS [date]
UNION ALL
SELECT DATE_ADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATE_ADD(dd, 1, [date]) <= '20170331'
) Select A.[date],
(SELECT CASE DATEPART(WEEKDAY,A.[date])
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY' End) WkDay,
Case When Isnull(C.Hdate,'')<>'' then C.Descr
When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 Then 'AlterWeekoff'
When B.WkOff=0 Then 'Weekoff'Else 'WD' End As WorkDay,
Case When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 or Isnull(C.Hdate,'')<>'' Then 'Holiday'
When B.WkOff=0 Then 'Holiday' Else 'WeekDay' End As WorkDescr
From CTE_DatesTable A Left Join BPC_WDD1 B on DATEPART(dw,A.[Date])=B.WDay
Left Join BPC_HCL1 C on A.date=C.Hdate
OPTION (MAXRECURSION 0);
  • 2
    Since it's not recursive, you can convert your CTE to a derived table. You then need to edit the query for all the other SQL differences between SQL Server and MySQL (date functions, square brackets, etc). – ypercubeᵀᴹ Nov 15 '16 at 11:02
  • 1
    please see stackoverflow.com/questions/1382573/… – Nawaz Sohail Nov 15 '16 at 11:08
1

Onetime action: Build a table with lots of dates-- a la DatesTable. (This can be done with a cross join to help generate lots of numbers, then use + INTERVAL n DAY for turning into dates.)

Then LEFT JOIN (with a WHERE clause) from the DatesTable to the other table(s).

Note that you will need to rewrite most date operations because of different syntax.

With MariaDB, you can use a pseudo table like seq_1_to_1000 to get a bunch of numbers. Also, MariaDB has CTEs. (MySQL will be getting CTEs sometime in version 8.0.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.