Subject: | Re: is a Crosstab query possible?
| Date: | Thu, 18 Jun 2020 10:27:09 -0400
| From: | Kevin Baker <shcsbaker@gmail.com>
| Newsgroups: | pnews.paradox-programming
|
WOW! Great info! Thanks!!
On 6/18/2020 9:49 AM, Thies Grimm wrote:
> 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...
>>
>>
>
|