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 ;
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 )