Styjun

Anagram holiday

Is it possible to create light that imparts a greater proportion of its energy as momentum rather than heat?

Is it possible to run Internet Explorer on OS X El Capitan?

How to model explosives?

What reasons are there for a Capitalist to oppose a 100% inheritance tax?

Do I have a twin with permutated remainders?

90's TV series where a boy goes to another dimension through portal near power lines

Why do I get two different answers for this counting problem?

How much of data wrangling is a data scientist's job?

What's the point of deactivating Num Lock on login screens?

Facing a paradox: Earnshaw's theorem in one dimension

Memorizing the Keyboard

Why does Kotter return in Welcome Back Kotter?

Forgetting the musical notes while performing in concert

When a company launches a new product do they "come out" with a new product or do they "come up" with a new product?

What killed these X2 caps?

How can I tell someone that I want to be his or her friend?

Stopping power of mountain vs road bike

Why is Collection not simply treated as Collection<?>

Is it legal for company to use my work email to pretend I still work there?

How do conventional missiles fly?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

How to prevent "they're falling in love" trope

How can I make my BBEG immortal short of making them a Lich or Vampire?

Why does this statement union RowIDs from a table and a dummy table?

SQL update from one Table to another based on a ID matchHow can I get column names from a table in SQL Server?HOW TO get records with given rowid list IN STRING from a table (Oracle)?Specifying parameters in the Using clause of an Oracle Merge statementOracle SQL Merge Statement IssueSQL statement for combobox row sourceOracle Merge statement not inserting dataSQL UNION and MERGESelect from two slightly similar tables with complex conditionsOracle Merge Into's Using clause?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

0

I am working on uplifting an application from Camel and myBatis to Spring boot and Jooq.

There is a very odd sql statement that is nested in our application's merge statement's using function. I don't understand the purpose of this statement, whoever wrote it is no longer with the company.

Inside the Merge's using portion, it is effectively like

