Mateen
asked on
Help in a Query/Retrieve technique.
Please ignore my question
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
To query/retrieve I have written a script in itemchanged event (BASE DATAWINDOW)
// SCRIPT STARTS
idw_super = this
IF UPPER(dw_master.Object.dat
string ls_colname,ls_coltype,ls_s
ls_colname = dwo.Name
if Pos (UPPER(IL_sql_syntax), "WHERE")=0 then
il_sql_syntax += ' WHERE '
ls_sql_syntax = il_sql_syntax
else
il_sql_syntax += ' and '
ls_sql_syntax = il_sql_syntax
end if
ls_coltype = this.Describe(ls_colname + ".coltype")
CHOOSE CASE mid(ls_coltype,1,4)
CASE "char"
il_sql_syntax += ' ' + ls_colname +' like ' +"'"+"%"+data+"%'"
CASE "numb","deci"
il_sql_syntax += ' CAST(' + ls_colname + " AS DECIMAL ) =" + data
CASE "date"
if mid(data,5,1) = '/' or mid(data,5,1) = '-' then
data = data
else
data = right(data,4)+"/"+mid(data
end if
date ld_date
ld_date = date(data)
string str1
str1 = string(ld_date,"yyyy/mm/dd
Il_sql_syntax += ' '+ "convert(char(10),"+ls_col
END CHOOSE
if lstr_window_info.company_c
il_sql_syntax += " and company_code ='"+lstr_window_info.compa
end if
ls_sql_syntax = trim(ls_sql_syntax)
if right(UPPER(ls_sql_syntax)
ls_sql_syntax = mid(ls_sql_syntax,1,len(ls
END IF
END IF
RETURN 0
// SCRIPT ENDS
Suppose a dw is made out of a table bonus(trans_no string,trans_date date,item_code varchar,company_code varchar)
Suppose a drop down dw is associated to item_Code by doing this
a) clicked copy in item_code
b) pasted in blank area of dw.
c) rename the column item_code as item_name
d) associated a drop down in item_name
If I write anything in column item_name it will fill the column item_code with the associated item_code
WHAT DOES THE ITEM CHANGED EVENT IS DOING.
--------------------------
It is building sql select.
If the user writes 'Item01' in the field item_code (not item_name) the sql select built will be
"select bonus.trans_no,bonus.trans
from bonus
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_code like '%0004%'"
MOST IMPORTANT OR PROBLEM LINES
--------------------------
ls_colname = dwo.Name // in the itemchanged event
and item_code like '%0004%' // in the sql select built by the itemchanged event.
Since Item_code is an actual column of the table bonus the sql select will be parsed and run without any error.
If the user writes 'Item01' in the field item_name (not item_code)
then the last line of the sql select will be
and item_name like '%0004%'
Since item_name column is not present in the table bonus the sql select will not be parsed and there will be error
[invalid column name 'item_name'].
Conclusion.
The query / retrieve runs successfully if the column names are actual.
It fails if the column names are virtual. (For all drop down columns)
Can any thing be done to run the query for virual columns as well.
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
To query/retrieve I have written a script in itemchanged event (BASE DATAWINDOW)
// SCRIPT STARTS
idw_super = this
IF UPPER(dw_master.Object.dat
string ls_colname,ls_coltype,ls_s
ls_colname = dwo.Name
if Pos (UPPER(IL_sql_syntax), "WHERE")=0 then
il_sql_syntax += ' WHERE '
ls_sql_syntax = il_sql_syntax
else
il_sql_syntax += ' and '
ls_sql_syntax = il_sql_syntax
end if
ls_coltype = this.Describe(ls_colname + ".coltype")
CHOOSE CASE mid(ls_coltype,1,4)
CASE "char"
il_sql_syntax += ' ' + ls_colname +' like ' +"'"+"%"+data+"%'"
CASE "numb","deci"
il_sql_syntax += ' CAST(' + ls_colname + " AS DECIMAL ) =" + data
CASE "date"
if mid(data,5,1) = '/' or mid(data,5,1) = '-' then
data = data
else
data = right(data,4)+"/"+mid(data
end if
date ld_date
ld_date = date(data)
string str1
str1 = string(ld_date,"yyyy/mm/dd
Il_sql_syntax += ' '+ "convert(char(10),"+ls_col
END CHOOSE
if lstr_window_info.company_c
il_sql_syntax += " and company_code ='"+lstr_window_info.compa
end if
ls_sql_syntax = trim(ls_sql_syntax)
if right(UPPER(ls_sql_syntax)
ls_sql_syntax = mid(ls_sql_syntax,1,len(ls
END IF
END IF
RETURN 0
// SCRIPT ENDS
Suppose a dw is made out of a table bonus(trans_no string,trans_date date,item_code varchar,company_code varchar)
Suppose a drop down dw is associated to item_Code by doing this
a) clicked copy in item_code
b) pasted in blank area of dw.
c) rename the column item_code as item_name
d) associated a drop down in item_name
If I write anything in column item_name it will fill the column item_code with the associated item_code
WHAT DOES THE ITEM CHANGED EVENT IS DOING.
--------------------------
It is building sql select.
If the user writes 'Item01' in the field item_code (not item_name) the sql select built will be
"select bonus.trans_no,bonus.trans
from bonus
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_code like '%0004%'"
MOST IMPORTANT OR PROBLEM LINES
--------------------------
ls_colname = dwo.Name // in the itemchanged event
and item_code like '%0004%' // in the sql select built by the itemchanged event.
Since Item_code is an actual column of the table bonus the sql select will be parsed and run without any error.
If the user writes 'Item01' in the field item_name (not item_code)
then the last line of the sql select will be
and item_name like '%0004%'
Since item_name column is not present in the table bonus the sql select will not be parsed and there will be error
[invalid column name 'item_name'].
Conclusion.
The query / retrieve runs successfully if the column names are actual.
It fails if the column names are virtual. (For all drop down columns)
Can any thing be done to run the query for virual columns as well.
ASKER
Hi dia
Unfortunately, my itemchanged code is ancestor code.
Isn't it possible to know that the current column is a virtual one and then
isn't there any techique to know the real one against this virual one.
Unfortunately, my itemchanged code is ancestor code.
Isn't it possible to know that the current column is a virtual one and then
isn't there any techique to know the real one against this virual one.
ASKER
I mean , can datacolumn or displaycolumn etc be used to distinguish dropdown column and actual column.
ASKER
<< or, if it is an ancestor code then in dws where ur using alias columns with query mode u can copy the code in ur descendant script and do the above changes... >>
This would not be dynamic. I have so many windows and datawindows.
This would not be dynamic. I have so many windows and datawindows.
ASKER
Hi dia,
Since I initially take item_code
then I copy and rename that column item_name
I feel that there must be some way to know item_name as drop-down-column name
and item_code as its parent column name.
Since I initially take item_code
then I copy and rename that column item_name
I feel that there must be some way to know item_name as drop-down-column name
and item_code as its parent column name.
ASKER
There was an error in the question.
please replace
like '%0004%'"
with
like '%item01%'
please replace
like '%0004%'"
with
like '%item01%'
HIT MATEEN
this is trick only and it will work if you are not using any update on this sql
try this sql and you can modify it also as you like
SELECT TRANS_NO,
TRANS_DATE,
ITEM_CODE,
ITEM_NAME,
COMPANY_CODE
FROM (
SELECT TRANS_NO,
TRANS_DATE,
ITEM_CODE,
ITEM_CODE AS ITEM_NAME,
COMPANY_CODE
FROM BONUS)
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_NAME like '%ITEM01%'"
THIS SHOULD SOLVE YOUR PROBLEM
IF YOU WANT MUCH ON THIS LET US KNOW
BEST OF LUCK
GAJENDER
this is trick only and it will work if you are not using any update on this sql
try this sql and you can modify it also as you like
SELECT TRANS_NO,
TRANS_DATE,
ITEM_CODE,
ITEM_NAME,
COMPANY_CODE
FROM (
SELECT TRANS_NO,
TRANS_DATE,
ITEM_CODE,
ITEM_CODE AS ITEM_NAME,
COMPANY_CODE
FROM BONUS)
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_NAME like '%ITEM01%'"
THIS SHOULD SOLVE YOUR PROBLEM
IF YOU WANT MUCH ON THIS LET US KNOW
BEST OF LUCK
GAJENDER
hi,
how about trying this workaround... it will help u to know if ur column is virtual or actual... and u can continue ur logic from there on....
Long ll_Ret
ll_Ret = POS(il_sql_syntax,ls_colna
If ll_Ret = 0 Then
MessageBox('Mateen','I have found ur virtual column.')
End If
//the above code check thru ur SQL statement for the column name u have entered in... if that column is not in ur SQL then its a virtual column else its an actual column...
Let me know if it helps and if u need any more assistance...
Cheers,
Rosh
how about trying this workaround... it will help u to know if ur column is virtual or actual... and u can continue ur logic from there on....
Long ll_Ret
ll_Ret = POS(il_sql_syntax,ls_colna
If ll_Ret = 0 Then
MessageBox('Mateen','I have found ur virtual column.')
End If
//the above code check thru ur SQL statement for the column name u have entered in... if that column is not in ur SQL then its a virtual column else its an actual column...
Let me know if it helps and if u need any more assistance...
Cheers,
Rosh
ASKER
Hi dia,
the idea is there but need some clearance.
a) il_sql_syntax is being built by item_changed_event code. Hence , it will never return 0
b) After finding virtual column I must find the actual column to retrieve data.
c) I cannot afford ignoring virtual column while building sql syntax because for the user
using dropdown column is easier that puttin actual code.
Hi gajender99:
By what technique I will get the words
ITEM_CODE AS ITEM_NAME
in the item_changed event of ancesstor dw.
the idea is there but need some clearance.
a) il_sql_syntax is being built by item_changed_event code. Hence , it will never return 0
b) After finding virtual column I must find the actual column to retrieve data.
c) I cannot afford ignoring virtual column while building sql syntax because for the user
using dropdown column is easier that puttin actual code.
Hi gajender99:
By what technique I will get the words
ITEM_CODE AS ITEM_NAME
in the item_changed event of ancesstor dw.
if you cut this sql and paste in the datawindow and it will do for you
if your run this sql directly you will come to know how it works
Gajender
if your run this sql directly you will come to know how it works
Gajender
you have to include this in your sql
ASKER
Hi gagendar99:
If the user is putting something in a column which is drop down column here item_name then
the task is to know by some scripting , functions etc that this column item_name is virtual column. After that, the task is to know its actual column name.
Since both columns are there in the dw one (item_code as sql part) and second (item_name by copy pasting item_code and renaming as item_name) there must be some technique to identify them seperately.
If the user is putting something in a column which is drop down column here item_name then
the task is to know by some scripting , functions etc that this column item_name is virtual column. After that, the task is to know its actual column name.
Since both columns are there in the dw one (item_code as sql part) and second (item_name by copy pasting item_code and renaming as item_name) there must be some technique to identify them seperately.
if you use in the sql then it will no longer be treated as virtual column by powerbuilder the only thingis you cannot update the table with this SQL
Gajender
Gajender
Hi,
a) "select bonus.trans_no,bonus.trans
from bonus
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_code like '%0004%'"
in the above query pasted by u i cannot see item_name... so i understand that the virtual columns are not part of ur SQL statement and hence the error is prompting... so POS will return 0 for a virtual column... just try it out...
b) & c) i completely agree with u ...
i guess u can get the actual name by ...
ls_colname = dw_control.Object.item_nam
Let me know if u need more assistance...
Cheers,
Rosh
a) "select bonus.trans_no,bonus.trans
from bonus
where 1=1
and convert(char(10),trans_dat
and company_code = '02'
and item_code like '%0004%'"
in the above query pasted by u i cannot see item_name... so i understand that the virtual columns are not part of ur SQL statement and hence the error is prompting... so POS will return 0 for a virtual column... just try it out...
b) & c) i completely agree with u ...
i guess u can get the actual name by ...
ls_colname = dw_control.Object.item_nam
Let me know if u need more assistance...
Cheers,
Rosh
ASKER
Hi gagendar99
Your code will certainly work but I don't want to put any thing fixed any where. The whole sql select is being built dynamically in the itemchanged event.
Your code will certainly work but I don't want to put any thing fixed any where. The whole sql select is being built dynamically in the itemchanged event.
ASKER
Hi dia
I did say in my question
<<If the user writes 'Item01' in the field item_name (not item_code)
then the last line of the sql select will be
and item_name like '%Item01%' >>.
I am always picking column name from
ls_colname = dwo.Name
I have got one thing to work on
ls_colname = dw_control.Object.item_nam
I will work on this and let u know.
Meanwhle , please keep thinking your ways.
Thanks
I did say in my question
<<If the user writes 'Item01' in the field item_name (not item_code)
then the last line of the sql select will be
and item_name like '%Item01%' >>.
I am always picking column name from
ls_colname = dwo.Name
I have got one thing to work on
ls_colname = dw_control.Object.item_nam
I will work on this and let u know.
Meanwhle , please keep thinking your ways.
Thanks
Hi mateen
yes try what you are saying
ls_colname = dw_control.Object.item_nam
this will always get you the right column name
to check for virtual name you can
if 'Item_name'<>ls_colname then
MessageBox("virtual","this
End if
ASKER
Your idea
ls_colname = dw_control.Object.item_nam
has given a break trhough.
I would need help many times while applying.
I tried
a)
ls_colname2 = 'trans_no'
ls_colname2 = this.object.trans_no.dbnam
messagebox('',ls_colname2)
I got "bonus.trans_no"
b) ls_colname2 = 'item_code'
ls_colname2 = this.object.item_code.dbna
messagebox('',ls_colname2)
c) ls_colname2 = 'item_name'
ls_colname2 = this.object.item_name.dbna
messagebox('',ls_colname2)
I got "bonus.item_code"
Problems:
1) Didn't get column name in b)
2) Don't know how to get dbname when colname is in variable
that is
ls_colname = dwo.name
ls_colname2 = this.object.variable ls_colname.dbname
ls_colname = dw_control.Object.item_nam
has given a break trhough.
I would need help many times while applying.
I tried
a)
ls_colname2 = 'trans_no'
ls_colname2 = this.object.trans_no.dbnam
messagebox('',ls_colname2)
I got "bonus.trans_no"
b) ls_colname2 = 'item_code'
ls_colname2 = this.object.item_code.dbna
messagebox('',ls_colname2)
c) ls_colname2 = 'item_name'
ls_colname2 = this.object.item_name.dbna
messagebox('',ls_colname2)
I got "bonus.item_code"
Problems:
1) Didn't get column name in b)
2) Don't know how to get dbname when colname is in variable
that is
ls_colname = dwo.name
ls_colname2 = this.object.variable ls_colname.dbname
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi,
'1) Didn't get column name in b)'
im surprised??? i just tried it and it works fine...
BUT,
but i had an observation... when i see for column item_code in column specifications of the dw i cannot see it in the list...
but i can see item_name with the dbname as item.item_code
anyways,... but,
b) ls_colname2 = 'item_code'
ls_colname2 = this.object.item_code.dbna
messagebox('',ls_colname2)
Shud return "bonus.item_code"
Cheers,
Rosh
'1) Didn't get column name in b)'
im surprised??? i just tried it and it works fine...
BUT,
but i had an observation... when i see for column item_code in column specifications of the dw i cannot see it in the list...
but i can see item_name with the dbname as item.item_code
anyways,... but,
b) ls_colname2 = 'item_code'
ls_colname2 = this.object.item_code.dbna
messagebox('',ls_colname2)
Shud return "bonus.item_code"
Cheers,
Rosh
ASKER
Hi dia
It seems that everything is fine now.
I have swapped ls_colname by this
ls_colname = dwo.Name
ls_colname2= this.Describe(ls_colname + ".dbName")
point_position = pos(ls_colname2,'.')
ls_colname=mid(ls_colname2
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
is also yours. Please put any comment there.
At the time, it all seems well. If , in future, i encounter any proble, may I post here 4 assistance.
It seems that everything is fine now.
I have swapped ls_colname by this
ls_colname = dwo.Name
ls_colname2= this.Describe(ls_colname + ".dbName")
point_position = pos(ls_colname2,'.')
ls_colname=mid(ls_colname2
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
is also yours. Please put any comment there.
At the time, it all seems well. If , in future, i encounter any proble, may I post here 4 assistance.
hi,
sure... u can post here...
by now u must have gauged i never back out in helping if its within my reach...
answering queries here at EE is a passion for me now...
if u want u can keep the post open....
Cheers,
Rosh
sure... u can post here...
by now u must have gauged i never back out in helping if its within my reach...
answering queries here at EE is a passion for me now...
if u want u can keep the post open....
Cheers,
Rosh
ASKER
Hi gagendar99
<<Hi mateen
yes try what you are saying
ls_colname = dw_control.Object.item_nam
tried and succeeded.
Please put a comment on
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
thanks 4 your help.
<<Hi mateen
yes try what you are saying
ls_colname = dw_control.Object.item_nam
tried and succeeded.
Please put a comment on
https://www.experts-exchange.com/questions/21433100/power-builder-7.html
thanks 4 your help.
ASKER
I love and respect EE experts.
there is logically no way of running the query for virtual columns... simply because the virtual column does not exist in the database so it cannot be handled...
what u can do is...
if ur itemchanged code is not an ancestor code u can simply check if ...
ls_colname = dwo.Name
If Upper(ls_colname) = 'ITEM_NAME' Then
ls_colname = 'item_code'
End If
or, if it is an ancestor code then in dws where ur using alias columns with query mode u can copy the code in ur descendant script and do the above changes...
Cheers,
Rosh