We have following SQL script in SQL Server 2012. We are about to write similar script in postgresql (HAWQ 1.3.1) at database conversion
SELECT *
FROM tablename_1
LEFT OUTER JOIN
(SELECT
SUM(b.OrderValue) AS OrderValue, b.OrderDate, b.Description
FROM
(SELECT *
FROM tablename_2 rcd
LEFT JOIN
(SELECT Distinct
afv.Item, afv.Description, afd.KeyField
FROM tablename_3 afd
JOIN tablename_3 afv ON afv.FormType = afd.FormType
AND afv.FieldName = afd.FieldName
AND afv.Item = afd.AlphaValue
WHERE
afd.FormType = 'CUS'
AND afd.FieldName = 'COR002') a ON a.KeyField = rcd.Customer
WHERE
OrderDate >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()), 101)) b
GROUP BY
b.OrderDate, b.Description) c ON rtr.CorpAcctName = c.Description
We tried and wrote the following script:
Above script compiled into postgresql ( VERSION HAWQ 1.3.1)
SELECT * from tablename_1 rtr LEFT OUTER JOIN
(SELECT SUM(b."OrderValue") as OrderValue,b."OrderDate", b."Description" from
(SELECT * from tablename_2 rcd
LEFT JOIN
( SELECT Distinct afv."Item", afv."Description", afd."KeyField"
FROM tablename_2 afd
Join tablename_3 afv on afv."FormType" = afd."FormType" and afv."FieldName"=afd."FieldName" and afv."Item"=afd."AlphaValue"
Where afd."FormType" = 'CUS'and afd."FieldName" = 'COR002') a
ON a."KeyField" =rcd."Customer" where "OrderDate">=TO_CHAR((CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY)) ,'MM-DD-YYYY')) b
group by b."OrderDate", b."Description") c
on rtr."CorpAcctName"=c."Description"
Also tried with :
**
**
i wanted to achieve first day of month from my current_date
So you want everything that was created after the start of the current month.
A simple
where "OrderDate" >= date_trunc('month', current_date)
will do that.
Details on the date_trunc() method can be found in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html
You should understand what your expression TO_CHAR((CURRENT_DATE -(CURRENT_DATE-INTERVAL '1 DAY')) ,'MM-DD-YYYY') is doing so that you avoid that error in the future:
First: current_date - interval '1 day' subtracts an interval from a date, which yields a timestamp: "yesterday" at 00:00:00.
Then you subtract that from today's date, so current_date - timestamp '2015-11-13 00:00:00.0' (if today is 2015-11-14).
This yields an interval: 0 years 0 mons 1 days 0 hours 0 mins 0.00 secs
You then pass that interval to the to_char() function which formats the passed interval. As it only has "1 day", no year, no month, the result of applying the format string 'MM-DD-YYYY' on that does indeed yield 00-01-0000.
You then compare this character value against a real date - which is also something you should not do.
You should really get rid of those dreaded quoted identifiers. They are much more trouble than they are worth it
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments