How can I find out which DB script these are?
INSERT INTO accnt
SELECT Concat('EBB','-','@cntryCode', '-',Trim(mast.masterno)) AS acct_srogt_id ,
Trim(mast.masterno) AS acct_host_num ,
mast.statusflag AS acct_sts_type_cd ,
'' AS prvsn_sts_type_cd ,
'' AS acct_auto_prvsn_cd ,
'' AS acct_ptfol_ctry_cd ,
mast.armcode AS arm_cd ,
'' AS acct_cury_cd ,
'' AS base_cury_cd ,
'' AS acct_blg_type_cd ,
'' AS acct_int_type_cd ,
'' AS acct_int_rt ,
'' AS anl_int_sprd_rt ,
'' AS eff_int_rt ,
masteropendate AS acct_open_dt ,
closingdate AS acct_clse_dt ,
'' AS acct_appr_dt ,
'' AS acct_stm_cyc_day ,
'' AS acct_div_cd ,
'' AS effective_start_date ,
'' AS acct_sts_type_desc ,
Concat('EBB','-','@cntryCode','-',Trim(arm.bankid)) AS arm_emp_srogt_id ,
'EBB_ACCT_01' AS prcs_id ,
'' AS acct_closr_rsn ,
'' AS prvsn_sts_type_desc ,
'' AS acct_acq_chnl_meth ,
mast.segmentcode AS acct_segmt_cd ,
seg.NAME AS acct_segmt_desc ,
'' AS intrnl_inst_clas_cd ,
'' AS intrnl_instl_clas_cd_desc ,
arm.NAME AS arm_nm ,
'' AS acct_last_cr_tran_dt ,
'' AS acct_book_loc ,
accttype AS acct_cat_cd ,
'' AS acct_cat_desc ,
'' AS busn_cat_cd ,
'' AS busn_cat_cd_desc ,
'' AS acct_int_type_cd_desc ,
'' AS actg_meth ,
'' AS acct_sts_chg_dt ,
'' AS prvsn_sts_chg_dt ,
'' AS acct_shrt_nm ,
'' AS intnl_bank_acct_num ,
'' AS deal_lien_num ,
'' AS prod_cd ,
'' AS lend_type ,
'' AS lend_type_desc ,
'' AS orig_maturty_dt ,
'' AS maturty_dt ,
'' AS ln_prps_cd ,
'' AS ln_prps_cd_desc ,
'' AS ptfol_id_num ,
'' AS ptfol_desc ,
'' AS ln_bss_cd ,
'' AS revn_chrg_cd ,
'' AS revn_chrg_cd_desc ,
'' AS ln_type_cd ,
'' AS ln_type_desc ,
'' AS ln_scty_cd ,
'' AS ln_clas ,
'' AS ln_appl_dclne_cd ,
'' AS ln_risk_clas_cd ,
'' AS drp_acct_fl ,
'' AS impairment_fl ,
'' AS stm_cd ,
'' AS acct_stm_addressing_titl ,
'' AS acct_drmt_dt,
Concat('EBBS','-','@cntryCode','-',Trim(rel.relationshipno)) AS prty_srogt_id,
'' AS drp_acct_num,
Concat('EBB','-','@cntryCode','-',Trim(mast.branchcode)) AS brnch_srogt_id ,
t.riskcode AS risk_cd,
risk.description AS risk_cd_desc
FROM @PREFIX2_MAST MAST
LEFT OUTER JOIN @PREFIX2_ARM ARM
ON mast.armcode=arm.armcode
LEFT OUTER JOIN @PREFIX2_SEG SEG
ON mast.segmentcode=seg.segmentcode
LEFT JOIN @PREFIX2_MASTREL MSTREL
ON mstrel.masterno=mast.masterno
AND mstrel.primaryflag='Y'
LEFT JOIN @PREFIX2_REL REL
ON mstrel.relationshipno=rel.relationshipno
AND rel.relationshiptype='C'
LEFT JOIN
(
SELECT riskcode,
base.masterno
FROM (
SELECT riskcode,
masterno ,
Row_number() OVER (partition BY masterno ORDER BY seqno DESC) AS max_row
FROM @PREFIX2_RSKIND RSKND) base
WHERE base.max_row=1) t
ON mast.masterno=t.masterno
LEFT JOIN @PREFIX2_RISK RISK
How can I find out which DB script these are please?
1 answer
See also questions close to this topic
-
Change the Colour of a value in a SQL Server DB Mail
I have created a DB Mail alert that sends out a HTML formatted table showing the pass or fail status of my SQL agents.
The alert works well, but the user has request an additional change where they want conditional formating on Pass/Fail status of the data, where failed status is to be highlighted RED and pass status to be highlighted GREEN.
Is this possible to achieve to in SQL using DB Mail and HTML formatting? A few forums have stated that JavaScript may need to be used but I'm not sure if its possible to even us JS in SQL.
My code is below:
---------------------------------------------------------------------------- ----------------------------------- Declare Variables ---------------------- ---------------------------------------------------------------------------- Declare @email_body nvarchar(max) Declare @email_profile_name nvarchar(max) Declare @email_recipients nvarchar(max) Declare @email_subject nvarchar(max) Declare @tableHTML nvarchar(MAX) = '' Declare @Style nvarchar(MAX) = '' Declare @Textdate nvarchar(max) DECLARE @crlf nvarchar = CHAR(13)+CHAR(10) Declare @Server nvarchar(max) ---------------------------------------------------------------------------- --------------------------- Set Varriables (That should not change) -------- ---------------------------------------------------------------------------- set @Server = @@SERVERNAME set @Textdate = cast(getdate() as date) Set @email_subject = @Textdate + ': Morning SQL Server Checks' ---------------------------------------------------------------------------- ----------------------------------- Set Varriables (User Input) ------------ ---------------------------------------------------------------------------- --Add other Email addresses as needed. Seperate with Semi-colon (;) set @email_recipients = 'Email@Email.com' ---------------------------------------------------------------------------- ----------------------------------- Create Temp Tables --------------------- ---------------------------------------------------------------------------- IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results Create table #Results ( [Server] nvarchar(max), [Entity] nvarchar(max), [Status] nvarchar(max), [Message] nvarchar(max), [Type] nvarchar(max)) ---------------------------------------------------------------------------- ----------------------------------- Generate Data -------------------------- ---------------------------------------------------------------------------- --Gets SQL Agent Results (Script Derived from here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/534cc5be-0021-4766- 9eef-92fea819e2e3/script-to-get-sql-server-agent-job-schedule-and-the-last- run-status?forum=sqldatabaseengine) Insert into #Results SELECT @@SERVERNAME as 'Server Name' ,J.Name AS 'Job Name' --,CASE J.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END as 'Job Enabled' ,CASE WHEN LASTRUN.run_status = 0 THEN 'Failed' WHEN LASTRUN.run_status = 1 THEN 'Succeeded' WHEN LASTRUN.run_status = 2 THEN 'Retry' WHEN LASTRUN.run_status = 3 THEN 'Cancelled' ELSE 'Unknown' END as 'Last Run Status' ,LASTRUN.message as 'Last Run Message' ,'SQL Agent Jobs' as 'Type' FROM msdb.dbo.sysjobs J LEFT OUTER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id LEFT OUTER JOIN msdb.dbo.sysschedules S ON JS.schedule_id = S.schedule_id LEFT OUTER JOIN (SELECT J1.job_id ,J1.RUN_DURATION ,J1.run_date ,J1.run_time ,J1.message ,J1.run_status FROM msdb.dbo.sysjobhistory J1 WHERE instance_id = (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory J2 WHERE J2.job_id = J1.job_id)) LASTRUN ON J.job_id = LASTRUN.job_id where J.Enabled = 1 --Only check for agents that have been enabled -------------------------------------------------------------------------- ----------------------------------- Generate Email ------------------------- ---------------------------------------------------------------------------- --Set Style (CSS) SET @Style += +N'<style type="text/css">' + N'.tg {border- collapse:collapse;border-spacing:0;border-color:#aaa;}' + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}' + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#f38630;}' + N'.tg .tg-9ajh{font-weight:bold;background-color:#68cbd0}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}' + N'</style>'; --Set Email Output (HTML Layout) Set @tableHTML += @Style + @tableHTML + N'<H1 style="color:Blue;">' + @Textdate + ': Morning SQL Server Checks</H1>' + N'<H2>' + @Server + '</H2>' + N'<H3>Databases</H3>' + + N'<table class="tg">' --DEFINE TABLE -- Define Headers for Database Check + N'<tr>' + N'<H3>SQL Agents</H3>' + + N'<table class="tg">' --DEFINE TABLE + N'<tr>' + N'<td class="tg-9ajh">Server</td>' + N'<td class="tg-9ajh">Entity</td>' + N'<td class="tg-9ajh">Status</td>' + N'<td class="tg-9ajh">Message</td>' + N'<td class="tg-9ajh">Type</td></tr>' -- Define data for SQL Agent and cast to xml + Cast(( Select td = isnull([Server],'###') ,'' ,td = isnull([Entity],'###') ,'' ,td = isnull([Status],'###') ,'' ,td = isnull([Message],'###') ,'' ,td = isnull([Type],'###') ,'' from #Results where [type] = 'SQL Agent Jobs' FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>'; -- Send the Email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Outlook Support Profile', @recipients = @email_recipients, @body = @tableHTML, @body_format = 'HTML', @subject = @email_subject ---------------------------------------------------------------------------- ----------------------------------- Cleanup -------------------------------- ---------------------------------------------------------------------------- drop table #ResultsI hope the code is clear enough and makes sense.
Thanks you Anthony
-
How to find matching columns in multiple tables in a sql database?
How to find matching columns in multiple tables in a sql database? I have a sql database with over 30 tables, is there a way to filter and/or produce a list of the common columns among the tables rather than going through each manually. Maybe a code?
-
SQL identifying Union
I keep getting a Select error that a reserved word is either misspelled, or missing or the punctuation is incorrect but I cannot locate the source of this error. Some help is needed.
SELECT Style, Short, Color, [Logo 1], Forecast, COMMIT, Variance INTO ForecastvsCommitCombined FROM (SELECT [FanWalForecastVsCommit].Style, [FanWalForecastVsCommit].Short, [FanWalForecastVsCommit].Color, [FanWalForecastVsCommit].[Logo 1], [FanWalForecastVsCommit].Forecast, [FanWalForecastVsCommit].Commit, [FanWalForecastVsCommit].Variance FROM [FanWalForecastVsCommit] UNION ALL SELECT [MeijerForecastVsCommit].Style, [MeijerForecastVsCommit].Short, [MeijerForecastVsCommit].Color, [MeijerForecastVsCommit].[Logo 1], AS [MeijerForecastVsCommit].Forecast, [MeijerForecastVsCommit].Commit, [MeijerForecastVsCommit].Variance FROM [MeijerForecastVsCommit] UNION ALL SELECT [MidTierForecastVsCommit].Style, [MidTierForecastVsCommit].Short, [MidTierForecastVsCommit].Color, [MidTierForecastVsCommit].[Logo 1], [MidTierForecastVsCommit].Forecast, [MidTierForecastVsCommit].Commit, [MidTierForecastVsCommit].Variance FROM [MidTierForecastVsCommit] UNION ALL SELECT [SamsClubForecastVsCommit].Style, [SamsClubForecastVsCommit].Short, [SamsClubForecastVsCommit].Color, [SamsClubForecastVsCommit].[Logo 1], [SamsClubForecastVsCommit].Forecast, [SamsClubForecastVsCommit].Commit, [SamsClubForecastVsCommit].Variance FROM [SamsClubForecastVsCommit] UNION ALL SELECT [TargetForecastvsCommit].Style, [TargetForecastvsCommit].Short, [TargetForecastvsCommit].Color, [TargetForecastvsCommit].[Logo 1], [TargetForecastvsCommit].Forecast, [TargetForecastvsCommit].Commit, [TargetForecastvsCommit].Variance FROM [TargetForecastvsCommit] UNION ALL SELECT [WalmartForecastVsCommit].Style, [WalmartForecastVsCommit].Short, [WalmartForecastVsCommit].Color, [WalmartForecastVsCommit].[Logo 1], [WalmartForecastVsCommit].Forecast, [WalmartForecastVsCommit].Commit, [WalmartForecastVsCommit].Variance FROM [WalmartForecastVsCommit]); -
Is it more efficient to store id values in dictionary or re-query database
I have a script that repopulates a large database and would generate id values from other tables when needed.
Example would be recording order information when given customer names only. I would check to see if the customer exists in a
CUSTOMERtable. If so,SELECTquery to get hisIDand insert the new record. Else I would create a newCUSTOMERentry and get theLast_Insert_Id().Since these values duplicate a lot and I don't always need to generate a new
ID-- Would it be better for me to store theID => CUSTOMERrelationship as a dictionary that gets checked before reaching the database or should I make the script constantly requery the database? I'm thinking the first approach is the best approach since it reduces load on the database, but I'm concerned for how large the ID Dictionary would get and the impacts of that.The script is running on the same box as the database, so network delays are negligible.
-
How to remove duplicate space from the string using t-sql
I have a string like this ' Hello <> world '. I am using below code to remove duplicate space from the string. But it removes
<>from the string.SELECT ' Hello <> world ' AS Name INTO #Temp; SELECT REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(Name)), ' ', '<>'), '><', ''), '<>', ' ') AS CleanName FROM #Temp;Is there any alternative ?
-
MySQL error 1036: table is read only using phpmyadmin
I'm trying to import a sql db using phpmyadmin it gives me
#1036 - Table 'wo_nf3_actions' is read onlyI read somewhere that changing table storage engine might work but still I'm getting the same error. Any help would be really appreciated. Thanks in advance
-
Visual Studio 2017 - Data Connections - Add Oracle DB
I am using Visual Studio 2017 Enterprise (and Pro) (latest update 15.9.4) with Oracle Data Tools (ODT 18.3.0.0) installed. I am trying to connect to an Oracle DB (using ODP.NET Managed Driver) in the Data Connections under Server Explorer but it keeps throwing an Oracle Communication error. I have tried using TNS entry and EZ Connect. But no matter what I do, I cannot connect to the DB. To verify that I can connect to the DB from my machine, I used SQL Developer and was able to connect and see all the DB objects. I also was able to ping the DB using TNSPing.
Here is the error I get:
I tried to change the port number to see if VS can connect to the server at least and it seems that it can. It throws a ORA error as it should, so I suspect there is something else going on here when it tries to connect.
And I know that the DB can connect to VS 2017 since I am able to connect to it from another machine which has all the same tools installed.
Any help would be greatly appreciated!
-
Use Oracle's BITAND with a multiple-value parameter
Trying to find an elegant way to be able to filter a query, using
BITANDwhere the values to beanded are supplied by a multiple-value parameter.Testing data:
WITH patient as ( select 1 patient_id, 'foo' patient_name from dual union all select 2 patient_id, 'bar' patient_name from dual union all select 3 patient_id, 'baz' patient_name from dual union all select 4 patient_id, 'zoo' patient_name from dual ) , -- each organ is a power of 2 organ as ( select 2 organ_id, 'Lung' organ_name from dual union all select 4 organ_id, 'Pancreas' organ_name from dual union all select 8 organ_id, 'Liver' organ_name from dual union all select 16 organ_id, 'Kidney' organ_name from dual ) , patient_organ as ( -- patient with a multi-organ transplant select 1 patient_id, 4 organ_id from dual union all select 1 patient_id, 16 organ_id from dual union all -- patient with a single-organ transplant select 2 patient_id, 4 organ_id from dual union all -- patient with a multi-organ transplant select 3 patient_id, 8 organ_id from dual union all select 3 patient_id, 16 organ_id from dual union all -- patient with a single-organ transplant select 4 patient_id, 2 organ_id from dual )This query:
select p.patient_id, p.patient_name ,po.bits,po.organs from patient p inner join ( SELECT patient_id, sum(organ_id) AS BITS ,listagg(organ, '; ') within group (order by organ_id) ORGANS FROM ( SELECT patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ FROM patient_organ po INNER JOIN organ o ON po.organ_id = o.organ_id ) GROUP BY patient_id ) po on p.patient_id=po.patient_idGenerates the desired data set; the multiple organs (e.g.
Pancreas [4]; Kidney [16]) are displayed:PATIENT_ID, PATIENT_NAME, BITS, ORGANS 1 foo 20 Pancreas [4]; Kidney [16] 2 bar 4 Pancreas [4] 3 baz 24 Liver [8]; Kidney [16] 4 zoo 2 Lung [2]I want to be able to supply a parameter value of
4,8and get these results:PATIENT_ID, PATIENT_NAME, BITS, ORGANS 1 foo 20 Pancreas [4]; Kidney [16] 2 bar 4 Pancreas [4] 3 baz 24 Liver [8]; Kidney [16]If I have a single value (simulated with
:organ=4), I can useBITANDand get the multi-organ values:select p.patient_id, p.patient_name ,po.bits,po.organs from patient p inner join ( SELECT patient_id, sum(organ_id) AS BITS ,listagg(organ, '; ') within group (order by organ_id) ORGANS FROM ( SELECT patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ FROM patient_organ po INNER JOIN organ o ON po.organ_id = o.organ_id ) GROUP BY patient_id ) po on p.patient_id=po.patient_id WHERE bitand(bits,:organ)=:organMulti-organs preserved:
PATIENT_ID, PATIENT_NAME, BITS, ORGANS 1 foo 20 Pancreas [4]; Kidney [16] 2 bar 4 Pancreas [4]I can use a multiple-value parameter (simulated by
&organs=4,8):select p.patient_id, p.patient_name ,po.bits,po.organs from patient p inner join ( SELECT patient_id, sum(organ_id) AS BITS ,listagg(organ, '; ') within group (order by organ_id) ORGANS FROM ( SELECT patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ FROM patient_organ po INNER JOIN organ o ON po.organ_id = o.organ_id WHERE po.organ_id IN (&organs) ) GROUP BY patient_id ) po on p.patient_id=po.patient_idBut this loses the multi-organ results:
PATIENT_ID, PATIENT_NAME, BITS, ORGANS 1 foo 4 Pancreas [4] 2 bar 4 Pancreas [4] 3 baz 8 Liver [8]Ideally, I'd be able to use the
BITANDfunction with theINstatement, but this isn't syntactically valid.Is there another non-procedural approach?
-
EntityFramework 6 - Entity Data Model Wizard - Add Oracle DB
I am trying to connect to an Oracle DB to reverse engineer it to use Entity Framework 6 (DB first). I am using VS 2017 (15.9.4 - latest update) Pro on a Windows 10 machine. I have verified that I can connect to the DB using SQL Developer and using TNSPing. I have also installed ODT 18.3.0.0 for VS 2017. The project also has the Oracle ManagedDataAccess Nuget package (and it's dependencies) installed as shown below:
Nuget packages and Entity Data Model Wizard
When I try to add the connection to the DB via the Entity Data Model Wizard and test it, it keeps throwing me a Oracle Communication error shown below:
I have tried using the TNSName files from the Progam Files directory (with the appropriate aliases added) and also using EZ Connect. Both times it shows the same error. I also tried changing the port number to see if it is actually a connection string issue as the error mentions, but it then throws an actual ORA error as below:
Any help would be greatly appreciated. I know this connection works since I used a different machine (older Windows 7 machine) and was able to see the tables (and other DB objects) in VS 2017 connected to the same DB. I also tried connecting to other Oracle DBs, and it keeps throwing the same error.
Thanks!
-
Column names with numbers in a file and creating hive table
I am trying to create a table in hive. need help with it.
Sample code: CREATE EXTERNAL TABLE table1( id STRING, name STRING, "12489738" STRING, "12492628" STRING, "12492633" STRING, "12492638" STRING, "12492655" STRING, "12492659" STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION "" tblproperties ("skip.header.line.count"="1");
But it throws error:
Error info: NoViableAltException(320@[]) at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11633) at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:49892) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:40082) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:38241) at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:6726) at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:4122) at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1786) at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1152) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:171) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:447) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:330) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1233) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1274) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1170) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1160) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:217) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:169) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:380) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:740) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:685) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148) FAILED: ParseException line 4:0 cannot recognize input near '"12489738"' 'STRING' ',' in column specification
-
Hive: create database fail with 'database already exists'
I have a test suite which runs several spark unit tests. Each of these tests shares the same underlying spark context. During the running of these tests I check if a db exists and if not I create it:
def dbExists(db: String) = spark.sql(s"show databases like '$db'").count > 0 if (!dbExists(db)) spark.sql(s"create database $db")For some reasons, one of the tests is failing. Debugging I saw that for a certain db
dbExists(db)returns false and the creation command fails withERROR RetryingHMSHandler:159 - AlreadyExistsException(message:Database db already exists) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:891) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)Everytime a test start, I clean the environment running
drop database db cascadefor each db that is not the default one. The only explanation I can give is that some corrupted metadata is in the catalogue and spark sql thinks that the db exists, while it is not anymore.The problem happens also inside a container with a fresh git clone of the project, meaning that it is not a previous run of the application that might pollute environment.
I run with hive support enabled.
-
Append/merge tables with different structure into one table in Hive?
I have couple of input tables as shown in the picture. I want to create an Output table that includes columns from both the input tables as shown in the picture. No of columns and column names in the input tables can change at anypoint. I want to create a dynamic process that reads the input tables and create output table with all the columns. I am using python. Has anyone done this before? Any help is much appreciated.
-
Extract text from <> tags searching a certain pattern
I have this pattern. I want to extract HISTORY0 pattern for STATUS, It's WAS value (in the tag ) and IS value (in the tag ) and segregate into new columns:
HISTORY0:<NAME>WAS<ABC>IS<XYZ> HISTORY1:<NOMBRE>WAS<ABC>IS<XYZ>HISTORY0:<ID>WAS<123>IS<456> HISTORY1:<ID>WAS<123>IS<456>HISTORY0:<STATUS>WAS<SINGLE>IS<MARRIED> HISTORY1:<ESTADO>WAS<SOLTERO>IS<CASADO>I tried using REGEX but it's being very complicated.. Please help.
-
java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.44] [Error 1338] [SQLState HY000] for a python etl code
java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.44] [Error 1338] [SQLState HY000] for a python etl code .the code runs fine for a while but now i am getting the error on daily basis. Also it says Details of the failure can be found in the exception chain that is accessible with getNextException. How to enable that?
-
Teradata Sub-string Before and After certain character
I am looking for a way to extract text that is before and after a certain character in Teradata. For example, I have a column that is consistent except for the word in the middle - which I want to extract.
I have these two examples:
- "Customer Activated Account DD: ABCD, ON DATE XXXX"
- "Customer Activated Account DD: EFGH, ON DATE XXXX"
I need the text that exists right after ":" and before "," - in this case ABCD or EFGH. So far I have
SUBSTRING (column FROM POSITION ('DD: ' IN column) +4)but that returns everything after the ":" likeABCD, ON DATE XXXX.
