how to find the difference between two tables in oracle?
I have two tables named
B where most of the columns are different and the common column is
name. Now I want to find the records which are extra in table
A based on the common field
name. How to get these?
One more thing here we have to check is a few names in table
B have words like
'Test_' on the beginning which we have to trim. Say for example
A is having
name ='Div_text_col_tar' and
B is having
name ='dummy_Div_text_col_tar' which actually the same. So we have to replace 'dummy_' and 'Test_' from the beginning of names. How to do it?
I tried like shown below without any luck:
SELECT * FROM A t1 WHERE NOT EXISTS (SELECT 1 FROM B t2 WHERE t1.name = REGEXP_SUBSTR(t2.name,'[^-dummy_|-Test_]+',1,1) ) AND t1.status =100 AND t1.floor IN ('1','2','3')
I think I would go for:
SELECT t1.* FROM A t1 WHERE NOT EXISTS (SELECT 1 FROM B t2 WHERE t2.name IN (t1.name, 'dummy_' || t1.name, 'Test_' || t1.name) ) AND t1.status = 100 AND t1.floor IN (1, 2, 3); -- presumably, these are numbers, not strings
This seems simpler and easier to follow than using regular expressions.
See also questions close to this topic
Active Directory: Duplicate group membership found
I am building a process to extract all users of type contact, computer, user, and group into SQL Server. As part of that process, I am retrieving all group members and inputting that information into a separate table, which has a primary key on the date, memberObjectGuid, and groupObjectGuid. Therefor, I expect a group to only have one instance of a given user. However, I received a primary key violation while loading the data, which shows two instances of the same memberObjectGuid trying to be loaded into the same groupObjectGuid.
Is this allowed? If not, what can cause it (how do I diagnose it) how can I fix it?
in the oracle how to define a foreign key in the nested table?
i need to add a foreign key refeences to the nested table how can i do that?
here is my oracle code here i my issue line is starting with "##" tag while executing this it says an error "missing keyword"
what wrong with the following code?
CREATE TYPE Doctor_type AS OBJECT( regNo char(100), name varchar2(50), specialization varchar2(50) ) / CREATE TYPE HospitalVisits_type AS OBJECT( visitNo char(10), hosCharge char(10), makedate date, Doctor REF Doctor_type ) not final / CREATE TYPE Make_type AS TABLE OF HospitalVisits_type; / CREATE TYPE Patient_type AS OBJECT( id char(10), name varchar2(50), dateofBirth date, phone varchar2(10), dependents Make_type ) / CREATE TYPE Admission_type UNDER HospitalVisits_type ( dischDate date ) / CREATE TYPE OPDVisit_type UNDER HospitalVisits_type( docCharge char(10) ) / CREATE TABLE Doctor OF Doctor_type(regNo primary key ) / CREATE TABLE Patient OF Patient_type(id primary key)NESTED TABLE dependents STORE AS HospitalVisits_Table / ##ALTER TABLE HospitalVisits_Table ADD CONSTRAINT Doc_FK FOREIGN KEY(Doctor) REFERENCES Doctor(regNo) /
Efficient of SQL Update Query
How to make this syntax below more efficient because i have to update more than 20 fields for one record?
UPDATE TRANS SET A = @XA WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and A <> @XA UPDATE TRANS SET B = @XB WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and B <> @XB UPDATE TRANS SET C = @XC WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and C <> @XC UPDATE TRANS SET D = @XD WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and D <> @XD UPDATE TRANS SET E = @XE WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and E <> @XE
Showing video from service application to user
This is a followup question to this one: Video frames send through Named Pipe very slow
I think I might be going about this all wrong. The back-end video processing library that I'm using is written in .Net and has a number of functions that rely on Windows Forms. When used as a single desktop application the library's code is extremely fast and efficient. So I'm not worried about any performance on that end.
What I've been trying to do is separate the processing code from the UI. Technically I've succeeded, as I have an application that functions without a UI and takes command line parameters. The issue that I'm having is efficiently communicating between the service and UI. In a followup to my previous question linked to above, the communication between the service and UI is very sluggish, as least for displaying video.
var sw = new Stopwatch(); sw.Start(); // ** code to convert image to byte array ** // Debug.Write(Environment.NewLine + sw.ElapsedMilliseconds + "\t"); sw.Reset(); sw.Start(); serverPipe.WriteBytes(byteImage); Debug.Write(Environment.NewLine + sw.ElapsedMilliseconds + "\t");
Here's the debug info (I have the FPS counter elsewhere, it shows the processing FPS). The first number is the time in milliseconds to convert the image to a byte array and the second number is the time to send the byte array through the named pipe:
51 131 52 127 43 131 FPS: 31 81 144 40 119 49 154 53 140 FPS: 30 72 130 42 130 56 137 48 131 FPS: 30 70 149 34 137 72 144 44 126 FPS: 29
This roughly translates to about 2 to 4 FPS being shown in the UI application. I'm running the above code in its own thread and I block any new threads from being created while one is already active (the thread terminates as soon as the image is sent, this prevents any possibility of frames arriving out of order in the UI).
I'm thinking about 3 possible solutions:
- Improve the efficiency of the named pipe code (unsure if possible)
- Create a single application that can satisfy both a service (running as SYSTEM user) and a front-end UI when needed
- Create an internal web component in the service to serve up a local page.
I'm not sure number 2 is possible as the application would need to run primarily as a system service that starts with the OS. It's a service that runs all the time regardless if a user is logged on. I'm not aware of a way to make a UI available to logged on users. In the past, I had accomplished something similar by relying on Session 0, which is not an option anymore (and very bad design).
Number 3 is likely my best choice as this is something that is on the road-map anyways, but I haven't tried to work this out yet on how to approach it. Creating the actual web-service portion is easy, the part I'm not sure about is how to serve each image as a frame of a continuous video displayed in the browser. Bandwidth is not a consideration as viewing the video stream is not all the time. Only once every so often to analyze or ensure the video processing is working correctly. Also all traffic will be local network only.
Could microservices be a replacement for MAF (managed addon framework)
I'm working on a project with a requirement similar to add-ons (for reporting, the requirement is to add change remove etc.. reports in form of dll's while the main application is still running).
While researching for a solution I found the MAF which is part of .Net, but I found on some Q&A's that it's not popular (and this the reason why it will not be part of .net core! Open Q&A: .NET panel at 57:25) and not recommended for small teams, also that it's not maintained and didn't get any updates for a long time.
So my question is if Microservices architecture can be a solution for this, can a service be added dynamically, or changed while the main service is still running, and if so, which framework would be best for this.
Another point: In the report Dll's we're using RazorLight to render cshtml templates, and other nuget packages (Dapper...), in order to be able to run the dll from main service even though the main sevrice doesn't have a reference to this nuget packages, i need to run the dll in a separate AppDomain, Please correct me if I'm wrong on this.
Update Visual Studio from 2012 to 2017 makes application return error 404
I work in an application using Visual Studio 2012, .Net Framework 4.0. Now, I need to update to VS 2017. The solution opens, builds and runs without errors.
When I access a public resource (i.e. Login.aspx, images), it loads successfully. After the login, the server always responds with an error 404. No matter if the resource requires authentication or not.
The exception is for the login page itself which never fails. After loading it, makes the other public resources also available (again).
Any idea for what might be the cause?
MAX() OVER PARTITION BY in Oracle SQL
I am trying to utilize the MAX() OVER PARTITION BY function to evaluate the most recent receipt for a specific part that my company has bought. Below is an example table of the information for a few parts from the last year:
| VEND_NUM | VEND_NAME | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE | |----------|--------------|-------------|----------|--------------| | 100 | SmallTech | 2001 | 5844HAJ | 11/22/2017 | | 100 | SmallTech | 3188 | 5521LRO | 12/31/2017 | | 200 | RealSolution | 5109 | 8715JUI | 05/01/2017 | | 100 | SmallTech | 3232 | 8715JUI | 11/01/2017 | | 200 | RealSolution | 2101 | 4715TEN | 01/01/2017 |
As you can see, the third and fourth row show two different vendors for the SAME part number.
Here is my current query:
WITH -- various other subqueries above... AllData AS ( SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE FROM tblVend INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM WHERE VEND_NUM = '100' OR VEND_NUM = '200' AND RECEIPT_DATE >= '01-Jan-2017' ), SELECT MAX(RECEIPT_DATE) OVER PARTITION BY(RECEIPT_ITEM) AS "Recent Date", RECEIPT_ITEM FROM AllData
My return set looks like:
| Recent Date | RECEIPT_ITEM | |-------------|--------------| | 11/22/2017 | 5844HAJ | | 12/31/2017 | 5521LRO | | 11/01/2017 | 8715JUI | | 11/01/2017 | 8715JUI | | 01/01/2017 | 4715TEN |
However, it should look like this:
| Recent Date | RECEIPT_ITEM | |-------------|--------------| | 11/22/2017 | 5844HAJ | | 12/31/2017 | 5521LRO | | 11/01/2017 | 8715JUI | | 01/01/2017 | 4715TEN |
Can anybody please offer advice as to what I'm doing wrong? It looks like it is simply replacing the most recent date, not giving me just the row I want that is most recent.
Ultimately, I would like for my table to look like this. However, I don't know how to use the MAX() or MAX() OVER PARTITION BY() functions properly to allow for this:
| VEND_NUM | VEND_NAME | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE | |----------|--------------|-------------|----------|--------------| | 100 | SmallTech | 2001 | 5844HAJ | 11/22/2017 | | 100 | SmallTech | 3188 | 5521LRO | 12/31/2017 | | 100 | SmallTech | 3232 | 8715JUI | 11/01/2017 | | 200 | RealSolution | 2101 | 4715TEN | 01/01/2017 |
Thank you very much for your time.
MVC 5 - Use windows authentication from active directory but get roles from existing oracle database
I am creating new application in MVC 5 which needs to use existing oracle database which was being used with previous ASP.Net application. The new application needs to authenticate users with active directory but get roles from Oracle database User_Roles table.
turnaround time calculation in oracle SQL developer
I am trying to calculate turnaround time in days between 2 dates for each record.
- the first date (ORDERDATE), which is a string that I converted to date format using To_char(to_date) function
- the second date is (CURRENT_DATE) which has proper date format.
SELECT SPECCODE, SOURCECODE, SOURCEDESCRIPTION, **TO_CHAR( TO_DATE (PATCASE.ORDEREDDATE, 'YYYYMMDD'))"ORDER_DATE", CURRENT_DATE** FROM ...........