Oracle query help urgent !!

[ananthaisin]

Hi,

This is the query i am trying to build

I have attached the same in the file....


while running its throwing an error ........

NVL((SELECT USAGEMODE FROM KKTYPEMAST_AP B WHERE B.TYPE_ID_USG=A.TYPE_ID_USG AND
B.JURISDICTION=A.JURISDICTION),(SELECT USAGEMODE FROM KKTYPEMAST_AP B WHERE B.T
YPE_ID_USG=A.TYPE_ID_USG AND ROWNUM=1)),
*
ERROR at line 15:
ORA-06550: line 15, column 6:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 15, column 112:
PLS-00103: Encountered the symbol "," when expecting one of the following:
; return returning and or
ORA-06550: line 15, column 199:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
, ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order start union where connect
ORA-06550: line 18, column 5:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order p


Pls help me for the error

[amitpatel66]

You did not give the Alias name to the NVL functions:

SELECT BILL_REF_NO,
       USAGEMODE,
       BILLED_AMOUNT,
       DISCOUNT,
       SOU,
       UNITS 
FROM (
SELECT
A.BILL_REF_NO,
NVL(
    (SELECT USAGEMODE 
     FROM KKTYPEMAST_AP B 
     WHERE B.TYPE_ID_USG=A.TYPE_ID_USG
     AND B.JURISDICTION=A.JURISDICTION),
    (SELECT USAGEMODE 
     FROM KKTYPEMAST_AP B 
     WHERE B.TYPE_ID_USG=A.TYPE_ID_USG 
     AND ROWNUM=1)
   ) usagemode,
A.BILLED_AMOUNT,
A.DISCOUNT,
NVL2(
     (SELECT 1 
      FROM 
      KKTYPEMAST_AP 
      WHERE TYPE_ID_USG=A.TYPE_ID_USG 
      AND (UPPER(TYPEDESC) LIKE '%MARKUP%' OR UPPER(TYPEDESC) LIKE '%TAX%' OR (UPPER(TYPEDESC) LIKE '%PSTN%' 
      AND TYPE_ID_USG LIKE '%2')) 
      AND ROWNUM=1),A.SOU,0
    ) sou,
NVL2(
     (SELECT 1 
      FROM KKTYPEMAST_AP 
      WHERE TYPE_ID_USG=A.TYPE_ID_USG 
      AND (UPPER(TYPEDESC) LIKE '%MARKUP%' OR UPPER(TYPEDESC) LIKE '%TAX%' 
      OR (UPPER(TYPEDESC) LIKE '%PSTN%' AND TYPE_ID_USG LIKE '%2')) 
      AND ROWNUM=1),A.UNITS,0
    ) units
FROM (SELECT /*+ ordered*/       CB.ACCOUNT_NO,
      CB.SUBSCR_NO,
      CB.SUBSCR_NO_RESETS,
      CB.BILL_REF_NO,
      CB.BILL_REF_RESETS,
      CB.TYPE_ID_USG,
      CD.JURISDICTION,
      SUM(CB.BILLED_AMOUNT)/100  BILLED_AMOUNT,
      SUM(CB.DISCOUNT)/100 DISCOUNT,
      SUM(PRIMARY_UNITS) SOU,
      SUM(CD.RATED_UNITS) UNITS
      FROM 
          CD_CU4@CU4 CD,
          CB_CU4@CU4 CB
     WHERE CD.CDR_DATA_PARTITION_KEY=CB.CDR_DATA_PARTITION_KEY
     AND CD.MSG_ID=CB.MSG_ID
     AND CD.MSG_ID2=CB.MSG_ID2
     AND CD.MSG_ID_SERV=CB.MSG_ID_SERV
     AND CD.SPLIT_ROW_NUM=CB.SPLIT_ROW_NUM
     AND (CB.BILL_REF_NO,CB.BILL_REF_RESETS) IN (SELECT BILL_REF_NO,BILL_REF_RESETS FROM KK_MOU_DUMP_CU4)
     GROUP BY          CB.ACCOUNT_NO,
         CB.SUBSCR_NO,
         CB.SUBSCR_NO_RESETS,
         CB.BILL_REF_NO,
         CB.BILL_REF_RESETS,
         CB.TYPE_ID_USG,
         CD.JURISDICTION) A);