Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Skylinc (Programmer) (OP)
2 Nov 10 9:35
Hi all

I have a table like this:
cn_name, fcst1, fdate1, fcst2, fdate2, ... fcst12, fdate12

I would like to know how to write hte query to return a result like this:

cn_name, fcst1, fdate1
cn_name, fcst2, fdate2
...
cn_name, fcst12, fdate12


At first it looks like i can use UNION ALL, but if I try that will the 12 resulting queries, I get an error message saying "too many unions".

Is there any way of transposing those columns to rows? Does Foxpro have a PIVOT equivelant that I can use? I'm running the query from VB.Net Code, and would prefer not running 12 queries.

Any help would be appreciated! :D
MrDataGuy (Programmer)
2 Nov 10 10:23
I suggest that you look into using the VFPxTab.Prg (A util program that comes with VFP) This program may be what you need.  

Another option: http://vfpx.codeplex.com/wikipage?title=FoxCharts%20Tools
Look at the GetChartDataFromRows PRG

Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com

 

jrbbldr (Programmer)
2 Nov 10 10:26
"Does Foxpro have a PIVOT equivelant"

In a word - No.

Since you are doing your work in VB.Net and not VFP, I cannot help you with your code.

However if you were doing the work in VFP there are a number of us who can assist you a number of ways to approach this with your code.

Good Luck,
JRB-Bldr
 
jrbbldr (Programmer)
2 Nov 10 10:57
MrDataGuy - He says that he is doing this work in VB.Net.  
As such neither VFPxTab.Prg nor GetChartDataFromRows PRG would not be code that he could/would execute in VB.Net.

I don't know about VB.Net's capability to Automate Excel, but if it could work as well as VFP (personally in doubt) he could export the VFP data to Excel and then do his row/column transposition there and then Export it from Excel to something he could use.

I know that's pretty much a kludge approach, but hey he's not working in VFP, so maybe that all that's available to him.

Skylinc - Good Luck,
JRB-Bldr


 
MikeLewis (Programmer)
2 Nov 10 11:44
Slyink, you said "At first it looks like i can use UNION ALL, but if I try that will the 12 resulting queries, I get an error message saying "too many unions"."

"Too many unions" is not a VFP error message. VFP does not impose a limit on the number of unions in a SELECT.

Perhaps it's something in VB.Net that imposes that limit (although I can't see why it should). If so, have you considered breaking the query down into, say, two separate SELECTs, with six unions in each. Send the results to two temporary tables, then union the two tables into one final result.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

TamarGranor (Programmer)
2 Nov 10 16:21
Actually, prior to VFP 9, there was a limit on the number of unions in a query. I think it was 9.

Skylinc, the real problem here is that your database is badly designed. Having numbered columns like that is a sure sign that data's being repeated. It's much better design to use an additional table structured pretty much like the result you're looking for.

Tamar
MrDataGuy (Programmer)
2 Nov 10 17:08
I know the limit to the number of tables that could be joined was 9, so I assume that UNION had the same limit.

Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com

 

OlafDoschke (Programmer)
2 Nov 10 17:50
In fact VFP has a Pivot table wizard in the Menu Tools->Wizards->Pivot Table.

Besides that the union approach is valid. As others already mentions the limitation dropped in VFP9, but you could do unions in two stages or simply use an appraoch of APPENDs, as in

CODE

Select cn_name, fcst1 as fcst, fdate1 as fdate from source into cursor curStart readwrite
Select cn_name, fcst2 as fcst, fdate2 as fdate from source into cursor curAppend

Select curStart
Append from DBF("curAppend")

Select cn_name, fcst3 as fcst, fdate3 as fdate from source into cursor curAppend

Select curStart
Append from DBF("curAppend")

...
Select curStart
Copy To destination.DBF Database Destionation.DBC

Bye, Olaf.
Skylinc (Programmer) (OP)
3 Nov 10 2:24
Thanks for the responses guys.

I will try MikeLewis's approach first (as it seems the simplest one), and if that doesn't work, then I will see if I can chat to the DB Admin to see if he could change the DB, although this seems unlikely.

Thank you!
OlafDoschke (Programmer)
3 Nov 10 3:38
Mike Lewis is wrong, actually ERROR 1834 is "SQL: Too many UNIONs." and in previous VFP version it also could have been literally "too many unions" without the SQL: prefix.

Bye, Olaf.
Skylinc (Programmer) (OP)
3 Nov 10 3:42
The part of Mike's post that I will use is

"two separate SELECTs, with six unions in each. Send the results to two temporary tables, then union the two tables into one final result."

I already have the query split in two parts, and it does work, but merging the resulting CSV files into a single output is a time consuming process.
OlafDoschke (Programmer)
3 Nov 10 4:02
Splitting unions was somethin I too recommended: "you could do unions in two stages"

Why are you merging two csv files? The split unions should each result in a VFP table or cursor and you can these with another union and only do the csv output with the final table.

Or are you not only exceeding the limitation on the number of unions but also the final table size? I actually doubt the last, as you actually already have all data in a DBF initially, splitting columns to records would just add few bytes, actually one per record, the deletion mark byte.

Bye, Olaf.
MikeLewis (Programmer)
3 Nov 10 7:27
It's true that in VFP 8.0 and earlier the limit on the number of unions was nine, but that no longer applies.

Skylinc, what component are you using to access this table? If you're using the current OLE DB provider, that it compatible with VFP 9.0, and you shouldn't be subject to the limit.

But if you are using the old ODBC driver, that hasn't been updated since VFP 6.0, so the old limit will apply.

If that's the case, you should get the current OLE DB provider before you go any further. It might solve the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

Skylinc (Programmer) (OP)
11 Nov 10 8:42
Just to let you all know, that the biggest cause of my problem was in fact that I was using the old ODBC driver (as mentioned by Mike, thanks). I have since installed and started using the OLD DB diver, and all is sorted now.

Thank you all for the help :D

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close