Avatar of Gerhardpet
Gerhardpet
Flag for Canada asked on

Need help with query for Pervasive

I have a query that I want to use for extracting data form a Pervasive database and then insert to a table.

This is the first part of the query which works in PostgreSQL

SELECT d.whse, d.part_no, EXTRACT(YEAR FROM d.invoice_date), EXTRACT(MONTH FROM d.invoice_date)
FROM sales_history_items AS d

Open in new window


In Pervasive the invoice_date is a string like so 20141128

Can someone help me with this? Once I got this working I will post the whole query is I need more help.
DatabasesPostgreSQLSQL

Avatar of undefined
Last Comment
Bill Bach

8/22/2022 - Mon
Mirtheil

I'm guessing the problem with Pervasive is the EXTRACT part.  If that's not the case, post the version of Pervasive and the error you are seeing.  
If it is the EXTRACT, the Pervasive syntax for EXTRACT (in v10 and v11, at least) is:
EXTRACT (extract_field, extract_source)

Open in new window

So your query would be :
SELECT d.whse, d.part_no, EXTRACT(YEAR, d.invoice_date), EXTRACT(MONTH, d.invoice_date)
FROM sales_history_items AS d

Open in new window

ASKER CERTIFIED SOLUTION
Bill Bach

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes