Solved

Databse Error: in Datawindow object - PB 8.0

Posted on 2003-11-12
9
627 Views
Last Modified: 2013-12-26
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.
0
Comment
Question by:georgero
9 Comments
 
LVL 6

Expert Comment

by:DaniPro
Comment Utility
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
 

Author Comment

by:georgero
Comment Utility
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
 
LVL 6

Expert Comment

by:DaniPro
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:georgero
Comment Utility
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
 
LVL 6

Expert Comment

by:DaniPro
Comment Utility
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
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
Which col are you trying to add ??
And does your current SQL works fine ??

Vikas
0
 
LVL 5

Accepted Solution

by:
berXpert earned 125 total points
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now