SQL Command Not Properly Ended 1

[omerso]

I am trying to add a barcode to this report, but getting way too much data. My first simple attempt worked (see 1st command below). When I tried to add something similar to an existing report (see 2nd command below) I am getting an error (SQL command not properly ended). Any help would be greatly appreciated.

SELECT
A.BI_ACCT, C.BI_CUST_NBR,
fn_genbarcode(
'biAcct='||to_char(a.bi_acct)||'::'||
'biCustNbr='||to_char(c.bi_cust_nbr)) as barcode,
(
'biAcct='||to_char(a.bi_acct)||'::'||
'biCustNbr='||to_char(c.bi_cust_nbr)) as str_barcode
FROM
(SELECT BI_ACCT FROM BI_AR WHERE bi_acct = {?p_bi_acct}) AR
LEFT OUTER JOIN BI_AR A ON AR.BI_ACCT = A.BI_ACCT
LEFT OUTER JOIN BI_CONSUMER C ON A.BI_ACCT = C.BI_ACCT

SELECT 
      CML.BI_MEM_NBR,
      A.BI_AR_STAT_CD,
      C.BI_CUST_NBR,
      T.BI_CON_DT,
T.BI_DISC_DT,
      C.BI_ACCT,
      CML.BI_TYPE_SRV,
      SL.BI_CNTY_CD,
      CV.BI_SORT_NAME,
      CV.BI_FNAME,
      CV.BI_MNAME,
      CV.BI_LNAME,
      CV.BI_ADDL_FNAME,
      CV.BI_ADDL_MNAME,
      CV.BI_ADDL_LNAME,
      CV.BI_PRT_CD,
      CV.BI_FORMAT_NAME,
      M.BI_ACCT AS MEM_ACCT,
      M.BI_MEM_CD,
      CASE WHEN (C.BI_ACCT = M.BI_ACCT AND M.BI_VOTE_SW = 'Y') THEN 'Y' ELSE ' ' END AS VOTE_SW,
      M.BI_VOTE_SW,
      SL.BI_DIST_OFC_CD,
      SL.BI_BOARD_DIST_CD,
      CASE WHEN (CV.BI_ADDL_PRT_CD IN (1,0)) THEN CV.BI_ADDL_LNAME||' '||CV.BI_ADDL_FNAME||'   '||CV.BI_ADDL_MNAME ELSE ' ' END AS BI_FORMAT_ADDL_NAME,
      CV.BI_ADDR1,
      CV.BI_ADDR2,
      CV.BI_ADDR3,
      CV.BI_CITY,
      CV.BI_ST,
      CV.BI_ZIP,
      CV.BI_PLUS_4,
      CV.BI_HOME_AREA_CD,
      CV.BI_HOME_PHN,
      CV.BI_MOBL_AREA_CD,
      CV.BI_MOBL_PHN,
      CV.BI_BUS_AREA_CD,
      CV.BI_BUS_PHN,
      CASE CV.BI_PRT_CD WHEN 3 THEN RTRIM(CV.BI_ADDR1) ELSE RTRIM (CV.BI_FORMAT_NAME) END PRINT_ADDR1,
      CASE CV.BI_PRT_CD WHEN 3 THEN RTRIM(CV.BI_ADDR2) ELSE RTRIM (CV.BI_ADDR1) END PRINT_ADDR2,
      CASE CV.BI_PRT_CD WHEN 3 THEN RTRIM(CV.BI_ADDR3) ELSE RTRIM (CV.BI_ADDR2) END PRINT_ADDR3,
      CASE CV.BI_PRT_CD WHEN 3 THEN CV.BI_CITY || ',  ' || CV.BI_ST || '   ' || RTRIM((RTRIM(CV.BI_ZIP) || '-' || RTRIM(CV.BI_PLUS_4)), '- ') ELSE RTRIM(CV.BI_ADDR3) END PRINT_ADDR4,
      CASE CV.BI_PRT_CD WHEN 3 THEN NULL ELSE  CV.BI_CITY || ',  ' || CV.BI_ST || '   ' || RTRIM((RTRIM(CV.BI_ZIP) || '-' || RTRIM(CV.BI_PLUS_4)), '- ') END PRINT_ADDR5,
      (SELECT BI_COOP FROM BI_MSTR_CTL) AS BI_COOP,
      (SELECT BI_COOP_NAME FROM BI_MSTR_CTL) AS BI_COOP_NAME,

