|
|
|
Forum Member
 
Group: Forum Members
Last Login: Today @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
Hi
I have a query below from the DBC database identifying the databasename,tablename, and column.
select DatabaseName
,TableName
,ColumnName
From dbc.columns
where 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 files
select
SB_Source_System_Cd ,count(*)
from testeiw.Branding
group by 1
;
Output required is as follows:
TableName SourceCode RecordLoaded
Branding 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 11779684
TableName SourceCode RecordLoaded
Leaf 1 32632083
10 1141374
12 24908995
20 1568
50 8340
91 144115
Please assist if you have any better options of doing this.
Regards,
Chauke
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/25/2008 1:20:39 PM
Posts: 91,
Visits: 119
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/5/2008 10:50:05 AM
Posts: 7,
Visits: 25
|
|
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 tables
please let me know if u get the right answer
thanks
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Today @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
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) STANLIB
FROM
(
SELECT TableName
,
CASE SB_Source_System_Cd
when 10 then '10'
ELSE
NULL
END "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 STANLIB
FROM
(
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 )
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|