Compare and insert using SAS or oracle SQL

Question

0.00/5 (No votes)

See more:

I have created two tables in SAS WORK Library using data queried from Oracle tables using implicit pass through connections. I am now looking to compare the values in colA of table_X with values in colP in table_Y and if they are not equal then insert the colA,colB values of table_X to colP and colQ values in table_Y. The colR value of table_Y needs to be updated by getting its value from a SQL Query. Sample data

Table_X is as follows

colA colB
DAVE 01-Apr-2022
MARK 05-Jan-2021
ROCK 10-FEB-2022

Table_Y is as follows

colP colQ colR
DAVE 01-Apr-2022 100

What I essentially want is If Value.Table_X.colA != Value.Table_Y.colP then INSERT that value of Table_X.colA and Table_X.colB INTO Table_Y.colP and Table_Y.colQ respectively. Table_Y.colR value would be filled by a SQL Query (which I can connect to SQL DBs using Implicit or Explicit Pass-Through connections)

Final Output for Table_Y should look like this

colP colQ colR
DAVE 01-Apr-2022 100
MARK 05-Jan-2021 200
ROCK 10-FEB-2022 500
How can I do this in SAS and/or Oracle SQL (I can push Table_X and Table_Y in Oracle using Explicit-Pass Through and use Oracle Syntax).

What I have tried:

I don't know what to try. I am looking for what can be done in either SAS or Oracle SQL for this

Posted 12-Apr-22 12:52pm

Rogue258

Updated 12-Apr-22 16:37pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2022-04-12T16:37:00

Solution 1

You didn't specify where the value colR comes from but in general, using Oracle SQL the query could look something like if I understood the question correctly

SQL

INSERT INTO Table_Y (colP, colQ, colR)
SELECT a.colA,
       a.colB,
       (for example some scalar query goes here)
FROM Table_X a
WHERE NOT EXISTS (SELECT 1
                  FROM   Table_Y b
                  WHERE  b.colP = a.ColA)

Posted 12-Apr-22 16:37pm

Wendelius

Comments

Rogue258 13-Apr-22 10:41am

@Wendelius colR comes from some other Query which has its own tables (table_F, table_J,table_k etc). The reason that I am not doing this for all the values and doing a check is because calculating colR for all the values of colP, every time would require a lot of computation (colR is calculated from tables which have 300-400 million rows and 60-100 columns). Since this will be a daily task, and the values of colR won't change once calculated, I did not want it to repeat for already present. Thank you for your solution.