Solved

# Looping thru 90 codes

Posted on 2009-12-28
343 Views
I need to query up 90 family codes. Right now I started to SQLexec() each one one by one because (still learning) I need to capture the code, year, and add up all 12 months as a sum() as total

So I was thinking I could just loop throught it? It would just replace the family code on each pass? Create a varible for the code but then I would have to load/ store all 90 some where first then go thru the second step of creating the 90 corsors?
0
Question by:jaymz69

LVL 41

Expert Comment

It is much faster if you select records for all 90 family codes at once. The SQL command will be more complex - you can prepare it in a loop as a text string - but it wil be executed just once which is a big time advantage.

But if you have the SQL command prepared already and you don't like to change it then you may call the SQL exec 90 times in a loop and append the result cursor into some new cursor in the same loop.
``````*-- 90 family codes in the WHERE clause:

lcCodes = ""

FOR lnI = 1 TO 90

lcYourCode =  .... && Assign the family code value (suppose character data type)

lcCodes = lcCode + IIF(EMPTY(lcCodes), "", ", ") + "'" + lcYourCode + "'"

NEXT

lcSQL = "SELECT * FROM YourTable WHERE FamilyCode IN (" + lcCodes + ")"

lnRetVal = SQLEXEC(lnSqlHandle, lcSQL, "cResult")

*-- etc.

*-- 90 SQLEXEC calls:

FOR lnI = 1 TO 90

lcYourCode =  .... && Assign the family code value (suppose character data type)

lcSQL = "SELECT * FROM YourTable WHERE FamilyCode = ?lcYourCode"

lnRetVal = SQLEXEC(lnSqlHandle, lcSQL, "cResult")

IF USED("cAllResults")

SELECT cAllResults

APPEND FROM cResult

ELSE

SELECT * FROM cResult INTO CURSOR cAllResults NOFILTER READWRITE

ENDIF

NEXT
``````
0

LVL 41

Expert Comment

Notes:
The lnRetVal value should be chcekd after the SQLEXEC function call.
Family codes cannot contain apostrophs. If they do you have to duplicate them.
Some SQL implementation could have restricted number of parameters in the IN() operator.

It is always better if you post some code and ask why it is not working as you need.
0

Author Comment

They are 2 char only

A1
A2
C#
C@
D%
D5
.......

Also on this line, still early for me...lol..What do you mean by  assign it a value?

lcYourCode =  .... && Assign the family code value (suppose character data type)

0

LVL 41

Expert Comment

Two charcodes are OK.

If you want to use ?variable parameter in your query then you need to know (to have, to assign) its value prior to SQLEXEC call.  Simply replace dots ....  in my example by your code value.

Just imagine how the string will look before the SQLEXEC. If it will be valid SQL command then everything is OK. If there is some part missing then you have to correct it.

Simply place the following command before the SQLEXEC:

? lcSQL

and you'll see it on the screen.
0

LVL 12

Expert Comment

You might also need to consider what your backend requires from a syntax perspective.
Perhaps you will need to put the appropriate Single Quote or Double Quote characters around your text values.

For example in a SQL Server backend you need Single Quotes
lcSQL = "SELECT * FROM YourTable WHERE FamilyCode = '" + lcFamilyCode + "'"
ln   RetVal = SQLEXEC(lnSqlHandle, lcSQL, "cResult")

One way to test your lcSQL syntax is to try in directly into the backend Database's Query Engine (i.e.  SQL Query Analyzer for SQL Server).   If the syntax works there it should work in your VFP code as well.

Good Luck

0

Author Comment

still not understanding this part:

lcYourCode =  ....

I query the table for all the family codes into cFamily

Now I need to loop through it to get the other tables 12 month tables per each code, by the loop

0

LVL 41

Accepted Solution

The you dont need FOR loop but you have to replace it by SCAN loop and the command

lcYourCode =

must be completed from the cFamily cursor:

SELECT cFamily
SCAN
lcYourCode =  cFamily.Code
lcSQL = "SELECT * FROM YourTable WHERE FamilyCode = ?lcYourCode"
lnRetVal = SQLEXEC(lnSqlHandle, lcSQL, "cResult")

IF USED("cAllResults")
SELECT cAllResults
APPEND FROM cResult
ELSE
SELECT * FROM cResult INTO CURSOR cAllResults NOFILTER READWRITE
ENDIF
ENDSCAN

0

Author Comment

ok I see wqhta your saying....

thanks

0

Author Comment

what
0

Author Comment

why does it program error on me the second time with" c:\... cResults.dbf not found"

I know the cursor is there in the data..

