Guest User

Hi. As you can see below, there are too many parameters at SQL. (given the characteristic of installed software at WAS)
When dragging at the X-View, a window called “application” pops up. When looking at the SQL of each line, I noticed a summary about SQL. When I clicked a line and right-click, something called “query build” appears.

When clicking the query build, I noticed the SQL along with a set of parameter. When pushing a button of query build without changing the parameter, I could see the mixed SQL. But when I conducted query with such SQL, there are many missing factors including invalid identifier. What should I do to get combined SQL?

param1:[,,4,,,20,,,1,,,,,1,,,0,,,,N,N,N,,00,0,,,,YYYYMMDD,1,,OFF,ON,N,N,0,0,0,,,N,,,1,N,N,N,50,A,20,A,B,00,0,0,4,5,4,0,00,9,NATION_MK_CD,Y,Y,03,Y,03,Y,60,] param2:[‘3873952’,‘3873952’,‘110’,‘3873952’,‘110’,‘INT’,‘3873952’,‘’,’110’,‘INT’,‘3873952’,‘110’,‘INT’,‘3873952’,‘INT’,‘SB’,‘15978618’,‘15978618’,‘15978618’,‘SB’]

Khalid Saeed

Pleas make sure you find the entire query that has been shown on X-VIEW. Because the Jennifer divided query with over 32k to save. The query stays at the log of Jennfier server. If you click the left side tool bar, a window would pop up. Select the tool→ query build, and input SQL, param1, and param2, you will be able to see the entirely binded query.

Guest User

while using the WebSphere Commerce, query and bide variables are quite long. Will it be possible to get the Full SQL and all the combined variables? And could I make query build possible to use under the current environment?

Khalid Saeed

If the query is over 32k, you may not get the result you expect because it would be split up. But you can recreate the original query most of the time. If things have not worked out, please send your param1 and param2. It may take a while to bind the query if there are mark such as # or $ inside the query. The Jennifer changes parameters into such marks.

Guest User

