Paradox Community

Items in pnews.paradox-programming

Subject:Re: is a Crosstab query possible?
Date:18 Jun 2020 09:49:34 -0400
From:"Thies Grimm" <thies.grimm@t-online.de>
Newsgroups:pnews.paradox-programming

hi, 
I found an example at	http://www.nknabe.dk/database/sql/code/SQL_crosstab_Paradox.htm

I made an example Script

looks nice, doesn't it? 
Thies

create a new script ant put the following code in it:

**********************************************************

; //	http://www.nknabe.dk/database/sql/code/SQL_crosstab_Paradox.htm
; //	Create Tables by Thies Grimm
; //	
proc cmExecuteSQLString(
				var db dataBase,
				stSQL	String
				) Logical
var
	itemSQL				SQL
endVar
	itemSQL.readFromString(stSQL) 
	return cmExecuteSQL(db, itemSQL) 
endProc
				
proc cmExecuteSQL(
				var db dataBase,
				var itemSQL	SQL
				) Logical
	if not itemSQL.executeSQL(db) then
		return False
	endIf
	return True
endProc

proc cmExecuteSQL2TC(
				var db dataBase,
				var itemSQL	SQL,
				var tc TCursor
				) Logical
	if not itemSQL.executeSQL(db, tc) then
		return False
	endIf
	return True
endProc

method run(var eventInfo Event)
var
	dynTblDef			dynArray[] String
	dynTblValues		dynArray[] String
	fs						FileSystem
	tbl					Table
	element				String
	ar						Array[] String
	si						Smallint
	db						DataBase
	itemSQL				SQL
	tc						TCursor
	tv						TableView
endVar

	db.open("priv")

; //	Table Definitions
	dynTblDef["__Assets.db"] 		 	= "AssetID INTEGER, AssetType INTEGER, AssetStatus
INTEGER, PRIMARY KEY(AssetID, AssetType)"
	dynTblDef["__AssetTypes.db"]  	= "ID INTEGER, TypeName CHAR(10), PRIMARY
KEY(ID)"
	dynTblDef["__AssetStatus.db"] 	= "ID INTEGER, StatusName CHAR(10), PRIMARY
KEY(ID)"

; //	Values
	dynTblValues["__AssetStatus.db"] = "1, 'Deployed'|2, 'Inventory'|3,'Shipped'"
	dynTblValues["__AssetTypes.db"] 	= "1, 'Desktop'|2, 'Laptop'|3,'Server'"
	dynTblValues["__Assets.db"] 		= "1010,1,2|1011,1,2|1012,1,2|1013,3,1|1014,1,3|1015,1,2|1016,1,2|1017,2,2|1018,1,1|1019,1,2"
+ 
												  "|1020,1,2|1021,1,2|1022,1,2|1023,3,1|1024,1,3|1025,1,2|1026,1,2|1027,2,2|1028,1,1|1029,1,2"
+ 
												  "|1030,1,2|1031,1,2|1032,1,2|1033,3,1|1034,1,3|1035,1,2|1036,1,2|1037,2,2|1038,1,1|1039,1,2"


	if not isDir(":priv:\\SQL") then
		fs.makeDir(":priv:\\SQL")
	endIf

; //	Create Tables
	forEach element in dynTblDef
		tbl.attach(element, db) 
		if tbl.isTable() then
			tbl.delete()
		endIf
		stSQL = "CREATE TABLE '" + element + "' (" + dynTblDef[element] + ")"
		if not cmExecuteSQLString(db, stSQL) then
			errorshow()
			return
		endIf
		dynTblValues[element].breakapart(ar, "|") 
		for si from 1 to ar.size()
			stSQL = "INSERT INTO '" + element + "' VALUES(" + ar[si] + ")"
			if not cmExecuteSQLString(db, stSQL) then
				errorshow()
				return
			endIf
		endFor
	endForEach


; //	First we will generate a de-coded Assets list for simpler access for
the successive queries:
; "/* This is the AssetsDecoded.sql extracting Assets decoded for TypeName
and Status */ " + 
	stSQL = "/* This is the AssetsDecoded.sql extracting Assets decoded for