Select A, B, C, D FROM (
 Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var
 UNION ALL
 Select ROWID, null, null, null, null FROM dual
WHERE ROWID >= 1 

The ROWID is not used in any the match statements, so the reason it is here makes no sense to me at all. Though I don't have much knowledge with merge statements to begin with.

Edit: the Using's On condition is to match sure A is equal to var, as var is a variable that is sent in through the function that triggers the sql. Basically it is just ON A = var

sql oracle oracle11g

share|improve this question

edited Mar 21 at 23:39

needoriginalname

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

add a comment | 

1

0

I am working on uplifting an application from Camel and myBatis to Spring boot and Jooq.

There is a very odd sql statement that is nested in our application's merge statement's using function. I don't understand the purpose of this statement, whoever wrote it is no longer with the company.

Inside the Merge's using portion, it is effectively like

Select A, B, C, D FROM (
 Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var
 UNION ALL
 Select ROWID, null, null, null, null FROM dual
WHERE ROWID >= 1 

The ROWID is not used in any the match statements, so the reason it is here makes no sense to me at all. Though I don't have much knowledge with merge statements to begin with.

Edit: the Using's On condition is to match sure A is equal to var, as var is a variable that is sent in through the function that triggers the sql. Basically it is just ON A = var

sql oracle oracle11g

share|improve this question

edited Mar 21 at 23:39

needoriginalname

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

add a comment | 

I am working on uplifting an application from Camel and myBatis to Spring boot and Jooq.

There is a very odd sql statement that is nested in our application's merge statement's using function. I don't understand the purpose of this statement, whoever wrote it is no longer with the company.

Inside the Merge's using portion, it is effectively like

Select A, B, C, D FROM (
 Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var
 UNION ALL
 Select ROWID, null, null, null, null FROM dual
WHERE ROWID >= 1 

The ROWID is not used in any the match statements, so the reason it is here makes no sense to me at all. Though I don't have much knowledge with merge statements to begin with.

Edit: the Using's On condition is to match sure A is equal to var, as var is a variable that is sent in through the function that triggers the sql. Basically it is just ON A = var

sql oracle oracle11g

share|improve this question

edited Mar 21 at 23:39

needoriginalname

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

Select A, B, C, D FROM (
 Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var
 UNION ALL
 Select ROWID, null, null, null, null FROM dual
WHERE ROWID >= 1 

share|improve this question

edited Mar 21 at 23:39

needoriginalname

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

share|improve this question

edited Mar 21 at 23:39

needoriginalname

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

asked Mar 21 at 22:02

needoriginalnameneedoriginalname

1711416

2 Answers
2

active

oldest

votes

0

The RowID is used in the last line. (WHERE ROWID >=1).

In order to get UNION ALL working, the number of columns must be the same.

For your question, it depends on the program logic after you fetch the query. Since all we can see here two tables are merge.

For example TableA has ROWID of 1,2,3, and "dual" table has ROWID of 1,2,3. The result (the merged table) will show 1,1,2,2,3,3.

It is more important to understand the program handles the result as you can find out why the old statement does the UNION ALL.

share|improve this answer

edited Mar 22 at 0:02

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But why is there a UNION ALL?
  
  – needoriginalname
  Mar 21 at 22:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As the old statement would like to join records from two tables together. However you cannot use JOIN as it is different. Please see details below: essentialsql.com/…
  
  – Anson Fong
  Mar 21 at 23:04
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, but I think I am not being very clear with my question. I am trying to understand why you would union the two tables, one table is a copy of everyone on the table + rowid, while the other is from a Dummy Table with only null values and a row id returned. It just does not make a lick of sense why this logic was coded like this. Espically since the outer query does not show the rowid.
  
  – needoriginalname
  Mar 21 at 23:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Updated the answer to have more detail.
  
  – Anson Fong
  Mar 22 at 0:02
  
  

  
  
  
  

add a comment | 

0

It could be that the author wanted to guarantee that the query will return at least one row. (or exactly one row)

If

Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var

returns nothing (zero rows, nothing is found), then UNION ALL will append the row of NULLs and it would be returned.

I'm not familiar with Oracle, so I'm not really sure what WHERE ROWID >= 1 achieves.
Maybe it is to guarantee that the result set would always have exactly one row.

share|improve this answer

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55289924%2fwhy-does-this-statement-union-rowids-from-a-table-and-a-dummy-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

The RowID is used in the last line. (WHERE ROWID >=1).

In order to get UNION ALL working, the number of columns must be the same.

For your question, it depends on the program logic after you fetch the query. Since all we can see here two tables are merge.

For example TableA has ROWID of 1,2,3, and "dual" table has ROWID of 1,2,3. The result (the merged table) will show 1,1,2,2,3,3.

It is more important to understand the program handles the result as you can find out why the old statement does the UNION ALL.

share|improve this answer

edited Mar 22 at 0:02

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But why is there a UNION ALL?
  
  – needoriginalname
  Mar 21 at 22:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As the old statement would like to join records from two tables together. However you cannot use JOIN as it is different. Please see details below: essentialsql.com/…
  
  – Anson Fong
  Mar 21 at 23:04
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, but I think I am not being very clear with my question. I am trying to understand why you would union the two tables, one table is a copy of everyone on the table + rowid, while the other is from a Dummy Table with only null values and a row id returned. It just does not make a lick of sense why this logic was coded like this. Espically since the outer query does not show the rowid.
  
  – needoriginalname
  Mar 21 at 23:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Updated the answer to have more detail.
  
  – Anson Fong
  Mar 22 at 0:02
  
  

  
  
  
  

add a comment | 

0

The RowID is used in the last line. (WHERE ROWID >=1).

In order to get UNION ALL working, the number of columns must be the same.

For your question, it depends on the program logic after you fetch the query. Since all we can see here two tables are merge.

For example TableA has ROWID of 1,2,3, and "dual" table has ROWID of 1,2,3. The result (the merged table) will show 1,1,2,2,3,3.

It is more important to understand the program handles the result as you can find out why the old statement does the UNION ALL.

share|improve this answer

edited Mar 22 at 0:02

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But why is there a UNION ALL?
  
  – needoriginalname
  Mar 21 at 22:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As the old statement would like to join records from two tables together. However you cannot use JOIN as it is different. Please see details below: essentialsql.com/…
  
  – Anson Fong
  Mar 21 at 23:04
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, but I think I am not being very clear with my question. I am trying to understand why you would union the two tables, one table is a copy of everyone on the table + rowid, while the other is from a Dummy Table with only null values and a row id returned. It just does not make a lick of sense why this logic was coded like this. Espically since the outer query does not show the rowid.
  
  – needoriginalname
  Mar 21 at 23:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Updated the answer to have more detail.
  
  – Anson Fong
  Mar 22 at 0:02
  
  

  
  
  
  

add a comment | 

The RowID is used in the last line. (WHERE ROWID >=1).

In order to get UNION ALL working, the number of columns must be the same.

For your question, it depends on the program logic after you fetch the query. Since all we can see here two tables are merge.

For example TableA has ROWID of 1,2,3, and "dual" table has ROWID of 1,2,3. The result (the merged table) will show 1,1,2,2,3,3.

It is more important to understand the program handles the result as you can find out why the old statement does the UNION ALL.

share|improve this answer

edited Mar 22 at 0:02

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

share|improve this answer

edited Mar 22 at 0:02

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

answered Mar 21 at 22:44

Anson FongAnson Fong

34917

0

It could be that the author wanted to guarantee that the query will return at least one row. (or exactly one row)

If

Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var

returns nothing (zero rows, nothing is found), then UNION ALL will append the row of NULLs and it would be returned.

I'm not familiar with Oracle, so I'm not really sure what WHERE ROWID >= 1 achieves.
Maybe it is to guarantee that the result set would always have exactly one row.

share|improve this answer

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

add a comment | 

0

It could be that the author wanted to guarantee that the query will return at least one row. (or exactly one row)

If

Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var

returns nothing (zero rows, nothing is found), then UNION ALL will append the row of NULLs and it would be returned.

I'm not familiar with Oracle, so I'm not really sure what WHERE ROWID >= 1 achieves.
Maybe it is to guarantee that the result set would always have exactly one row.

share|improve this answer

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

add a comment | 

It could be that the author wanted to guarantee that the query will return at least one row. (or exactly one row)

If

Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var

returns nothing (zero rows, nothing is found), then UNION ALL will append the row of NULLs and it would be returned.

I'm not familiar with Oracle, so I'm not really sure what WHERE ROWID >= 1 achieves.
Maybe it is to guarantee that the result set would always have exactly one row.

share|improve this answer

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

Select ROWID as RID, A, B, C, D FROM TableA TA WHERE A = var

share|improve this answer

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965

answered Mar 22 at 0:23

Vladimir BaranovVladimir Baranov

23.1k52965