O, do I need to use cReult everytime?
I see cResult on the second loop still hase the first code in it... when I debug

0

Author Comment

APPEND FROM DBF("cResult")

thanks again
0

Author Comment

now that I get it all in one loop I still have the last SELECT that would gather all them  togther by the customer number for each row..

see attachment how is currently goes.

here is the code from help of you all before, thanks!
That gathers all the seperate code cursors and joins them all together. This one is for product code but same theory. I can't see doing thise for 90 codes. This one is for 20...

``````*--- create customer sales by product and target "ALL"

Select cCustMarketingCodesALL.cmcust, cCustMarketingCodesALL.cmname, cCustSalesMan.smno, cCustSalesMan.smname, ;

NVL(c13603.Park_Ave_2009_Total_Sales, 00000000.00) as Park_Ave_2009_Total_Sales,  NVL(c13604.Bellagio_Unback_2009_Total_Sales, 00000000.00) as Bellagio_Unback_2009_Total_Sales, ;

NVL(c22653.Bamboo_3_1_2_HS_2009_Total_Sales, 00000000.00) as Bamboo_3_1_2_HS_2009_Total_Sales, NVL(c22659.Johnson_Lumb_G1_2009_Total_Sales, 00000000.00) as Johnson_Lumb_G1_2009_Total_Sales, ;

NVL(c22660.Johnson_Lumb_G2_2009_Total_Sales, 000000000.00) as Johnson_Lumb_G2_2009_Total_Sales , NVL(c22661.Johnson_Lumb_G3_2009_Total_Sales, 00000000.00) as Johnson_Lumb_G3_2009_Total_Sales, ;

NVL(c22680.Grand_Mesa_2009_Total_Sales, 00000000.00) as Grand_Mesa_2009_Total_Sales, NVL(c82110.TAS_LVT_ANTQ_Pine_2009_Total_Sales, 00000000.00) as TAS_LVT_ANTQ_Pine_2009_Total_Sales, ;

NVL(c82113.TAS_LVT_Comml_2009_Total_Sales, 00000000.00)  as TAS_LVT_Comml_2009_Total_Sales, ;

NVL(c82140.Titan_Grand_Plank_2009_Total_Sales, 00000000.00) as Titan_Grand_Plank_2009_Total_Sales, NVL(c83041.Congress_Oak_3_1_4_2009_Total_Sales, 00000000.00) as Congress_Oak_3_1_4_2009_Total_Sales, ;

NVL(c33500.Paratimber_2009_Total_Sales, 00000000.00) as Paratimber_2009_Total_Sales, NVL(c22654.Bamboo_5_HS_2009_Total_Sales, 00000000.00) as Bamboo_5_HS_2009_Total_Sales, ;

NVL(c13602.Manhattan_2009_Total_Sales, 00000000.00) as Manhattan_2009_Total_Sales, NVL(c2490.Prime_Supply_Oak_2009_Total_Sales, 00000000.00) as Prime_Supply_Oak_2009_Total_Sales, ;

NVL(c22652.Strand_Bamboo_2009_Total_Sales, 00000000.00) as Strand_Bamboo_2009_Total_Sales ;

from ((((((((((((((((((((cCustSalesMan ;

left Join c13602 On c13602.cpcust = cCustSalesMan.cmcust) ;

left Join c13603 On c13603.cpcust = cCustSalesMan.cmcust) ;

left Join c13604 On c13604.cpcust = cCustSalesMan.cmcust) ;

left Join c13605 On c13605.cpcust = cCustSalesMan.cmcust) ;

left Join c13607 On c13607.cpcust = cCustSalesMan.cmcust) ;

left Join c22652 On c22652.cpcust = cCustSalesMan.cmcust) ;

left Join c22653 On c22653.cpcust = cCustSalesMan.cmcust) ;

left Join c22654 On c22654.cpcust = cCustSalesMan.cmcust) ;

left Join c22659 On c22659.cpcust = cCustSalesMan.cmcust) ;

left Join c22660 On c22660.cpcust = cCustSalesMan.cmcust) ;

left Join c22661 On c22661.cpcust = cCustSalesMan.cmcust) ;

left Join c22680 On c22680.cpcust = cCustSalesMan.cmcust) ;

left Join c2490 On c2490.cpcust = cCustSalesMan.cmcust) ;

left Join c33500 On c33500.cpcust = cCustSalesMan.cmcust) ;

left Join c82110 On c82110.cpcust = cCustSalesMan.cmcust) ;

left Join c82113 On c82113.cpcust = cCustSalesMan.cmcust) ;

left Join c82119 On c82119.cpcust = cCustSalesMan.cmcust) ;

left Join c82121 On c82121.cpcust = cCustSalesMan.cmcust) ;

left Join c82140 On c82140.cpcust = cCustSalesMan.cmcust) ;

left Join c83041 On c83041.cpcust = cCustSalesMan.cmcust) ;

