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.TY PE_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:

[code=oracle]
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.T YPE_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.T YPE_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.UNI TS,0
) units
FROM (SELECT /*+ ordered*/ CB.ACCOUNT_NO,
CB.SUBSCR_NO,
CB.SUBSCR_NO_RE SETS,
CB.BILL_REF_NO,
CB.BILL_REF_RES ETS,
CB.TYPE_ID_USG,
CD.JURISDICTION ,
SUM(CB.BILLED_A MOUNT)/100 BILLED_AMOUNT,
SUM(CB.DISCOUNT )/100 DISCOUNT,
SUM(PRIMARY_UNI TS) SOU,
SUM(CD.RATED_UN ITS) UNITS
FROM
CD_CU4@CU4 CD,
CB_CU4@CU4 CB
WHERE CD.CDR_DATA_PAR TITION_KEY=CB.C DR_DATA_PARTITI ON_KEY
AND CD.MSG_ID=CB.MS G_ID
AND CD.MSG_ID2=CB.M SG_ID2
AND CD.MSG_ID_SERV= CB.MSG_ID_SERV
AND CD.SPLIT_ROW_NU M=CB.SPLIT_ROW_ NUM
AND (CB.BILL_REF_NO ,CB.BILL_REF_RE SETS) IN (SELECT BILL_REF_NO,BIL L_REF_RESETS FROM KK_MOU_DUMP_CU4 )
GROUP BY CB.ACCOUNT_NO,
CB.SUBSCR_NO,
CB.SUBSCR_NO_RE SETS,
CB.BILL_REF_NO,
CB.BILL_REF_RES ETS,
CB.TYPE_ID_USG,
CD.JURISDICTION ) A);
[/code]