SQLQuery Script Modification PT2

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 21, 2018 at 9:37 am

  #381039

  Hello Community,

  A very clever person helped compile the following sql script:

  with cmn as (
  select
    a.a
  from TableA a
  inner join TableB b on a.a = b.a and a.b = b.b and a.c = b.c
  and a.b = 'bb'
  )
select 0 as is_deleted, a.a, b, c, d, e, f from TableA a inner join cmn on a.a = cmn.a
union all
select 1 as is_deleted, b.a, b, c, d, e, f from TableB b inner join cmn on b.a = cmn.a

  The script currently queries columns a b c d e f as shown in the image below:
  

  However, I need to change the column names to as shown in the image below:
  

  Can someone let me know what changes I need to make to the script?
  Very much appreciated.
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 21, 2018 at 11:15 am

  #2017113

  carlton 84646 - Friday, December 21, 2018 9:37 AM

  Hello Community,

  A very clever person helped compile the following sql script:

  with cmn as (
  select
    a.a
  from TableA a
  inner join TableB b on a.a = b.a and a.b = b.b and a.c = b.c
  and a.b = 'bb'
  )
select 0 as is_deleted, a.a, b, c, d, e, f from TableA a inner join cmn on a.a = cmn.a
union all
select 1 as is_deleted, b.a, b, c, d, e, f from TableB b inner join cmn on b.a = cmn.a

  The script currently queries columns a b c d e f as shown in the image below:
  

  However, I need to change the column names to as shown in the image below:
  

  Can someone let me know what changes I need to make to the script?
  Very much appreciated.
  

  Use column aliases like you did for Is_Deleted.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736162

  More actions

  December 21, 2018 at 1:41 pm

  #2017128

  You can set the alias in the first SELECT and it will work. Doesn't need to be in both.

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 21, 2018 at 1:49 pm

  #2017131

  Steve Jones - SSC Editor - Friday, December 21, 2018 1:41 PM

  You can set the alias in the first SELECT and it will work. Doesn't need to be in both.

  Hi Steve Jones, thanks for reaching out.
  I don't want to seem like the type of person who wants someone to do the work for them, but could you guide me as to what you mean when you say 'set the alias in the first SELECT?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736162

  More actions

  December 21, 2018 at 1:59 pm

  #2017137

  
with cmn as (
select
  a.a
from TableA a
inner join TableB b on a.a = b.a and a.b = b.b and a.c = b.c
and a.b = 'bb'
)
select 0 as is_deleted, a.a as FirstName, b, c, d, e, f from TableA a inner join cmn on a.a = cmn.a
union all
select 1 as is_deleted, b.a, b, c, d, e, f from TableB b inner join cmn on b.a = cmn.a

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 21, 2018 at 5:14 pm

  #2017143

  Steve Jones - SSC Editor - Friday, December 21, 2018 1:59 PM

  
  with cmn as (
  select
    a.a
  from TableA a
  inner join TableB b on a.a = b.a and a.b = b.b and a.c = b.c
  and a.b = 'bb'
  )
  select 0 as is_deleted, a.a as FirstName, b, c, d, e, f from TableA a inner join cmn on a.a = cmn.a
  union all
  select 1 as is_deleted, b.a, b, c, d, e, f from TableB b inner join cmn on b.a = cmn.a

  Steve, I haven't had a chance to test the script, but I just wanted to say thanks for reaching again.