Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help with query for Pervasive

Posted on 2014-11-26
2
Medium Priority
?
151 Views
Last Modified: 2014-12-30
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.
0
Comment
Question by:Gerhardpet
2 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 40467071
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

0
 
LVL 29

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 40467113
If InvoiceDate is a String field, then you cannot use Extract.  Use Substring instead!
SELECT d.whse, d.part_no, LEFT(d.invoice_date,4), RIGHT(LEFT(d.invoice_date,6),4)
FROM sales_history_items AS d

Open in new window

MID might also work, but I sometimes have issues with that.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question