?
Solved

Need help with query for Pervasive

Posted on 2014-11-26
2
Medium Priority
?
143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 28

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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