inner Join cCustMarketingCodesALL On cCustSalesMan.cmcust=cCustMarketingCodesALL.cmcust Into Cursor cALL_customers
``````
marketing-target-ta-customers.xls
0

Author Comment

Now with the loop I only get 1 cursor that would need to JOIN with the other tables..
0

LVL 41

Expert Comment

Great! It seems you've solved all problems. You are right, the loop will simplify the SQL select significantly.
0

Author Comment

not all... LOL

when I do the cross tab on my cResults.dbf
it creates 4,939 rows, the cross_tab supports up to 254, something like that is says when I try it...

so I still get all my results one by one in row format when I still need it cross_tabbed per custnum...

0

Author Comment

"There are too many unique valuesfor the column field. The maximum is 254."

that is the messageBox I get when it runs...
0

LVL 41

Expert Comment

Cross-tab means to use some existing (and distinct) column values for cross-tabbed column names. And DBF format supports just 254 columns. If the selected column contains more than 254 values then it cannot be used for column names.

You've probably selected wrong column or your GROUP BY is not correct. The input cursor for the Cross-tab program must have as many rows as the output cursor has columns.
0

Author Comment

I can have up to 122 family codes accross, so that is under then....

I did add some fields to the cross tab query...??
0

LVL 41

Expert Comment

Did you use Cross-tab Wizard?
0

LVL 41

Expert Comment

The input cursor should contain just three columns. Everything else will be joined later.

BTW, it your family codes are C#, C@ then it will be replaced by C_ on output which is not good because you probably need two columns for these two codes.

0

LVL 41

Expert Comment

Try to create some small table with 3 columns and let say 7 rows and use it as input for Cross-tab Wizard - it is the best way how to learn it.
0

Author Comment

I did the create myOwnDbf and INSERT Values(7)

and did the cross_tab wizard and see how it flows.

The C# did go to C_..... on all special char.

Now I need to get those special chars back....
0

LVL 41

Expert Comment

Column names can be alphanumeric only, so you have to add one more column to your original table which will contain alphanumeric family names.

E.g.

Family Code   Family name
A1                  A1
A2                  A2
C#                 CSHARP
C@                 CAT
D%                 DPROC
D5                  D5

And above names you can use in Cross-tab wizard.
0

Author Comment

Do I have to add the names or is it programiclly done?

0

LVL 41

Expert Comment

Of course, you have to add these names because Cross-tab wizard is not as powerfull... (You've seen already - changes D%, D@, and D# to D_)
0

Author Comment

wow, prettu slick until it comes to the cross_tab part then. It takes like 10 minutes to do also...

0

Author Comment

wow this takes way too long... maybe 20-25 minutes to do the cross_tab

there has to be another way to loop through all 90 codes then group by cust#, cross_tab the codes across.....
0

Author Comment

I have the totals already it is just grouping the cust# and displaying the codes across
0

LVL 41

Expert Comment

In fact, if you have cursor ready for cross-tabbing and you just need to transform rows to columns without any calculations then it would need seconds no minutes.
0

Author Comment

I have not done that before, transform rows to coloumns.

that sounds better if I can group by customer then transform...

0

LVL 41

Expert Comment

It is still unclear what takes the long time in your case. Maybe it is data groupping and then the Cross-tabbing is fast.
0

Author Comment

Alot of records to group. I think like 7000

so know any good knowledge base on the row and column switch?
0

LVL 41

Expert Comment

0

LVL 41

Expert Comment

BTW, 7000 records is not many to group. The Cross-tab utility is probably slow.
0

Author Comment

so nothing I can do to the utility...?

Thanks for the documentations...
0

Author Comment

0

LVL 41

Expert Comment

Great!

BTW, we should discuss Cross-tab in different question (http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_25002290.html) which is not closed yet.

I'll place a back-link to this one into it.
0

Author Comment

oh yes that will work for there too.

Thanks Again!

Now I just need to get back the symbols... C!,C#........instead of C_,C_....
0

Author Comment

it is now taking it everytime for 7,896 records to .265 seconds to cross tab!!
0

LVL 41

Expert Comment

If these strings are column names then you cannot change them. If you need them on output somewhere then you will need conversion table (two columns, one for family code, the second one for column name).
0

## Featured Post

Microsoft Visual FoxPro (short VFP) is a programming language with itâ€™s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msdâ€¦
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :) Â Step 1: Make surâ€¦
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