TypeName and Status */ \n" + 
			"SELECT DISTINCT  T.TypeName , S.StatusName , A.AssetID \n" + 
			"FROM '__AssetTypes.db' T \n" + 
			" LEFT JOIN '__Assets.db' A \n" + 
			"	ON A.AssetType = T.ID \n" + 
			" JOIN '__AssetStatus.db' S \n" + 
			"	ON S.ID = A.AssetStatus	\n"
	itemSQL.ReadFromString(stSQL)
	itemSQL.writeSQL(":priv:SQL\\AssetsDecoded.sql") 


	stSQL = "/* This is the AssetsXTab0.sql generating the left hand categories
*/ \n" +
			"SELECT DISTINCT TypeName \n" + 
			"FROM '__AssetTypes.db' "
	itemSQL.ReadFromString(stSQL)
	itemSQL.writeSQL(":priv:SQL\\AssetsXTab0.sql") 

; //	alle StatusName ermitteln für Columns
	stSQL = "SELECT DISTINCT StatusName \n FROM '__AssetStatus.db'"
	itemSQL.ReadFromString(stSQL)
	if not cmExecuteSQL2TC(db, itemSQL, tc) then
		errorShow()
		return
	endIf	 

; //	je Spalte(Column) ein SQL Statement
	scan tc:
		st = tc."StatusName"
		stSQL = "/* This is the AssetsXTab" + st + ".sql generating the " + st
+ " aggregate column */ \n" + 
				"SELECT DISTINCT TypeName \n" + 
				" \t\t, COUNT(AssetID) AS " + st + "\n" + 
				" FROM SQL\\AssetsDecoded.sql \n" + 
				"WHERE \n" + 
				" StatusName = '" + st +"' \n" + 
				"GROUP BY TypeName "
		itemSQL.ReadFromString(stSQL)
		itemSQL.writeSQL(":priv:SQL\\AssetsXTab" + st + ".sql") 		
	endScan

; // ein zusammenfassendes Statment für Total
	stSQL = "/* This is the AssetsXTabDTotal.sql generating the Totals aggregate
column */ \n" + 
			"SELECT DISTINCT TypeName \n" + 
			" \t\t, COUNT(AssetID) AS Total \n" + 
			" FROM SQL\\AssetsDecoded.sql \n" + 
			"GROUP BY TypeName "
	itemSQL.ReadFromString(stSQL)
	itemSQL.writeSQL(":priv:SQL\\AssetsXTabDTotal.sql") 


; //	And to run the final crosstab query just execute this:
	stSQL = 
		; //	"/* This is AssetsXTabFinal.sql calling all other SQLs and generating
final crosstab */ " + 
		" SELECT DISTINCT a.TypeName \n" 
		scan tc: 
			st = tc."StatusName"
			stSQL = stSQL + " \t\t, " + st + "\n"
		endScan
		stSQL = stSQL + " \t\t, Total \n FROM SQL\\AssetsXTab0.sql a \n"
		scan tc: 
			st = tc."StatusName"
			stSQL = stSQL + "   LEFT JOIN SQL\\AssetsXTab" + st + ".sql x" + st +
" ON x" + st + ".TypeName = a.TypeName" + "\n"
		endScan
		stSQL = stSQL +"   LEFT JOIN SQL\\AssetsXTabDTotal.sql xTotal ON xTotal.TypeName
= a.TypeName"
	itemSQL.readFromString(stSQL)
	itemSQL.writeSQL(":priv:SQL\\AssetsFinal.sql") 
			
	if not cmExecuteSQL2TC(db, itemSQL, tc) then
		errorShow()
		return
	endIf

	tc.instantiateView(":priv:__tblCrosstabAsset.db") 
	tc.close()

	tv.open(":priv:__tblCrosstabAsset.db") 


endMethod


**********************************************************


Anders Jonsson <andersREMOVETHIS@jonssondata.se> wrote:
>
>
>> But surely ObjectPAL does CROSSTAB (like pdoxDOS did)?
>
>It does but not as easily as in DOS!
>
>It was a long time since I did this but if I remember correctly you need

>to first create a Crosstab in a a form and then you can use the 
>AcctionDataCommand DataSaveCrosstab for the uibojcet which will create a

>table CROSSTAB.DB in PRIV.
>
>I used something like this:
>
>Open a hidden form with the crosstab
>
>Issue uiCrossTab.action(DataSaveCrosstab)
>
>Close the hidden form
>
>Work with the Crosstab table.
>
>There are probably a better way but that was how I solved it a long time

>ago...
>
>


Copyright © 2004 thedbcommunity.com