Thanks. Executed time: 12:51:34 283 response time: 7,210
SQL query:
SELECT CATENTRY_ID 
, PARTNUMBER 
, BUSCHN_ID 
, HEADNAME 
, VENDOR_ID 
, BRAND_KOR_NM 
, BRAND_CD 
, ITNCATENTRYNM 
, DISP_TYPE_CD 
, CATENTTYPE 
, PRIOD_DLVR_YN 
, DLVREXPNSCALCTYPE 
, ADLT_GOODS_YN 
, DCCP_EXCPT_YN 
, CHC_CHILD_YN 
, DEFSORT 
, DEFSORTCNT 
, JOIN_CNT 
, SCORE 
, CO_BUY_ID 
, EVERYCPEXCPTYN 
, CARDCHRGDCEXCPTYN 
, LUMPSUMDCEXCPTYN 
, MULTI_CD 
, GOODEVALYN 
, FC_GOODS_PROM_DC (B.BUSCHN_ID, FC_PARSER(‘$’||B.PRICEOFFERPRICE, #, ‘$’), B.CATENTRY_ID, B.MULTI_CD) FC_DC_VAL 
, CATALOG_ID 
, CATALOG_NM 
, CAT1_CD 
, CAT1_NAME 
, CAT2_CD 
, CAT2_NAME 
, CAT3_CD 
, CAT3_NAME 
, CAT4_CD 
, CAT4_NAME 
, CAT5_CD 
, CAT5_NAME 
, INIT_REGI_DTTM 
, ALLI_MALL_GOODS_CD 
, DUTY_USE_DUR 
, MM_RNTAL_PRC 
, MIN_CNTRB_PFRATE 
, CAT_NAME 
, PRICEOFFERPRICE 
, CARD_BNFT 
, GIFT_BNFT 
, CASE WHEN B.DISP_TYPE_CD = ‘$’ 
THEN FC_GET_PACK_DLVR_FEE(B.BUSCHN_ID, B.CATENTRY_ID, B.MULTI_CD, ?, FC_PARSER(‘$’||B.PRICEOFFERPRICE, #, ‘$’)) 
ELSE FC_GET_GOODS_DLVR_FEE(B.BUSCHN_ID, B.CATENTRY_ID, B.MULTI_CD, ?, FC_PARSER(‘$’||B.PRICEOFFERPRICE, #, ‘$’)) 
END DLVR_PRICE 
, ‘$’ CO_CD 
, ‘$’ DEVICE_CHN_ID 
, DECODE) AS PRC_SORT 
, ‘$’ AS ORDER_YN 
FROM (SELECT CATENTRY_ID 
, PARTNUMBER 
, BUSCHN_ID 
, HEADNAME 
, VENDOR_ID 
, BRAND_KOR_NM 
, BRAND_CD 
, ITNCATENTRYNM 
, DISP_TYPE_CD 
, CATENTTYPE 
, PRIOD_DLVR_YN 
, DLVREXPNSCALCTYPE 
, ADLT_GOODS_YN 
, DCCP_EXCPT_YN 
, CHC_CHILD_YN 
, DEFSORT 
, DEFSORTCNT 
, JOIN_CNT 
, NVL AS SCORE 
, CO_BUY_ID 
, EVERYCPEXCPTYN 
, CARDCHRGDCEXCPTYN 
, LUMPSUMDCEXCPTYN 
, MULTI_CD 
, GOODEVALYN 
, CATALOG_ID 
, CATALOG_NM 
, CAT1_CD 
, CAT1_NAME 
, CAT2_CD 
, CAT2_NAME 
, CAT3_CD 
, CAT3_NAME 
, CAT4_CD 
, CAT4_NAME 
, CAT5_CD 
, CAT5_NAME 
, INIT_REGI_DTTM 
, ALLI_MALL_GOODS_CD 
, DUTY_USE_DUR 
, MM_RNTAL_PRC 
, MIN_CNTRB_PFRATE 
, SUBSTR-#) AS CAT_NAME 
, FC_GOODS_PRC_DC (A.BUSCHN_ID, ?, NULL, A.CATENTRY_ID, A.MULTI_CD, NULL, NULL, ?) AS PRICEOFFERPRICE 
, FC_GET_OFFER_CARDEVENT_BATCH (A.BUSCHN_ID, ?, SYSDATE, A.CATENTRY_ID, A.MULTI_CD, ?) AS CARD_BNFT 
, ‘$’ AS GIFT_BNFT 
FROM (SELECT 
/+ LEADING USE_NL(XD XM) INDEX 
XD.CATENTRY_ID 
, XD.PARTNUMBER 
, XD.BUSCHN_ID 
, FC_HEADNM(XD.CATENTRY_ID) AS HEADNAME 
, NVL (XD.VENDOR_ID, #) VENDOR_ID 
, NVL (XD.BRAND_NM, ‘$’) BRAND_KOR_NM 
, NVL (TO_CHAR (XD.BRAND_CD), ‘$’) BRAND_CD 
, XD.NAME AS ITNCATENTRYNM 
, NVL (XD.DISP_TYPE_CD, ‘$’) DISP_TYPE_CD 
, NVL (XD.CATENTTYPE, ‘$’) CATENTTYPE 
, NVL (XD.PRIOD_DLVR_YN, ‘$’) PRIOD_DLVR_YN 
, NVL (XD.OUTGO_LOC_CD, ‘$’) OUTGO_LOC_CD 
, NVL (XD.DLVR_WAY_CD1, ‘$’) DLVR_WAY_CD1 
, NVL (XD.AGREE_DLINE, ‘$’) AGREE_DLINE 
, NVL (XD.ADLT_GOODS_YN, ‘$’) ADLT_GOODS_YN 
, NVL (XD.DCCP_EXCPT_YN, ‘$’) DCCP_EXCPT_YN 
, NVL (XD.CHC_CHILD_YN, ‘$’) CHC_CHILD_YN 
, NVL (XD.STYLE_MNG_YN, ‘$’) STYLE_MNG_YN 
, XD.CLICK_NUM AS DEFSORT 
, XD.RANK_QTY AS DEFSORTCNT 
, XD.JOIN_CNT 
, XD.STAR AS SCORE 
, ‘$’ AS CO_BUY_ID 
, NVL (XD.EVERYCPEXCPTYN, ‘$’) AS EVERYCPEXCPTYN 
, NVL (XD.CARDCHRGDCEXCPTYN, ‘$’) AS CARDCHRGDCEXCPTYN 
, NVL (XD.LUMPSUMDCEXCPTYN, ‘$’) AS LUMPSUMDCEXCPTYN 
, XD.MULTI_CD 
, NVL (XD.ALLI_MALL_GOODS_CD, ‘$’) AS ALLI_MALL_GOODS_CD 
, NVL (XD.DUTY_USE_DUR, #) AS DUTY_USE_DUR 
, NVL (XD.MM_RNTAL_PRC, #) AS MM_RNTAL_PRC 
, ‘$’ AS GOODEVALYN 
, XD.CATALOG_ID 
, XD.CATALOG_NM 
, XD.CAT1_CD 
, XD.CAT1_NAME 
, XD.CAT2_CD 
, XD.CAT2_NAME 
, XD.CAT3_CD 
, XD.CAT3_NAME 
, XD.CAT4_CD 
, XD.CAT4_NAME 
, XD.CAT5_CD 
, XD.CAT5_NAME 
, XD.INIT_REGI_DTTM 
, XM.MIN_CNTRB_PFRATE 
, NVL AS DLVREXPNSCALCTYPE 
, DECODE || DECODE || DECODE || DECODE || XD.CAT5_NAME AS CAT_NAME 
FROM XCATENTRY_DISP XD 
, XMD_MD_PFRATE_MNG_DTL XM 
WHERE XD.MDCD = XM.MD_CD(+) 
AND XD.SALESTAT = ‘$’ 
AND CATENTTYPE NOT IN (‘$’, ‘$’) 
AND XD.CATENTRY_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
AND XD.FLAG <> ‘$’ 
AND XD.BUSCHN_ID = FC_GET_CHNCD (XD.CATENTRY_ID, ?) 
AND DECODE = ‘$’ 
)A )B 
WHERE #=# 
AND # = CASE 
WHEN DISP_TYPE_CD IN (#, #, #, #, #, #, #, #, #) 
THEN # 
WHEN FC_PARSER(‘$’||B.PRICEOFFERPRICE, #, ‘$’) <= # 
THEN # 
ELSE # 
END 
ORDER BY DEFSORTCNT ASC, INIT_REGI_DTTM DESC
parameter 1: ,,4,,,20,,,1,,,,,1,,,110,INT,45,57,,,2,,,Y,0,0,> etcV,1,,0,,,,,,,,,N,,,,,N,N,0,,0,0,P,,, > ,, > ,, > ,, > ,ON,CHC,CHL,D,11110,FLASH,NOTFLASH,NOTFLASH,1,1,1,35,40,45,50,55,56,57,58,59,1,,,2,,,0,0,1,
parameter2: ‘110’,‘110’,‘INT’,‘110’,‘INT’,‘110’,‘15971651’,‘15971651’,‘15971616’,‘15971616’,‘15723121’,‘16087526’,‘16087526’,‘16378494’,‘16373473’,‘16367539’,‘16341683’,‘16341538’,‘16341532’,‘16341528’,‘16337730’,‘16320395’,‘16312813’,‘16312813’,‘16312811’,‘16312811’,‘16312768’,‘16312768’,‘16310806’,‘16309771’,‘16309770’,‘16309769’,‘16307041’,‘16306787’,‘16305511’,‘16305510’,‘16305509’,‘16305506’,‘16305370’,‘16305309’,‘16305308’,‘16305307’,‘16304924’,‘16304789’,‘16304788’,‘16301699’,‘16299101’,‘16299050’,‘16298984’,‘16298887’,‘16297035’,‘16296700’,‘16296112’,‘16295921’,‘16295244’,‘16295158’,‘16294585’,‘16294577’,‘16294503’,‘16294461’,‘16294445’,‘16294433’,‘16294433’,‘16294429’,‘16294428’,‘16293967’,‘16293967’,‘16293621’,‘16293621’,‘16293518’,‘16293518’,‘16293440’,‘16293440’,‘16293175’,‘16293175’,‘16292605’,‘16292605’,‘16292486’,‘16292486’,‘16290389’,‘16290388’,‘16288484’,‘16288483’,‘16288481’,‘16288466’,‘16288396’,‘16288305’,‘16288212’,‘16288145’,‘16288143’,‘16287979’,‘16287942’,‘16287888’,‘16287873’,‘16287871’,‘16287869’,‘16287868’,‘16287705’,‘16287568’,‘16285753’,‘16285741’,‘16285057’,‘16285020’,‘16285003’,‘16284689’,‘16284681’,‘SB’

Khalid Saeed

it seems an error occurs in the process of changing the parameter into the mark. The result is supposed to be like below.
FC_GOODS_PROM_DC (B.BUSCHN_ID, FC_PARSER(‘,’||B.PRICEOFFERPRICE, 4, ‘,’), B.CATENTRY_ID, B.MULTI_CD)
In this case, the Jennifer would conduct parsing like below.
FC_GOODS_PROM_DC (B.BUSCHN_ID, FC_PARSER(‘$’||B.PRICEOFFERPRICE, #, ‘$’), B.CATENTRY_ID, B.MULTI_CD)
“,” is actually parsed value, however, the mark holds the parameter after it has been changed. This is why you see the result of “,,4,,,”

It is supposed to be shown as “,”,4,xxx. But the software failed to recognize between actual value and delimiter. It may take a while to come up with a patch. We may not able to fix the bug because the patch could impact on backward compatibility. Sorry for the inconvenience. We will put more thoughts to fix the error if you could send the detail to nomin@jennifersoft.com.