Selecting data from a partitioned table in Oracle using VFP

[torturedmind]

We have a partitioned table in Oracle 10g and we are using VFP to SELECT data from it. The table is partitioned by year and are named P1, P2, ..., Pnn. I've constructed a view in VFP to query the records but no data appear. I found out that I need the PARTITION phrase to make things happen. In PL/SQL, i can say

SELECT alpha.* 
     FROM alpha alpha 
     [b][COLOR=blue]PARTITION (P13)[/color][/b]
     WHERE alpha.sdate BETWEEN ?xdate1 AND xdate2 
     ORDER BY alpha.enum, alpha.sdate, alpha.stime

Partition P13 is where all 2011 records are. My question is, how can I tell Oracle to look for data under a particular partition from VFP? I'm looking at macro substitution but don't really know how to implement it. Or maybe there is a better way.

TIA


kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Hi Kilroy,

You say the SELECT statement is correct PL/SQL syntax. In that case, you should be able to execute it as it stands from VFP. However, rather than using a remote view, have you tried to execute if via SQLEXEX()? If the syntax is correct, it should work.

I don't understand your reference to macro substition. Why do you need to do that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[torturedmind]

Thanks Sir Mike for the immediate response. It's been a while since I was here. Anyway, from the initial looks of it, I think SQLEXEC() will do the job. I just need to test what year xdate1 and xdate2 will fall under so that I can pass the PARTITION name as a macro substitution in Oracle side. Like I said, there is really a better way. I completely overlooked (if not really forgotten) good ol' SQLEXEC(). Just a lil nudge to my brain from an expert helps me start thinking again . I'll try using SQLEXEC() and post back the result soon. Thanks again sir.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Kilroy,

Yes, I noticed it has been a while since you stopped by. Good that you are still immersed in VFP.

Regarding the macro substitution: I don't think that's what you want. With SQLEXEC(), you can substitute variable information by building the command dynamically.

For example, if the value 13 in your example is the value that you want to substitute, you would do it like this:

lnPartition = 13   && or whatever
lcCmd = "SELECT * FROM Alpha PARTITION (P" + ;
  TRANSFORM(lnPartition) + ") WHERE sdate ..... etc. etc."

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Thanks Sir Mike

Mike - have you received Knighthood from the Queen for your great VFP Guru work (or anything else) at some point in time and not let us know about it?

JRB-Bldr

 

[Mike Lewis]

Well, I did ask the Palace to keep it quiet - out of sheer modesty, you understand - but it seems that word has got out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy