Solved

Databse Error: in Datawindow object - PB 8.0

Posted on 2003-11-12
9
634 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
ID: 9738031
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
ID: 9738049
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
ID: 9738160
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:georgero
ID: 9738185
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
ID: 9738241
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
ID: 9738736
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
ID: 9751959
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

785 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