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...
>
>
|