Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
CREATE TABLE WC_COVID1 AS
SELECT '1060' COMPANY_CODE,
'AP' STATE_CODE,
WC_POS_ITEM_F.X_DRC_CODE MARKET_CODE,
'AP' MARKET_NAME,
'AP' PPAC_DIST_CODE,
'AP'DISTRICT_NAME,
W_INT_ORG_D.X_SAP_ID DISTRIBUTOR_CODE,
W_INT_ORG_D.X_NAME,
3||WC_POS_ITEM_F.x_rel_id LPG_ID,
W_PARTY_ORG_D.NAME CUSTOMER_NAME,
CASE WHEN WC_POS_ITEM_F.X_CYL_UNIT_SIZE = '14.2' THEN 'D142' ELSE 'D005'   END EQUIPMENT_CODE,
 ORDER_NUM REFILL_ID,
 ORDER_DT Refill_Booking_Date,
 X_FRGHT_AMT_DT Refill_Delivery_Date,
WC_POS_ITEM_F.DEBIT_NOTE_AMT RSP_CLMD_AMT,
x_batchid Bank_Batch_ID,

WC_POS_ITEM_F.x_bankdos Bank_Batch_ID_Date,
WC_POS_ITEM_F.DEBIT_NOTE_NUM RSP_TFD_AMT,
W_ORDER_MD.STATUS_CD,
to_char(X_FRGHT_AMT_DT, 'mm_yyyy')||'_'||
case when to_char(X_FRGHT_AMT_DT, 'DD') between '01' and '07' then  '01_07'
 when to_char(X_FRGHT_AMT_DT, 'DD') between '08' and '15' then '08_15'
 when to_char(X_FRGHT_AMT_DT, 'DD') between '16' and '22' then  '16_22'
 when to_char(X_FRGHT_AMT_DT, 'DD') > '22'  then  '23_EOM' end CLAIM_MONTH,
W_INT_ORG_D.X_PR_ADDR_ID
FROM WC_POS_ITEM_F WC_POS_ITEM_F, WC_POS_ITEM_F WC_POS_ITEM_F, W_ORDER_F W_ORDER_F, W_ORDER_MD, W_INT_ORG_D W_INT_ORG_D, W_PARTY_ORG_D W_PARTY_ORG_D, W_PRODUCT_D W_PRODUCT_D
--SIEBEL.S_ADDR_PER S_ADDR_PER 
  WHERE --W_INT_ORG_D.X_PR_ADDR_ID =S_ADDR_PER.ROW_ID AND
 WC_POS_ITEM_F.TYPE_CD = 'COVID' 
 AND WC_POS_ITEM_F.PROD_INT_ID = W_PRODUCT_D.INTEGRATION_ID
 AND WC_POS_ITEM_F.X_REL_ID = W_PARTY_ORG_D.X_SAP_ID
 AND W_PARTY_ORG_D.X_REL_TYPE = '112'
AND W_ORDER_F.PR_VIS_ORG_WID = W_INT_ORG_D.SCD1_WID
AND W_ORDER_MD.ROW_WID = W_ORDER_F.ORDER_ATTR_WID
AND W_ORDER_F.CUSTOMER_WID = W_PARTY_ORG.ROW_WID
AND WC_POS_ITEM_F.X_IOCLBANKTIDSTATUS = 'SU'
--and x_order_item_id = '1-1OGNSG9G'
and WC_POS_ITEM_F.X_INV_ID is not null
and WC_POS_ITEM_F.claim_status_cd =  'Settled';


What I have tried:

Tried putting alias against each column
Posted
Updated 25-Apr-20 8:27am

1 solution

Some simple formatting of your code shows the FROM portion to have 2 references to the same table with the same alias; any calls to elements from this table naturally are going to be confused
SQL
FROM  WC_POS_ITEM_F   WC_POS_ITEM_F
   ,  WC_POS_ITEM_F   WC_POS_ITEM_F
   ,  W_ORDER_F       W_ORDER_F
   , -- etcetera

That said... your code being a mess makes it harder to see these types of things.

