valiant_at_mci
asked on
Datawindow error
I'm getting an error I have never seen before.
Datawindow error
Select Error: Column lists do not match
I've not seen this error before.
The DW in question had its Owner changed (from DBO), was a Sybase DB, and now is an Oracle 8i Database.
No other properties other than a join and the ownership were changed in the Datawindow, now this error is poppin up. On both the native retrieve, and when run through the application.
Any help would be appreciated.
Datawindow error
Select Error: Column lists do not match
I've not seen this error before.
The DW in question had its Owner changed (from DBO), was a Sybase DB, and now is an Oracle 8i Database.
No other properties other than a join and the ownership were changed in the Datawindow, now this error is poppin up. On both the native retrieve, and when run through the application.
Any help would be appreciated.
This is a problem with you select statement. Are you using a union? If so, look that both sideds of the union (all) have the same number of columns and that the column types match in both type and position.
If you can post the Select statement I may be of more help.
If you can post the Select statement I may be of more help.
Try Export-Import
send DataWindow to me levrom@hotmail.com
I will examine it
send DataWindow to me levrom@hotmail.com
I will examine it
Have u tested the sql for the datawindow.
Does it works fine.
Please send the sql for DW .
Regards
vikas
Does it works fine.
Please send the sql for DW .
Regards
vikas
ASKER
I found the problem with the DW, and its not in the SQL or the pull of the DW itself its the use of the MODIFY function to change the SQL behind the scenes.
But the initial problem remains, as the solution I devised did not work.
Before calling the Modify function I changed the single quotes (') to double (") around certain items (listed below), and this actually got past the column error that is a result of a bad Modify call. The problem that arose from this change is that though the MODIFY function now modifies correctly, the DW gets an invalid Column error with Double "'s.
Catch 22. Change the SQL to use the modify function, but then the DW wont retrieve properly.
Columns Changing:
Select < Various Coumns>,
To_Number( To_Char(FAST.account_reque st.request ed_date, "mm") ),
To_Number( To_Char(FAST.account_reque st.request ed_date, "dd") ),
To_Number( To_Char(FAST.account_reque st.request ed_date, "yy") ),
InStr(FAST.account_request .status, "xndaper")
SQL comes from here:
is_orgselect = dw_list.Describe("DataWind ow.Table.S elect")
Changes are here:
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'mm'"), 4, "~"mm~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'dd'"), 4, "~"dd~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'yy'"), 4, "~"yy~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'xndaper'"), 9, "~"xndaper~"")
t_modstrg = "DataWindow.Table.Select=' " &
+ is_orgselect + t_where + "'"
The entire SQL of the DW becomes what is below:
SELECT FAST.ACCOUNT_REQUEST.~"SSO _LOGIN_ID~ " , ~"FAST~".~"ACCOUNT_REQUEST ~".~"CONNE CT_ID~" , ~"FAST~".~"ACCOUNT_REQUEST ~".~"REQUE STED_DATE~ " , To_Number( To_Char(FAST.account_reque st.request ed_date, "mm") ), To_Number( To_Char(FAST.account_reque st.request ed_date, "dd") ), To_Number( To_Char(FAST.account_reque st.request ed_date, "yy") ), ~"FAST~".~"ACCOUNT_REQUEST ~".~"STATU S~" , ~"FAST~".~"ACCOUNT_REQUEST ~".~"REQUE STOR_LOGIN _ID~" , ~"FAST~".~"SSO_USER~".~"LA ST_NAME~" , ~"FAST~".~"SSO_USER~".~"FI RST_NAME~" , ~"FAST~".~"SSO_USER~".~"MI DDLE_NAME~ " , ~"FAST~".~"SSO_USER~".~"FI N_DEPT_ID~ " , ~"FAST~".~"ACCOUNT_REQUEST ~".~"APPRO VER_LOGIN_ ID~" , ~"FAST~".~"SSO_USER~".~"SU _ID~" , ~"FAST~".~"ACCOUNT_REQUEST ~".~"STATU S_DATE~" , ~"FAST~".~"DEPARTMENT~".~" DEPT_NAME~ " , ~"FAST~".~"CONNECTION~".~" SHORT_DESC ~" , InStr(FAST.account_request .status,"x ndaper") FROM ~"FAST~".~"ACCOUNT_REQUEST ~" , ~"FAST~".~"SSO_USER~" , ~"FAST~".~"CONNECTION~" , ~"FAST~".~"DEPARTMENT~" WHERE ( ~"FAST~".~"SSO_USER~".~"FI N_DEPT_ID~ " = ~"FAST~".~"DEPARTMENT~".~" DEPT_NUMBE R~" (+)) and ( ~"FAST~".~"ACCOUNT_REQUEST ~".~"SSO_L OGIN_ID~" = ~"FAST~".~"SSO_USER~".~"SS O_LOGIN_ID ~" ) and ( ~"FAST~".~"ACCOUNT_REQUEST ~".~"CONNE CT_ID~" = ~"FAST~".~"CONNECTION~".~" CONNECT_ID ~" ) and (FAST.account_request.stat us in ("r", "e"))'
But the initial problem remains, as the solution I devised did not work.
Before calling the Modify function I changed the single quotes (') to double (") around certain items (listed below), and this actually got past the column error that is a result of a bad Modify call. The problem that arose from this change is that though the MODIFY function now modifies correctly, the DW gets an invalid Column error with Double "'s.
Catch 22. Change the SQL to use the modify function, but then the DW wont retrieve properly.
Columns Changing:
Select < Various Coumns>,
To_Number( To_Char(FAST.account_reque
To_Number( To_Char(FAST.account_reque
To_Number( To_Char(FAST.account_reque
InStr(FAST.account_request
SQL comes from here:
is_orgselect = dw_list.Describe("DataWind
Changes are here:
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'mm'"), 4, "~"mm~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'dd'"), 4, "~"dd~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'yy'"), 4, "~"yy~"")
is_orgselect = replace(is_orgselect, pos(is_orgselect, "'xndaper'"), 9, "~"xndaper~"")
t_modstrg = "DataWindow.Table.Select='
+ is_orgselect + t_where + "'"
The entire SQL of the DW becomes what is below:
SELECT FAST.ACCOUNT_REQUEST.~"SSO
ASKER
Forgot to mention. Still seeking some kind of solution to this. Fixing the initial error produces the DW Invalid Column error (Oracle 8i). So I am sort of stuck. Other than making another DW with the modified SQL as a base and switching out the properties on the one displayed.
valiant,
Changing the single quotes to double quotes will not fix your problem but just create another. The double quotes identify database objects. The single quotes designate the start and end of strings. This would generate the invalid column error. Oracle would assume "mm", "dd", "yy", "xndaper", "r" and "e" to all be names of columns.
These are the errors in your select SQL
1) To_Number( To_Char(FAST.account_reque st.request ed_date, "mm") ),
The "mm" should be 'mm'
2) To_Number( To_Char(FAST.account_reque st.request ed_date, "dd") ),
The "dd" should be 'dd'
3) To_Number( To_Char(FAST.account_reque st.request ed_date, "yy") ),
The "yy" should be 'yy'
4) InStr(FAST.account_request .status,"x ndaper")
The "xndaper" should be 'xndaper' though "xndaper" may be a column name.
5) FAST.account_request.statu s in ("r", "e"))'
The "r", "e" should be 'r', 'e' and the trailing ' should not be there
I think thats it.
A) Why do you think you need to change the single to double quotes?
B) Did you always manipulate the SQL prior to the retrieve?
c) Instr will return a number. Is this what your looking for in the last column expression?
Changing the single quotes to double quotes will not fix your problem but just create another. The double quotes identify database objects. The single quotes designate the start and end of strings. This would generate the invalid column error. Oracle would assume "mm", "dd", "yy", "xndaper", "r" and "e" to all be names of columns.
These are the errors in your select SQL
1) To_Number( To_Char(FAST.account_reque
The "mm" should be 'mm'
2) To_Number( To_Char(FAST.account_reque
The "dd" should be 'dd'
3) To_Number( To_Char(FAST.account_reque
The "yy" should be 'yy'
4) InStr(FAST.account_request
The "xndaper" should be 'xndaper' though "xndaper" may be a column name.
5) FAST.account_request.statu
The "r", "e" should be 'r', 'e' and the trailing ' should not be there
I think thats it.
A) Why do you think you need to change the single to double quotes?
B) Did you always manipulate the SQL prior to the retrieve?
c) Instr will return a number. Is this what your looking for in the last column expression?
ASKER
The DW actually has no criteria for when it was built (I am not the author).
Based on who is pulling information (their access level), certain filters are tacked on with a MODIFY command. The sinlge quotes will not go through the Modify command, and doubles aren't recognized by Oracle. The singles (along your theme), were the solution by prefacing them with ~~
Based on who is pulling information (their access level), certain filters are tacked on with a MODIFY command. The sinlge quotes will not go through the Modify command, and doubles aren't recognized by Oracle. The singles (along your theme), were the solution by prefacing them with ~~
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Column list defined in the datawindow doesn't match with the select command. The most often reasons:
1) Select contains * (all columns) and table was changed.
2) Select references view, original table was changed, and view was not recreated. The column types are shifted.
1) Select contains * (all columns) and table was changed.
2) Select references view, original table was changed, and view was not recreated. The column types are shifted.
Okay I just had the same/similar problem and then read through this. Based, on Hinks comment from Date: 10/31/2002 02:51PM PST, I derived a solution.
PB version 5. ( Iit old I know but we have a huge application that uses a Framework based on PB 5 that cannot be upgraded.)
My data and SQL ( actually a stored proc ) had both gone through several revisions. However, I did not want to lose all the formatting work in the existing dw. I never saw the problem while previewing, the dw from PB, only when I ran it from the App. A slight difference for me was my owner did not change....just had a lot of revisions in DW and SP.
Here is what I did. I decided to build a new temp DW. And then I compared the columns and data specs from the new one to the old one. Sure enough, there was a minor difference. Once I made the old DW match the new DW column specs, It worked fine.
Hope this helps others.
PB version 5. ( Iit old I know but we have a huge application that uses a Framework based on PB 5 that cannot be upgraded.)
My data and SQL ( actually a stored proc ) had both gone through several revisions. However, I did not want to lose all the formatting work in the existing dw. I never saw the problem while previewing, the dw from PB, only when I ran it from the App. A slight difference for me was my owner did not change....just had a lot of revisions in DW and SP.
Here is what I did. I decided to build a new temp DW. And then I compared the columns and data specs from the new one to the old one. Sure enough, there was a minor difference. Once I made the old DW match the new DW column specs, It worked fine.
Hope this helps others.
I PB, You don't need to create new datawindow, when datatypes are set wrongly. It is enough to change anything in its SQL select, for example add and a space in the command or deselect any column and select again. When You will return to design, the datatypes are OK.
I did not realize I missed this until I was cleaning out my email. Thanks hink I will try this net time.
BR
BR
ASKER