fn_genbarcode(
'biAcct='||to_char(C.BI_ACCT)||'::'||
'biSortName='||to_char(CV.BI_SORT_NAME)||'::'||
'biCustNbr='||to_char(C.BI_CUST_NBR)) as barcode,

(
'biAcct='||to_char(C.BI_ACCT)||'::'||
'biSortName='||to_char(CV.BI_SORT_NAME)||'::'||
'biCustNbr='||to_char(C.BI_CUST_NBR)) as str_barcode


FROM
(SELECT BI_ACCT FROM BI_CONSUMER WHERE BI_ACCT = {?p_bi_acct}) BI_CONSUMER C
  INNER JOIN BI_CUST_MEM_LINK CML
      ON C.BI_CUST_NBR=CML.BI_CUST_NBR
  INNER JOIN BI_MEM M
      ON CML.BI_MEM_NBR=M.BI_MEM_NBR
      AND CML.BI_TYPE_SRV=M.BI_TYPE_SRV
  INNER JOIN BI_CONSUMER_VIEW_1 CV
      ON C.BI_ACCT = CV.BI_VWN_CO_ACCT
  INNER JOIN BI_AR A
      ON C.BI_ACCT = A.BI_ACCT
      AND CML.BI_TYPE_SRV = A.BI_TYPE_SRV
  INNER JOIN BI_TYPE_SERVICE T
      ON A.BI_ACCT = T.BI_ACCT
      AND A.BI_TYPE_SRV = T.BI_TYPE_SRV
      AND A.BI_PRIM_SRV_LOC_NBR = T.BI_SRV_LOC_NBR
   INNER JOIN BI_SRV_LOC SL
      ON T.BI_SRV_LOC_NBR = SL.BI_SRV_LOC_NBR
   LEFT OUTER JOIN BI_TYPE_SRV_REF TSR
      ON T.BI_TYPE_SRV = TSR.BI_TYPE_SRV
   LEFT OUTER JOIN BI_DIST_OFC_REF DOR
      ON SL.BI_DIST_OFC_CD = DOR.BI_DIST_OFC_CD
   LEFT OUTER JOIN BI_BOARD_DIST_REF BDR
      ON M.BI_VOTING_DIST_CD = BDR.BI_BOARD_DIST_CD
   LEFT OUTER JOIN BI_TYPE_SRV_GRP_REF TSGR
      ON TSR.BI_TYPE_SRV_GRP = TSGR.BI_TYPE_SRV_GRP
WHERE (('*' IN {?P_TYPE_SRV}) OR (CML.BI_TYPE_SRV IN {?P_TYPE_SRV}))
AND ((-1 IN {?P_AR_STAT_CD}) OR (A.BI_AR_STAT_CD IN {?P_AR_STAT_CD}))
AND (('*' IN {?P_BOARD_DIST_CD}) OR (SL.BI_BOARD_DIST_CD IN {?P_BOARD_DIST_CD}))
AND (('*' IN {?P_CNTY_CD}) OR (T.BI_CNTY_CD IN {?P_CNTY_CD}))

 

[omerso]

Additional Info:
If I remove the SELECT statement nested in the FROM clause, I don't get any errors, but the report crawls. I think it's trying to build a barcode for every record in the command before it looks at the Select Expert and filters down to the records I want.

 

[PeterDimitrov]

What kind of databases is this ?

I am not sure if this is wrong , but the following part has 2 aliases
(SELECT BI_ACCT FROM BI_CONSUMER WHERE BI_ACCT = {?p_bi_acct}) BI_CONSUMER C

BI_CONSUMER and C. If C is the right one then you are missing some fields. The subquery will return just BI_ACCT, but you are using also BI_CUST_NBR in the query. I have no idea how the command works at all , but my guess is that it is not returning what you expect.

You can avoid having subquery if you move BI_ACCT = {?p_bi_acct} to the where clause.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[omerso]

Thanks so much for the tips. I made the changes and it's working well.