It does not take much to make apply some logical formatting.. I would also recommend that if you are going to alias some names you may want to shorten the names up a bit for readability.
SQL
CREATE TABLE WC_COVID1 AS
SELECT '1060'                    COMPANY_CODE,
       'AP'                      STATE_CODE,
       WC_POS_ITEM_F.X_DRC_CODE  MARKET_CODE,
       'AP'                      MARKET_NAME,
       'AP'                      PPAC_DIST_CODE,
       'AP'                      DISTRICT_NAME,
       W_INT_ORG_D.X_SAP_ID      DISTRIBUTOR_CODE,
       W_INT_ORG_D.X_NAME,
       3||WC_POS_ITEM_F.x_rel_id PG_ID,
       W_PARTY_ORG_D.NAME        CUSTOMER_NAME,
       CASE
         WHEN WC_POS_ITEM_F.X_CYL_UNIT_SIZE = '14.2' THEN 'D142'
         ELSE 'D005'
       END                           EQUIPMENT_CODE,
       ORDER_NUM                     REFILL_ID,
       ORDER_DT                      Refill_Booking_Date,
       X_FRGHT_AMT_DT                Refill_Delivery_Date,
       WC_POS_ITEM_F.DEBIT_NOTE_AMT  RSP_CLMD_AMT,
       x_batchid Bank_Batch_ID,
       WC_POS_ITEM_F.x_bankdos       Bank_Batch_ID_Date,
       WC_POS_ITEM_F.DEBIT_NOTE_NUM  RSP_TFD_AMT,
       W_ORDER_MD.STATUS_CD,
       to_char(X_FRGHT_AMT_DT, 'mm_yyyy')||'_'||
       case
          when to_char(X_FRGHT_AMT_DT, 'DD') between '01' and '07' then '01_07'
          when to_char(X_FRGHT_AMT_DT, 'DD') between '08' and '15' then '08_15'
          when to_char(X_FRGHT_AMT_DT, 'DD') between '16' and '22' then '16_22'
          when to_char(X_FRGHT_AMT_DT, 'DD') > '22'                then '23_EOM'
      end                            CLAIM_MONTH,
      W_INT_ORG_D.X_PR_ADDR_ID

      FROM  WC_POS_ITEM_F      WC_POS_ITEM_F
         ,  WC_POS_ITEM_F      WC_POS_ITEM_F
         ,  W_ORDER_F          W_ORDER_F
         ,  W_ORDER_MD
         ,  W_INT_ORG_D        W_INT_ORG_D
         ,  W_PARTY_ORG_D      W_PARTY_ORG_D
         ,  W_PRODUCT_D        W_PRODUCT_D
        --  SIEBEL.S_ADDR_PER  S_ADDR_PER 


      WHERE /* W_INT_ORG_D.X_PR_ADDR_ID       = S_ADDR_PER.ROW_ID
      AND   */ WC_POS_ITEM_F.TYPE_CD          = 'COVID' 
      AND   WC_POS_ITEM_F.PROD_INT_ID         = W_PRODUCT_D.INTEGRATION_ID
      AND   WC_POS_ITEM_F.X_REL_ID            = W_PARTY_ORG_D.X_SAP_ID
      AND   W_PARTY_ORG_D.X_REL_TYPE          = '112'
      AND   W_ORDER_F.PR_VIS_ORG_WID          = W_INT_ORG_D.SCD1_WID
      AND   W_ORDER_MD.ROW_WID                = W_ORDER_F.ORDER_ATTR_WID
      AND   W_ORDER_F.CUSTOMER_WID            = W_PARTY_ORG.ROW_WID
      AND   WC_POS_ITEM_F.X_IOCLBANKTIDSTATUS = 'SU'
      --and   x_order_item_id                 = '1-1OGNSG9G'
      and   WC_POS_ITEM_F.X_INV_ID            is not null
      and   WC_POS_ITEM_F.claim_status_cd     =  'Settled';
 
Share this answer
 
Comments
[no name] 25-Apr-20 14:30pm    
+5
Maciej Los 26-Apr-20 3:22am    
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900