Prompt will not execute 1

[DugsDMan]

Has anyone ever had a problem with the prompt window not going away, not allowing the SQL to run?

I have a report I'm trying to create/run, but when the prompt window comes up, it won't go away. It populates the values properly, and after all 3 have been selected, the OK button is enabled. But, after I click on OK, nothing happens.

Here's one of the queries I'm trying to use:

SELECT BILL_DATE, FEATURE_GROUP, JURISDICTION, DATA_CENTER, DATA_CENTER_NAME, SUM(USAGE_QUANTITY) AS USAGE
FROM TABLE1.USAGE,
TABLE2.DATA_CENTERS
WHERE (SUBSTR(BAN, 1, 3)  = SPECIAL_TABLES.CABS_DATA_CENTERS.NPA)
AND (SUBSTR(ENDOFFICE, 5, 2) = 'IL' OR SUBSTR(ENDOFFICE, 7, 1) = 'X')
AND EXTRACT(DAY FROM BILL_DATE) = @Prompt ('Select Bill Period Day:', 'D', {'05', '15', '25'}, MONO, CONSTRAINED) 
AND EXTRACT(MONTH FROM BILL_DATE) = @Prompt ('Select Bill Period Month:', 'D', {'01', '02', '03','04','05','06','07','08','09','10','11','12'}, MONO, CONSTRAINED) 
AND EXTRACT(YEAR FROM BILL_DATE) = @Prompt ('Select Bill Period Year:', 'D', {'2003', '2004', '2005','2006','2007','2008','2009','2010'}, MONO, CONSTRAINED) 
AND FEATURE_GROUP IN ('B','C','D')
AND RECORD_TYPE = '3505'
AND ((JURISDICTION = 'A' AND LOCAL_TRANSPORT_RATE_ELEM_IND = '9')
          OR (JURISDICTION = 'R' AND LOCAL_TRANSPORT_RATE_ELEM_IND = '1'))
GROUP BY 1, 2, 3, 4, 5

 

[Sridharan]

DugsDMan,

It will happen if there is some problem with the way you have constructed the prompts. The problem which I foresee in your case is to do with 'D' in the prompt since you are not getting a date but a number for Day, Month and Year. So try changing the prompt definitions second parameter from D to N or C accordingly based on what EXTRACT returns and check if that helps. I'm sure it will.

Good Luck
Sri

 

[DugsDMan]

Thanks Sri! That has it going now. I guess that's what I get for copying & pasting that prompt from another report. It worked fine in the other one, but I was receiving a date.

Thanks again!