July 4, 2014 at 5:43 am
Hi, I have this Header Code:
select
UPLN.PLNNM
,UBRC.BRCNM
,UEMP.EMPRNM
,UEMP.EMPID
,UMBR.MBRANM
,UMBR.MBRID
,UCONHH.CONID
,UCONHH.CONRDT
,UCONHH.CONDDT
,UCONH.CONCDT
,UCONH.CONCID
,UADJDH.ADJID
,UADJDH.ADJRCD
,UADJDH.ADJCDT
,UADJDH.ADJCID
,UCONDH.PERFNO
,UADJDH.ADDBVL
,UCONDH.PERSNO
,(UCOND.CODAVL + UCOND.CODNVL + UCOND.CODSVL + UCOND.CODMVL) VAL
,UCONDH.CODPUV
,UCONDH.CODUFC
,UCONDH.CODAWD
,UCONDH.CODBNV
,UCONDH.CODNP
,UCONDH.CODNN
,UCONDH.CODBSV
,UCONDH.CODSP
,UCONDH.CODSN
,UCONDH.CODBMV
,UCONDH.CODMP
,UCONDH.CODMN
,UPLN.PRDID
,UPLN.PLNSCD
from ebl2ulif.UMBR
,ebl2ulif.UPRD
,ebl2ulif.UBRC
,ebl2ulif.UEMP
,ebl2ulif.UPLN
,ebl2ulif.UADJDH
,ebl2ulif.UCONDH
,ebl2ulif.UCONH
,ebl2ulif.UCOND
,ebl2ulif.UCONHH
where UPLN.PLNID = UMBR.PLNID
and UPLN.PRDID = UMBR.PRDID
and UMBR.PRDID = UPRD.PRDID
and UBRC.BRCID = UMBR.BRCRID
and UMBR.PRDID = UADJDH.PRDID
and UMBR.PLNID = UADJDH.PLNID
and UMBR.MBRID = UADJDH.MBRID
and UADJDH.EMPID = UEMP.EMPID
/* and UADJDH.CONID = UCONDH.CONID */
and UADJDH.MBRID = UCONDH.MBRID
and UCONDH.CONID = UCONH.CONID
and UCONDH.CONID = UCOND.CONID
and UCONDH.MBRID = UCOND.MBRID
and UCONDH.CONID = UCONHH.CONID
and I also have this Body code:
select
'CON' TYP
,null ADJID
,UCONDH.CONCDT
,UCONDH.CODPUV
,UCONDH.CODAWD "MBRVOL"
,(UCONDH.CODNN + UCONDH.CODNP ) "EMPNML"
,(UCONDH.CODSN + UCONDH.CODSP ) "MBRNML"
,UCONDH.PRDID
,UCONDH.PLNID
,UCONDH.CONID
,UCONDH.MBRID
FROM ebl2ulif.UCONDH
union all
SELECT
'ADJ' TYP
,UADJDH.ADJID
,UADJDH.ADJCDT CONCDT
,UCONDH.CODPUV
,UADJDH.ADDAWD "MBRVOL"
,(UADJDH.ADDNN + UADJDH.ADDNP ) "EMPNML"
,(UADJDH.ADDSN + UADJDH.ADDSP ) "MBRNML"
,UADJDH.PRDID
,UADJDH.PLNID
,UADJDH.CONID
,UADJDH.MBRID
from ebl2ulif.UADJDH
,ebl2ulif.UCONDH
,ebl2ulif.UCONHH
where UADJDH.CONID = UCONDH.CONID
and UADJDH.MBRID = UCONDH.MBRID
and UCONDH.CONID = UCONHH.CONID
how do I combine these two codes, considering also the UNION in the Body code.
Thank you.
July 4, 2014 at 6:18 am
Parallel combination or serial? If serial, you will have to match the number and type of output columns between the queries. If parallel, you will require a join strategy.
You're using old-style joins, how do you do an outer join?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 1:25 am
What I want it's for the results of the second query to be joined to the results of the first query. I don't know if that's parallel or not.
July 7, 2014 at 1:47 am
hoseam (7/7/2014)
What I want it's for the results of the second query to be joined to the results of the first query. I don't know if that's parallel or not.
Joined side by side = parallel. The results from one query tacked onto the bottom of the results from the other query = serial. They're completely different.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 2:03 am
Then it's serial I'm looking for.
July 7, 2014 at 2:14 am
hoseam (7/7/2014)
Then it's serial I'm looking for.
Thanks.
Then you should use UNION (ALL). The results of the two queries have widely different column counts. You will have to decide which columns match.
You could use two CTE's or run the results into two #temp tables (or any combination). If you choose to use two CTE's, your query will look something like this:
;WITH
CTE1 AS (first query)
, CTE2 AS (second query)
SELECT <explicit column list> FROM CTE1
UNION ALL
SELECT <explicit column list> FROM CTE2.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 2:49 am
You could almost certainly do all of this work in a single query if you switched from old-style joins to ANSI-standard joins and learned how to use outer joins. Wikipedia has a reasonable entry here.
This union query
SELECT
'CON' TYP
,null ADJID
,UCONDH.CONCDT
,UCONDH.CODPUV
,UCONDH.CODAWD "MBRVOL"
,(UCONDH.CODNN + UCONDH.CODNP ) "EMPNML"
,(UCONDH.CODSN + UCONDH.CODSP ) "MBRNML"
,UCONDH.PRDID
,UCONDH.PLNID
,UCONDH.CONID
,UCONDH.MBRID
INTO #Body
FROM ebl2ulif.UCONDH
UNION ALL
SELECT
'ADJ' TYP
,UADJDH.ADJID
,UADJDH.ADJCDT CONCDT
,UCONDH.CODPUV
,UADJDH.ADDAWD "MBRVOL"
,(UADJDH.ADDNN + UADJDH.ADDNP ) "EMPNML"
,(UADJDH.ADDSN + UADJDH.ADDSP ) "MBRNML"
,UADJDH.PRDID
,UADJDH.PLNID
,UADJDH.CONID
,UADJDH.MBRID
FROM ebl2ulif.UCONDH
,ebl2ulif.UADJDH
,ebl2ulif.UCONHH
WHERE UADJDH.CONID = UCONDH.CONID
AND UADJDH.MBRID = UCONDH.MBRID
AND UCONDH.CONID = UCONHH.CONID
is simply a costly way of left-joining UADJDH and UCONHH to UCONDH.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 2:58 am
I'm running the Code in Toad Oracle, so #temp doesn't work there.
July 7, 2014 at 3:02 am
hoseam (7/7/2014)
I'm running the Code in Toad Oracle, so #temp doesn't work there.
Are you working with SQL Server or Oracle? This is the SQL Server 2008 forum section. Answers provided to your question not be guaranteed to work in Oracle.
The principle remains the same:
select -- 33 columns
UPLN.PLNNM
,UBRC.BRCNM
,UEMP.EMPRNM
,UEMP.EMPID
,UMBR.MBRANM
,UMBR.MBRID
,UCONHH.CONID
,UCONHH.CONRDT
,UCONHH.CONDDT
,UCONH.CONCDT
,UCONH.CONCID
,UADJDH.ADJID
,UADJDH.ADJRCD
,UADJDH.ADJCDT
,UADJDH.ADJCID
,UCONDH.PERFNO
,UADJDH.ADDBVL
,UCONDH.PERSNO
,(UCOND.CODAVL + UCOND.CODNVL + UCOND.CODSVL + UCOND.CODMVL) VAL
,UCONDH.CODPUV
,UCONDH.CODUFC
,UCONDH.CODAWD
,UCONDH.CODBNV
,UCONDH.CODNP
,UCONDH.CODNN
,UCONDH.CODBSV
,UCONDH.CODSP
,UCONDH.CODSN
,UCONDH.CODBMV
,UCONDH.CODMP
,UCONDH.CODMN
,UPLN.PRDID
,UPLN.PLNSCD
INTO #Header
from ebl2ulif.UMBR
,ebl2ulif.UPRD
,ebl2ulif.UBRC
,ebl2ulif.UEMP
,ebl2ulif.UPLN
,ebl2ulif.UADJDH
,ebl2ulif.UCONDH
,ebl2ulif.UCONH
,ebl2ulif.UCOND
,ebl2ulif.UCONHH
where UPLN.PLNID = UMBR.PLNID
and UPLN.PRDID = UMBR.PRDID
and UMBR.PRDID = UPRD.PRDID
and UBRC.BRCID = UMBR.BRCRID
and UMBR.PRDID = UADJDH.PRDID
and UMBR.PLNID = UADJDH.PLNID
and UMBR.MBRID = UADJDH.MBRID
and UADJDH.EMPID = UEMP.EMPID
/* and UADJDH.CONID = UCONDH.CONID */
and UADJDH.MBRID = UCONDH.MBRID
and UCONDH.CONID = UCONH.CONID
and UCONDH.CONID = UCOND.CONID
and UCONDH.MBRID = UCOND.MBRID
and UCONDH.CONID = UCONHH.CONID
SELECT
'CON' TYP
,null ADJID
,UCONDH.CONCDT
,UCONDH.CODPUV
,UCONDH.CODAWD "MBRVOL"
,(UCONDH.CODNN + UCONDH.CODNP ) "EMPNML"
,(UCONDH.CODSN + UCONDH.CODSP ) "MBRNML"
,UCONDH.PRDID
,UCONDH.PLNID
,UCONDH.CONID
,UCONDH.MBRID
INTO #Body
FROM ebl2ulif.UCONDH
UNION ALL
SELECT
'ADJ' TYP
,UADJDH.ADJID
,UADJDH.ADJCDT CONCDT
,UCONDH.CODPUV
,UADJDH.ADDAWD "MBRVOL"
,(UADJDH.ADDNN + UADJDH.ADDNP ) "EMPNML"
,(UADJDH.ADDSN + UADJDH.ADDSP ) "MBRNML"
,UADJDH.PRDID
,UADJDH.PLNID
,UADJDH.CONID
,UADJDH.MBRID
FROM ebl2ulif.UCONDH
,ebl2ulif.UADJDH
,ebl2ulif.UCONHH
WHERE UADJDH.CONID = UCONDH.CONID
AND UADJDH.MBRID = UCONDH.MBRID
AND UCONDH.CONID = UCONHH.CONID
SELECT
PLNNM,BRCNM,EMPRNM,EMPID,MBRANM,MBRID,CONID,CONRDT,CONDDT,CONCDT,CONCID,
ADJID,ADJRCD,ADJCDT,ADJCID,PERFNO,ADDBVL,PERSNO,VAL,CODPUV,CODUFC,CODAWD,
CODBNV,CODNP,CODNN,CODBSV,CODSP,CODSN,CODBMV,CODMP,CODMN,PRDID,PLNSCD
FROM #Header
UNION ALL
SELECT
PLNNM = NULL,BRCNM = NULL,EMPRNM = NULL,EMPID = NULL,MBRANM = NULL,MBRID = MBRID,CONID = CONID,CONRDT = NULL,CONDDT = NULL,CONCDT = CONCDT,CONCID = NULL,
ADJID = ADJID,ADJRCD = NULL,ADJCDT = NULL,ADJCID = NULL,PERFNO = NULL,ADDBVL = NULL,PERSNO = NULL,VAL = NULL,CODPUV = CODPUV,CODUFC = NULL,CODAWD = MBRVOL,
CODBNV = NULL,CODNP = NULL,CODNN = NULL,CODBSV = NULL,CODSP = NULL,CODSN = NULL,CODBMV = NULL,CODMP = NULL,CODMN = NULL,PRDID = PRDID,PLNSCD = NULL
FROM #Body
/* Unmatched columns
TYP
,(UCONDH.CODNN + UCONDH.CODNP ) "EMPNML"
,(UCONDH.CODSN + UCONDH.CODSP ) "MBRNML"
,UCONDH.PLNID
*/
/* Matched columns
,null ADJID
,UCONDH.CONCDT
,UCONDH.CODPUV
,UCONDH.CODAWD "MBRVOL"
,UCONDH.PRDID
,UCONDH.CONID
,UCONDH.MBRID
*/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 7:43 am
So what about the data types from #Header and #Body since we going to Union both queries?
July 7, 2014 at 7:46 am
hoseam (7/7/2014)
So what about the data types from #Header and #Body since we going to Union both queries?
Quite. I could guess them for you, or you could investigate what they actually are. The first option could take the rest of the day.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 7, 2014 at 7:57 am
Thought I maybe be wrong, wanted to confirm.
July 7, 2014 at 8:02 am
That's why I put the query which had the most columns first in the UNION. The NULL placeholders in query 2 don't need datatyping. You only have to investigate the columns which exist in query 2 which don't exist in query 1, and put appropriately-typed NULL placeholders in query 1.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2014 at 2:49 am
How can I deal with this situation, The #Header:
select
UPLN.PRDID
,UPLN.PLNID
,UPLN.PLNNM
,UBRC.BRCNM
,UMBR.MBRANM
,UMBR.MBRID
,UINTH.INTID
,UINTH.INTEDT
,UPLN.PLNSCD
from ebl2ulif.UMBR
,ebl2ulif.UPLN
,ebl2ulif.UBRC
,ebl2ulif.UINTH
where UPLN.PLNID = UMBR.PLNID
and UPLN.PRDID = UMBR.PRDID
and UBRC.BRCID = UINTH.BRCRID
and UMBR.PLNID = UINTH.PLNID
and UMBR.PRDID = UINTH.PRDID
and UMBR.MBRID = UINTH.MBRID
and UMBR.BRCRID = UINTH.BRCRID
Those are all INT
Then #Body, with only VARCHARS, Can I still Join them Serially like you did up there??
select 'Preserved' PRVD_STATUS
,UINTH.INTNP EMPLOYER_NML
,UINTH.INTSP MEMBER_NML
,UINTH.INTMP MEMBER_VOL
,UMBR.PRDID
,UPLN.PLNSCD
,UMBR.MBRID
,UINTH.INTID
from ebl2ulif.UMBR
,ebl2ulif.UPLN
,ebl2ulif.UBRC
,ebl2ulif.UINTH
where UPLN.PLNID = UMBR.PLNID
and UPLN.PRDID = UMBR.PRDID
and UBRC.BRCID = UINTH.BRCRID
and UMBR.PLNID = UINTH.PLNID
and UMBR.PRDID = UINTH.PRDID
and UMBR.MBRID = UINTH.MBRID
and UMBR.BRCRID = UINTH.BRCRID
union all
select 'Non Preserved' PRVD_STATUS
,UINTH.INTNNP EMPLOYER_NML
,UINTH.INTSN MEMBER_NML
,UINTH.INTMNP MEMBER_VOL
,UMBR.PRDID
,UPLN.PLNSCD
,UMBR.MBRID
from ebl2ulif.UMBR
,ebl2ulif.UPLN
,ebl2ulif.UBRC
,ebl2ulif.UINTH
UINTH.INTID
where UPLN.PLNID = UMBR.PLNID
and UPLN.PRDID = UMBR.PRDID
and UBRC.BRCID = UINTH.BRCRID
and UMBR.PLNID = UINTH.PLNID
and UMBR.PRDID = UINTH.PRDID
and UMBR.MBRID = UINTH.MBRID
and UMBR.BRCRID = UINTH.BRCRID
July 8, 2014 at 3:03 am
The output columns of #header (and #body?) in this new post are completely different to earlier posts. Are you simply describing the data types of these columns or proposing a new set of output columns?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply