Solved

Databse Error: in Datawindow object - PB 8.0

Posted on 2003-11-12
9
633 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DirectXFramework 5 106
Windows 8 - Determine what application / process "owns" the F11 key. 4 63
error when i compile 5 575
debug as  junit test 4 73
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…
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

861 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

29 Experts available now in Live!

Get 1:1 Help Now