Oracle Database Discussions

User_<whatever> wrote:

create table ##input1
(Billid int,
Ctextid int,
info JSON,
user varchar(30),
created datetime2
)

Posting CREATE TABLE and INSERT statements is great.
But it is not so great when they have obvious errors, which you could catch easily if you would test your statements yourself, in an Oracle database, before posting them here. (Please don't say you did - that would be a lie.)
USER is a reserved keyword in Oracle; the db will not let you use it as a column name, unless you enclose it in double-quotes. (However, that would be a very poor practice: just don't use keywords as identifiers and you won't get into all sorts of problems later.)
Worse, there is no DATETIME2 data type in Oracle. There is no way you tested your CREATE TABLE on an Oracle database and it didn't throw an error at least for that reason. I don't suppose you created a user-defined data type by that name, did you?
At this point in trying to help you, I stopped. If you can't even test your code before you post it (which would take a few seconds at most), how can I trust that the rest is OK? Even if I fix the data type myself, and I am able to create the table, and I understand your question, how do I know that the way you asked the question is correct? I fear that I may work on your problem, and later you will come back and say "sorry, I misspoke; in fact my problem is...." Do you see how that could be a problem?

@mathguy : Please accept my apologies!..I recently moved from using SQL Server to Oracle SQL ..Also I am using a BenchSQL interface ..But that is not a reason..I will figure out the way to test the oracle DDL soon...Meanwhile please find below the changes to my DDL..
Input table

create table ##input1

(Billid int,

Ctextid int,

info IS JSON

cuser varchar(30),

created timestamp

)

insert into ##input1 values

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

}

]','amt1','08/03/2020 17:07'),"

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""siluka "",

""dCode"": ""887756"",

""CodeId"": ""883773"",

""messages"": [

""""

],

""Number"": 3

}

]','all1','08/03/2020 21:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90774"",

""CodeId"": ""7800034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk1','08/04/2020 18:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90974"",

""CodeId"": ""78034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk2','08/05/2020 18:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z003345"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""IMPACTED BILATERAL "",

""dCode"": ""U8923"",

""CodeId"": ""7324"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""IMMUNIZATION "",

""dCode"": ""H678"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 4

},

{

""description"": ""CHILDHOOD FEVER "",

""dCode"": ""Y98620"",

""CodeId"": ""55467"",

""messages"": [

""""

],

""Number"": 5

}

]','ec1','08/07/2020 6:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z8897645"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

}, {

""description"": ""IMMUNIZATION "",

""dCode"": ""H67891"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": ""Kirolo substaniss FEVER "",

""dCode"": ""J18907"",

""CodeId"": ""66712"",

""messages"": [

""""

],

""Number"": 4

}','ec2','08/07/2020 17:07')

Output table

create table ##output1

(billid int,

ctextid int,

codername varchar(30),

correctedcode varchar(100),

deletedcode varchar(100),

addedcode varchar(100)

)

insert into ##output1 values

('2132','91156','amt1','','','887756'),

('5678','99344','jk1','90774','',''),

('7789','60045','ec1','Z003345,H678','U8923,Y98620','J18907')