• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Looping thru 90 codes

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
jaymz69
Asked:
jaymz69
  • 22
  • 17
1 Solution
 
pcelbaCommented:
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

Open in new window

0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pcelbaCommented:
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
 
jrbbldrCommented:
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
 
jaymz69Author Commented:
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
 
pcelbaCommented:
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
 
jaymz69Author Commented:
ok I see wqhta your saying....


thanks

0
 
jaymz69Author Commented:
what
0
 
jaymz69Author Commented:
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
 
jaymz69Author Commented:

I had to add the      dbf("cReults")

so the code read :
 APPEND FROM DBF("cResult")

thanks again
0
 
jaymz69Author Commented:
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(c13605.Palisades_2009_Total_Sales, 00000000.00) as Palisades_2009_Total_Sales, NVL(c13607.Bellagio_Backed_2009_Total_Sales, 00000000.00) as Bellagio_Backed_2009_Total_Sale,;
		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(c82119.TAS_LVT_Premium_2009_Total_Sales, 00000000.00) as TAS_LVT_Premium_2009_Total_Sales, NVL(c82121.TAS_LVT_ProFloor_2009_Total_Sales, 00000000.00) as LVT_ProFloor_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

Open in new window

marketing-target-ta-customers.xls
0
 
jaymz69Author Commented:
Now with the loop I only get 1 cursor that would need to JOIN with the other tables..
0
 
pcelbaCommented:
Great! It seems you've solved all problems. You are right, the loop will simplify the SQL select significantly.
0
 
jaymz69Author Commented:
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
 
jaymz69Author Commented:
"There are too many unique valuesfor the column field. The maximum is 254."

that is the messageBox I get when it runs...
0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
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
 
pcelbaCommented:
Did you use Cross-tab Wizard?
0
 
pcelbaCommented:
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
 
pcelbaCommented:
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
 
jaymz69Author Commented:
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
 
pcelbaCommented:
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
 
jaymz69Author Commented:
Do I have to add the names or is it programiclly done?

0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
wow, prettu slick until it comes to the cross_tab part then. It takes like 10 minutes to do also...

0
 
jaymz69Author Commented:
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
 
jaymz69Author Commented:
I have the totals already it is just grouping the cust# and displaying the codes across
0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
I have not done that before, transform rows to coloumns.

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

google it up?
0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
Alot of records to group. I think like 7000

so know any good knowledge base on the row and column switch?
0
 
pcelbaCommented:
BTW, 7000 records is not many to group. The Cross-tab utility is probably slow.
0
 
jaymz69Author Commented:
so nothing I can do to the utility...?

Thanks for the documentations...
0
 
pcelbaCommented:
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
 
jaymz69Author Commented:
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
 
jaymz69Author Commented:
it is now taking it everytime for 7,896 records to .265 seconds to cross tab!!
0
 
pcelbaCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 22
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now