Databse Error: in Datawindow object - PB 8.0

In PB 8.0 I am adding a column to a data window consisting of 3 retrieval arguments. I am not able to save the datawindow with the new column added. It says Database error: No detailed message. Please help it is very urgent.
georgeroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaniProCommented:
Do you use the designer painter or you are writing the SQL directly?
Wath is the data type of the column the you are adding to the DW?
Please tell me more detail.
0
georgeroAuthor Commented:
I was using the SQL directly. The following is my SQL. It looks like the In-line view v_status in the query is a problem. Do you know, how can we get around it? It was working fine with 6.5 not in 8

SELECT decode(:account_type,'BUYER',"PRD_SERV_PROVIDER_XREF"."SERV_PROVIDER_PRODUCT_DSC","PRODUCT"."PRODUCT_DSC") ,
 "PRD_ACCT_CMP_GRP"."COMPONENT_GROUP_CD",
 "PRD_ACCT_CMP_GRP"."CUST_CMP_GRP_NAME",
 "PRD_ACCT_CMP_GRP"."APPLICATION_CODE",
 "PRD_ACCT_CMP_GRP"."ACCESS_METHOD_CODE",
 "PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL" as phone_number,
 "PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL" as home_800_number,
 "PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL" as default_number,
 DECODE(:view_pin, 'Y', "PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL", SUBSTR("PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL", 1, 10)||'****') as calling_card_number,
 DECODE("PRD_ACCT_CMP_GRP"."COMPONENT_GROUP_CD", 'DU', DECODE("V_STATUS"."STATUS_DATE", NULL, 'PENDING',"V_STATUS"."STATUS"),"V_STATUS"."STATUS")  STATUS,
 "V_STATUS"."STATUS_DATE",
 DECODE("PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL", "PRD_ACCT_CMP_GRP"."LEC_BTN",'Y','N') AS LEC_BTN_IND,
"PRD_ACCT_CMP_GRP"."COMPONENT_GRP_VAL",
 "PRODUCT_ACCT"."PRODUCT_CODE",
 "PRD_ACCT_CMP_GRP"."PRODUCT_ACCOUNT_ID" ,
 "PRD_ACCT_CMP_GRP"."OCCURRENCE_NUMBER",
'N' as TPV_stage_ind
FROM "PRODUCT" ,
 "PRD_ACCT_CMP_GRP" ,
 "PRODUCT_ACCT" ,
 "PRD_SERV_PROVIDER_XREF",
 (SELECT product_account_id, occurrence_number,
             DECODE(SUM(DECODE(SIGN(NVL(bill_eff_end_date, to_date('12/31/4444','MM/DD/YYYY'))-sysdate), 1, 1, 0)),
               0, 'DISCONNECT', decode(sum(decode(sign(bill_eff_bgn_date - NVL(bill_eff_end_date,to_date('12/31/4444','MM/DD/YYYY')) ), 0,0,1)),0,'DISCON PEND','ACTIVE')) status,
             DECODE(SUM(DECODE(SIGN(NVL(bill_eff_end_date, to_date('12/31/4444','MM/DD/YYYY'))-sysdate), 1, 1, 0)),
              0, MAX(bill_eff_end_date), MAX(bill_eff_bgn_date)) status_date
             FROM        prd_acct_cmp
            WHERE product_account_id IN             (SELECT product_account_id      
                               FROM product_acct
                               WHERE customer_acct_id =:customer_acct_id)
                                GROUP BY product_account_id, occurrence_number) "V_STATUS"
WHERE ( "PRODUCT"."PRODUCT_CODE" = "PRODUCT_ACCT"."PRODUCT_CODE" ) and
 ( "PRD_ACCT_CMP_GRP"."PRODUCT_ACCOUNT_ID" = "PRODUCT_ACCT"."PRODUCT_ACCOUNT_ID" ) and
 ( "V_STATUS"."PRODUCT_ACCOUNT_ID" = "PRD_ACCT_CMP_GRP"."PRODUCT_ACCOUNT_ID" ) and
 ( "V_STATUS"."OCCURRENCE_NUMBER" = "PRD_ACCT_CMP_GRP"."OCCURRENCE_NUMBER" ) and
 ("PRD_SERV_PROVIDER_XREF"."PRODUCT_ID"(+) ="PRODUCT"."PRODUCT_ID")
0
DaniProCommented:
What kind of database are you using? I can't execute ."PRODUCT_ID"(*).
You can try to create the view in permanent mode in your db and refer to it directly in the where clause without the SQL that generate the view.
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

georgeroAuthor Commented:
I am unsing Oracle. I cannot create the view in permanent mode 'cos the view is going to vary based on the customer acct id I have in GUI. Also, many users might be accessing the view concurrently. That again is going to be a problem.
0
DaniProCommented:
Then you can try to create a stored procedure that create the view and after execute the query. In the DW you must only call the stored procedure with the appropriate parameter
0
Vikas_DixitCommented:
Which col are you trying to add ??
And does your current SQL works fine ??

Vikas
0
berXpertCommented:
Hi,

I got the same error on Sybase when you work with a temporary table, those named begining with #.

In Pb 6.5 you can create a temporary table, build a datawindow, save it, open it, change it and every thing is OK

In Pb 8.0 there is a reported bug where you can only create a new datawindow based on a temporary table and save it, but if you open it and try to change like add a column it does not save and send the message " Database error: No detailed message "

So I guess your table "V_STATUS" is accting like sybase's #temp_tables

To solve your problem you have to options:

A. build a new datawindow with your new query every time you change it. Bad thing is you need to reformat your DW

or

B. export your datawindow and edit its source code, there you can work on your query. Next just import it and your dw will have your new fields keeping its format. Bad thing is more dificult to work on source code than in painter.



BerX
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.