All Forums Database
Ince 31 posts Joined 05/08
17 Jul 2008
Counting Record From Source File

HiI have a query below from the DBC database identifying the databasename,tablename, and column.select DatabaseName,TableName,ColumnNameFrom dbc.columnswhere DatabaseName = 'testeiw'and ColumnName = 'SB_Source_System_Cd'order by TableName; I need to find out how many records have been loaded into the database called testeiw, by using the column name 'SB_Source_System_Cd', does anyone knows how to do this?I can get the results by running this query below, the problem is that I have to run this query for each tablename I want to find the records loaded for SB_Source_System_Cd, the SB_Source_System_Cd represent my source filesselect SB_Source_System_Cd ,count(*) from testeiw.Branding group by 1;Output required is as follows:TableName SourceCode RecordLoadedBranding 1 52000772 2 608169 3 155223 10 11629083 12 11315323 20 1006372 24 234777 36 116502 50 201191 64 144893 91 744 92 339 94 503051 106 11779684TableName SourceCode RecordLoadedLeaf 1 32632083 10 1141374 12 24908995 20 1568 50 8340 91 144115Please assist if you have any better options of doing this.Regards,Chauke

j355ga 100 posts Joined 12/05
18 Jul 2008

Since you have many tables with the same column name, this is the only way you can count the rows in a table. You could review the load logs (if there are any) and extract the rows loaded.

Jeff

krisp1234 7 posts Joined 07/08
29 Jul 2008

even I am looking for same query which can get records counts on list of tables which I have tablulated in a table. query u have just showing record counts as 1 for all tablesplease let me know if u get the right answerthanks

Ince 31 posts Joined 05/08
30 Jul 2008

I have tried to use the options below, but seems to be taking long to return something, although it works,you have to use the UNION ALL to retrieve all the required columns per table.SELECT TableName ,MAX(CARD) Card , MAX(HOMELOAN) Homeloan, MAX(BRANCHACCOUNTING) "BRANCH ACCOUNTING",MAX(STANLIB) STANLIBFROM (SELECT TableName, CASE SB_Source_System_Cd when 10 then '10' ELSE NULLEND "CARD",CASE SB_Source_System_Cd when 12 then '12' ELSE NULL END HOMELOAN,CASE SB_Source_System_Cd when 60 then '60' ELSE NULL END BRANCHACCOUNTING,CASE SB_Source_System_Cd when 2 then '2' ELSE NULL END STANLIBFROM(SELECT DISTINCT TRIM(CAST('LOAN_ACCOUNT' AS VARCHAR(150))) as TableName, SB_Source_System_Cd ,COUNT(*) as counter FROM TESTEIW.LOAN_ACCOUNT GROUP by 1,2 )

You must sign in to leave a comment.