All Forums Analytics
Chuken22 5 posts Joined 07/12
23 Aug 2012
JOIN - Need to include a.tkt_num = b.tkt_num And (a.tkt_num not = b.tkt_num)

Hi,

Can you please assist me with the following Join Query… I’d like my query to always show everything from the pedw_tmp.tickets_ccb_tmp even if there’s nothing in the pedw.tickets b, and pedw_tmp.sadf_rec40_stg.  They (.b, .c) can be NIL or BLANK if there are no tkt_num matches but I must have all data from tickets_ccb_tmp a.

Thank you for your help!

 

CURRENT QUERY:

SELECT  a.postdate, a.cc_typ_cde, a.sale_crcy_cde, a.billing_crcy_cde, a.tkt_num, a.sale_amt, a.billing_amt, b.tkt_cpn_ind, b.true_orig, b.true_dest, c.tkt_rpt_src_typ, c.CPN1, c.CPN2, c.CPN3, c.CPN4 FROM pedw_tmp.tickets_ccb_tmp a JOIN  (SELECT b.tkt_num,  b.tkt_cpn_ind, b.true_orig, b.true_dest FROM pedw.tickets b  WHERE b.postdate > '2012-01-01'   GROUP BY 1,2,3,4  ) b ON a.tkt_num = b.tkt_num  JOIN  (SELECT     c.tkt_num, c.tkt_rpt_src_typ, max((CASE WHEN c.tkt_cpn_num = '1' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde  end)) as CPN1, max((CASE WHEN c.tkt_cpn_num = '2' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN2, max((CASE WHEN c.tkt_cpn_num = '3' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN3, max((CASE WHEN c.tkt_cpn_num = '4' THEN c.seg_orig_ap_cde || c.seg_dest_ap_cde end)) as CPN4 FROM pedw_tmp.sadf_rec40_stg c  WHERE c.postdate > '2012-01-01'   GROUP BY 1,2 ) c  ON a.tkt_num = c.tkt_num  WHERE a.sale_crcy_cde = 'USD'  AND a.billing_crcy_cde = 'USD' AND a.cc_typ_cde IN ('ca','vi')  AND a.postdate = '2012-08-10' ORDER BY 1,2,3,4,5;

 

CURRENT QUERY OUTPUT

 

 

 

 

 

 

 

 

 

 

 

pedw_tmp.tickets_ccb_tmp a

pedw.tickets b

pedw_tmp.sadf_rec40_stg

POSTDATE

CC_
TYP
_CDE

SALE
_CRCY
_CDE

BILLING
_CRCY
_CDE

TKT_NUM

SALE
_AMT

BILLING
_AMT

TKT
_CPN
_IND

TRUE
_ORIG

TRUE
_DEST

TKT
_RPT
_SRC
_TYP

CPN1

CPN2

CPN3

CPN4

2012/08/10

VI

USD

USD

1X205XXXX582

$23.63

$23.63

SSVV

YYZ

LGA

BSP 

YYZ  LGA  

LGA  YYZ  

?

?

2012/08/10

VI

USD

USD

1X205XXXX590

$23.63

$23.63

SSSS

YLW

LAX

BSP 

YLW  YVR  

YVR  LAX  

LAX  YVR  

YVR  YLW  

***

 

 

 

 

 

 

 

 

 

 

 

 

 

 

***tkt_num - 1X205XXXX085 Is Missing/dropped as there is no data in SADF_rec40 or in Tickets

 

 

 

 

 

 

 

Desired final output to look something like this…(I need all the tkts from ccb_tmp a to Balance to a Aug 10 Report)

 

 

POSTDATE

 

 

CC_
TYP
_CDE

SALE
_CRCY
_CDE

BILLING
_CRCY
_CDE

TKT_NUM

SALE
_AMT

BILLING
_AMT

TKT
_CPN
_IND

TRUE
_ORIG

TRUE
_DEST

TKT
_RPT
_SRC
_TYP

CPN1

CPN2

CPN3

CPN4

2012/08/10

VI

USD

USD

1X205XXXX582

$23.63

$23.63

SSVV

YYZ

LGA

BSP 

YYZ  LGA  

LGA  YYZ  

?

?

2012/08/10

CA

USD

USD

1X205XXXX085

$22.50

$22.50

NIL or Blank

NIL or Blank

NIL or Blank

NIL or Blank

NIL or Blank

NIL or Blank

NIL or Blank

NIL or Blank

2012/08/10

VI

USD

USD

1X205XXXX590

$23.63

$23.63

SSSS

YLW

LAX

BSP 

YLW  YVR  

YVR  LAX  

LAX  YVR  

YVR  YLW  

 

 

Here’s the  Data  from the 3 Sources:

 

All 3 tkts in pedw_tmp.tickets_ccb_tmp a

 

 

 

 

POSTDATE

CC_TYP_CDE

SALE_CRCY_CDE

BILLING_CRCY_CDE

TKT_NUM

SALE_AMT

BILLING_AMT

2012/08/10

VI

USD

USD

1X205XXXX582

$23.63

$23.63

2012/08/10

CA

USD

USD

1X205XXXX085

$22.50

$22.50

2012/08/10

VI

USD

USD

1X205XXXX590

$23.63

$23.63

 

 

 

 

 

 

 

pedw.tickets b

 

(1X205XXXX085 tkt_num is not in pedw.tickets b)

 

 

TKT_NUM

TKT_CPN_IND

TRUE_ORIG

TRUE_DEST

 

 

 

1X205XXXX582

SSVV

YYZ

LGA

 

 

 

1X205XXXX590

SSSS

YLW

LAX

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

pedw_tmp.sadf_rec40_stg

(1X205XXXX085 tkt_num is not in pedw_tmp.sadf_rec40_stg c)

 

TKT_NUM

TKT_RPT_SRC_TYP

SEG_ORIG_AP_CDE

SEG_DEST_AP_CDE

 

 

 

1X205XXXX582

BSP 

YYZ  

LGA  

 

 

 

1X205XXXX582

BSP 

LGA  

YYZ  

 

 

 

1X205XXXX590

BSP 

YLW  

YVR  

 

 

 

1X205XXXX590

BSP 

YVR  

LAX  

 

 

 

1X205XXXX590

BSP 

LAX  

YVR  

 

 

 

1X205XXXX590

BSP 

YVR  

YLW  

 

 

 

 

 

 

 

 

 

 

 

 

 

CarlosAL 512 posts Joined 04/08
24 Aug 2012

>>"Can you please assist me with the following Join Query… I’d like my query to always show everything from the pedw_tmp.tickets_ccb_tmp even if there’s nothing in the pedw.tickets b, and pedw_tmp.sadf_rec40_stg.  They (.b, .c) can be NIL or BLANK if there are no tkt_num matches but I must have all data from tickets_ccb_tmp a."

 

Three words: LEFT OUTER JOIN.

Cheers.

Carlos.

 

You must sign in to leave a comment.