SQL using @prompt in custom query script in IDT - Data mismatch

[former_member223477]

Hello ,

We are getting different values for the same sql while using @prompt in BI 4.0 in IDT ( sp2 patch 12)

--------------------------------------------------------------------------------------------------------------------------------------------
Objects created with the following sql with hard coded material code

SELECT   DISTINCT "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0PROFIT_CTR"
FROM
"CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL" INNER JOIN
"CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL" ON
("CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL"."0MATERIAL"="CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0MATERIAL")
WHERE
"CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL"."0MATERIAL" = 'TT02'
AND "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0PROFIT_CTR" =  '0000204712'

AND "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."REFERENCE_DATE" =  '2012-03-28'

  --output

One value 0000204712

  ---------------------------------------------------------------------------

when using prompt and passed the same material code

SELECT
  DISTINCT "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0PROFIT_CTR"
FROM
"CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL" INNER JOIN
"CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL" ON
("CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL"."0MATERIAL"="CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0MATERIAL")
WHERE
"CN_ZMPROVPL_02"."PUBLIC"."D0MATERIAL"."0MATERIAL" =@prompt('Enter Material
Code:','A',,,) // Passing TT02
AND "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."0PROFIT_CTR" =  '0000204712'
AND "CN_ZMPROVPL_02"."PUBLIC"."IZMPROVPL"."REFERENCE_DATE" =  '2012-03-28'

output same value 0000204712 multiple times

Can anyone explain the reason for that ?

thanks,

Krishna reddy

[Adam_Stone]

If you monitor the database to see what SQL it receives, do you see a difference there?  Does it return the same rows in both cases from there?