datawindow: SELECT

Hi,

I have this problem.

Suppose I have a table 'A' which has columns: ID, and description.  A
file 'B' which has 1 to 2000 IDs.

I want to select all of the rows in table 'A' that have IDs in file 'B'.

My solution was to add a tempory table name 'tmp' into the database
(note that the user who running the application do not have the right to
create or drop table).  Then before doing the selection, I delete all
row in table 'tmp', insert all IDs in file 'B' into it, then do a
subquery select like

SELECT ID, description
FROM  A
WHERE A.ID IN (SELECT ID FROM tmp);

That work perfectly fine, but the trouble is that this is a multi-user
application.  The data in 'tmp' table will be messed up if 2 or more
people delete or insert data at the same time.

Does anyone has any idea how to solve it.

Thanks in advance for help.

BN

0
BN
12/16/1997 4:51:51 PM
sybase.powerbuilder.datawindow 28057 articles. 4 followers. Follow

10 Replies
476 Views

Similar Articles

[PageSpeed] 58

On Tue, 16 Dec 1997 11:51:51 -0500, BN <bnguyen@hq.caci.com> wrote:

>Hi,
>
>I have this problem.
>
>Suppose I have a table 'A' which has columns: ID, and description.  A
>file 'B' which has 1 to 2000 IDs.
>
>I want to select all of the rows in table 'A' that have IDs in file 'B'.
>
>My solution was to add a tempory table name 'tmp' into the database
>(note that the user who running the application do not have the right to
>create or drop table).  Then before doing the selection, I delete all
>row in table 'tmp', insert all IDs in file 'B' into it, then do a
>subquery select like
>
>SELECT ID, description
>FROM  A
>WHERE A.ID IN (SELECT ID FROM tmp);
>
>That work perfectly fine, but the trouble is that this is a multi-user
>application.  The data in 'tmp' table will be messed up if 2 or more
>people delete or insert data at the same time.
>
>Does anyone has any idea how to solve it.
>
>Thanks in advance for help.
>
>BN

You don't say what database you are using, but with Sybase SQLServer
you could do away with the temp table and code the select as follows:

Select A.ID, A.description
    from A, B
 where A.ID = B.ID

And, since this is the datawindow forum, if you are using a
datawindow, you could put this select as the datasource and not need a
stored procedure.





0
charlesb
12/16/1997 6:13:25 PM
BN wrote:

> Hi,
>
> I have this problem.
>
> Suppose I have a table 'A' which has columns: ID, and description.  A
> file 'B' which has 1 to 2000 IDs.
>
> I want to select all of the rows in table 'A' that have IDs in file
> 'B'.
>
> My solution was to add a tempory table name 'tmp' into the database
> (note that the user who running the application do not have the right
> to
> create or drop table).  Then before doing the selection, I delete all
> row in table 'tmp', insert all IDs in file 'B' into it, then do a
> subquery select like
>
> SELECT ID, description
> FROM  A
> WHERE A.ID IN (SELECT ID FROM tmp);
>
> That work perfectly fine, but the trouble is that this is a multi-user
>
> application.  The data in 'tmp' table will be messed up if 2 or more
> people delete or insert data at the same time.
>
> Does anyone has any idea how to solve it.
>
> Thanks in advance for help.
>
> BN

Why don't you put an user code, or something to identify the user, in
the tmp table??

What about doing it in a single transaction??

regards
Argollo.

0
Eduardo
12/16/1997 8:14:29 PM
Why not add a column to the tmp_table that stores the session id.
This should give you uniqueness.  insybase you can acces sthis
throught the global variable @@spid.  I'm not sure hor to do this
other databases.  This will not eliminate your multiuser problems, but
its a start.  If your DBMS uses page locking, or does lock escalation,
you will still have issues, because you are doing a large number of
inserts.


On Tue, 16 Dec 1997 11:51:51 -0500, BN <bnguyen@hq.caci.com> wrote:

>Hi,
>
>I have this problem.
>
>Suppose I have a table 'A' which has columns: ID, and description.  A
>file 'B' which has 1 to 2000 IDs.
>
>I want to select all of the rows in table 'A' that have IDs in file 'B'.
>
>My solution was to add a tempory table name 'tmp' into the database
>(note that the user who running the application do not have the right to
>create or drop table).  Then before doing the selection, I delete all
>row in table 'tmp', insert all IDs in file 'B' into it, then do a
>subquery select like
>
>SELECT ID, description
>FROM  A
>WHERE A.ID IN (SELECT ID FROM tmp);
>
>That work perfectly fine, but the trouble is that this is a multi-user
>application.  The data in 'tmp' table will be messed up if 2 or more
>people delete or insert data at the same time.
>
>Does anyone has any idea how to solve it.
>
>Thanks in advance for help.
>
>BN

---------------------------------------
To reply by e-mail, remove the "nospam"
prefix from the reply to address.
0
nospam
12/16/1997 9:18:21 PM
As your problem is a part of datawindow, you can pass all your arguments
as an array and write SQL statement like

SELECT ID, description
FROM A
WHERE A.ID IN ( :ai_id_array )

As array is at client side, you donot have any problem in multi-user.

You can generate an array from your File B same way you are transferring
your information from file B to temp file on Database Server.

Regards.

Rupesh

BN wrote:
> 
> Hi,
> 
> I have this problem.
> 
> Suppose I have a table 'A' which has columns: ID, and description.  A
> file 'B' which has 1 to 2000 IDs.
> 
> I want to select all of the rows in table 'A' that have IDs in file 'B'.
> 
> My solution was to add a tempory table name 'tmp' into the database
> (note that the user who running the application do not have the right to
> create or drop table).  Then before doing the selection, I delete all
> row in table 'tmp', insert all IDs in file 'B' into it, then do a
> subquery select like
> 
> SELECT ID, description
> FROM  A
> WHERE A.ID IN (SELECT ID FROM tmp);
> 
> That work perfectly fine, but the trouble is that this is a multi-user
> application.  The data in 'tmp' table will be messed up if 2 or more
> people delete or insert data at the same time.
> 
> Does anyone has any idea how to solve it.
> 
> Thanks in advance for help.
> 
> BN
0
Rupesh
12/17/1997 1:41:08 AM
BN <bnguyen@hq.caci.com> p�e v��l�nku <3496B1A7.FAA81748@hq.caci.com>�
> Hi,
> 
> I have this problem.
> 
> Suppose I have a table 'A' which has columns: ID, and description.  A
> file 'B' which has 1 to 2000 IDs.
> 
> I want to select all of the rows in table 'A' that have IDs in file 'B'.
> 
> My solution was to add a tempory table name 'tmp' into the database
> (note that the user who running the application do not have the right to
> create or drop table).  Then before doing the selection, I delete all
> row in table 'tmp', insert all IDs in file 'B' into it, then do a
> subquery select like
> 
> SELECT ID, description
> FROM  A
> WHERE A.ID IN (SELECT ID FROM tmp);
> 
> That work perfectly fine, but the trouble is that this is a multi-user
> application.  The data in 'tmp' table will be messed up if 2 or more
> people delete or insert data at the same time.
> 
> Does anyone has any idea how to solve it.
> 
> Thanks in advance for help.
> 
> BN
> 
Which RDBMS do you use?
In a "true" RDBMS temporary tables are unique for every session!

HTH,
Pepa
-- 
***************************************************************************
Please remove the NOSPAM- from my address before replying to me!
Josef Dufek
ISS Czech, s. r. o.
jdufek@iss.cz
****************************************************************************


0
Josef
12/17/1997 10:36:19 AM
Hi,

That is a very good solution, but I tried it before and it didn't work well if
the there are more than 100 (maybe 150) elements in the array.  If the array
has 100 elements, it worked fine, but with 200 elements I got an error
something like ".... the system need more than 64K of virtual memory to ....".



Thanks.
Bye.

BN


Rupesh Shah wrote:

> As your problem is a part of datawindow, you can pass all your arguments
> as an array and write SQL statement like
>
> SELECT ID, description
> FROM A
> WHERE A.ID IN ( :ai_id_array )
>
> As array is at client side, you donot have any problem in multi-user.
>
> You can generate an array from your File B same way you are transferring
> your information from file B to temp file on Database Server.
>
> Regards.
>
> Rupesh
>
> BN wrote:
> >
> > Hi,
> >
> > I have this problem.
> >
> > Suppose I have a table 'A' which has columns: ID, and description.  A
> > file 'B' which has 1 to 2000 IDs.
> >
> > I want to select all of the rows in table 'A' that have IDs in file 'B'.
> >
> > My solution was to add a tempory table name 'tmp' into the database
> > (note that the user who running the application do not have the right to
> > create or drop table).  Then before doing the selection, I delete all
> > row in table 'tmp', insert all IDs in file 'B' into it, then do a
> > subquery select like
> >
> > SELECT ID, description
> > FROM  A
> > WHERE A.ID IN (SELECT ID FROM tmp);
> >
> > That work perfectly fine, but the trouble is that this is a multi-user
> > application.  The data in 'tmp' table will be messed up if 2 or more
> > people delete or insert data at the same time.
> >
> > Does anyone has any idea how to solve it.
> >
> > Thanks in advance for help.
> >
> > BN



0
BN
12/17/1997 3:13:05 PM
Hi,

That's what I am thinking about, but I don't like this solution much since
many users can use this tmp table.

Thanks
Bye.

BN

Ethan Smith wrote:

> Why not add a column to the tmp_table that stores the session id.
> This should give you uniqueness.  insybase you can acces sthis
> throught the global variable @@spid.  I'm not sure hor to do this
> other databases.  This will not eliminate your multiuser problems, but
> its a start.  If your DBMS uses page locking, or does lock escalation,
> you will still have issues, because you are doing a large number of
> inserts.
>
> On Tue, 16 Dec 1997 11:51:51 -0500, BN <bnguyen@hq.caci.com> wrote:
>
> >Hi,
> >
> >I have this problem.
> >
> >Suppose I have a table 'A' which has columns: ID, and description.  A
> >file 'B' which has 1 to 2000 IDs.
> >
> >I want to select all of the rows in table 'A' that have IDs in file 'B'.
> >
> >My solution was to add a tempory table name 'tmp' into the database
> >(note that the user who running the application do not have the right to
> >create or drop table).  Then before doing the selection, I delete all
> >row in table 'tmp', insert all IDs in file 'B' into it, then do a
> >subquery select like
> >
> >SELECT ID, description
> >FROM  A
> >WHERE A.ID IN (SELECT ID FROM tmp);
> >
> >That work perfectly fine, but the trouble is that this is a multi-user
> >application.  The data in 'tmp' table will be messed up if 2 or more
> >people delete or insert data at the same time.
> >
> >Does anyone has any idea how to solve it.
> >
> >Thanks in advance for help.
> >
> >BN
>
> ---------------------------------------
> To reply by e-mail, remove the "nospam"
> prefix from the reply to address.



0
BN
12/17/1997 3:20:20 PM
Hi,

I was thinking about it too, but for a multi-user application I am afraid
that this will cause troubles in future.

Thanks
Bye.

BN

Eduardo Argollo wrote:

> BN wrote:
>
> > Hi,
> >
> > I have this problem.
> >
> > Suppose I have a table 'A' which has columns: ID, and description.  A
> > file 'B' which has 1 to 2000 IDs.
> >
> > I want to select all of the rows in table 'A' that have IDs in file
> > 'B'.
> >
> > My solution was to add a tempory table name 'tmp' into the database
> > (note that the user who running the application do not have the right
> > to
> > create or drop table).  Then before doing the selection, I delete all
> > row in table 'tmp', insert all IDs in file 'B' into it, then do a
> > subquery select like
> >
> > SELECT ID, description
> > FROM  A
> > WHERE A.ID IN (SELECT ID FROM tmp);
> >
> > That work perfectly fine, but the trouble is that this is a multi-user
> >
> > application.  The data in 'tmp' table will be messed up if 2 or more
> > people delete or insert data at the same time.
> >
> > Does anyone has any idea how to solve it.
> >
> > Thanks in advance for help.
> >
> > BN
>
> Why don't you put an user code, or something to identify the user, in
> the tmp table??
>
> What about doing it in a single transaction??
>
> regards
> Argollo.



0
BN
12/17/1997 3:24:49 PM
Hi,

B is a file, not a table.  I don't know how can you do that.

Thanks
Bye

BN

Charles Busbee wrote:

> You don't say what database you are using, but with Sybase SQLServer
> you could do away with the temp table and code the select as follows:
>
> Select A.ID, A.description
>     from A, B
>  where A.ID = B.ID
>
> And, since this is the datawindow forum, if you are using a
> datawindow, you could put this select as the datasource and not need a
> stored procedure.



0
BN
12/17/1997 3:27:23 PM
Following tips may help you.

Before start retrieving reset dw. Put RetreiveStart event return code 2.
Do multiple retrieve in a loop.

Rupesh.

BN wrote:
> 
> Hi,
> 
> That is a very good solution, but I tried it before and it didn't work well if
> the there are more than 100 (maybe 150) elements in the array.  If the array
> has 100 elements, it worked fine, but with 200 elements I got an error
> something like ".... the system need more than 64K of virtual memory to ....".
> 
> Thanks.
> Bye.
> 
> BN
> 
> Rupesh Shah wrote:
> 
> > As your problem is a part of datawindow, you can pass all your arguments
> > as an array and write SQL statement like
> >
> > SELECT ID, description
> > FROM A
> > WHERE A.ID IN ( :ai_id_array )
> >
> > As array is at client side, you donot have any problem in multi-user.
> >
> > You can generate an array from your File B same way you are transferring
> > your information from file B to temp file on Database Server.
> >
> > Regards.
> >
> > Rupesh
> >
> > BN wrote:
> > >
> > > Hi,
> > >
> > > I have this problem.
> > >
> > > Suppose I have a table 'A' which has columns: ID, and description.  A
> > > file 'B' which has 1 to 2000 IDs.
> > >
> > > I want to select all of the rows in table 'A' that have IDs in file 'B'.
> > >
> > > My solution was to add a tempory table name 'tmp' into the database
> > > (note that the user who running the application do not have the right to
> > > create or drop table).  Then before doing the selection, I delete all
> > > row in table 'tmp', insert all IDs in file 'B' into it, then do a
> > > subquery select like
> > >
> > > SELECT ID, description
> > > FROM  A
> > > WHERE A.ID IN (SELECT ID FROM tmp);
> > >
> > > That work perfectly fine, but the trouble is that this is a multi-user
> > > application.  The data in 'tmp' table will be messed up if 2 or more
> > > people delete or insert data at the same time.
> > >
> > > Does anyone has any idea how to solve it.
> > >
> > > Thanks in advance for help.
> > >
> > > BN
0
Rupesh
12/18/1997 7:11:53 AM
Reply:

Similar Artilces:

DataWindow This, DataWindow That...
Blah blah blah You've heard it enough that you don't even want to comment on how many times... The DataWindow. ....but it can't be enough anymore...can it? This from one of our colleagues (and yes my apologies for taking it out of context)... "...PB covers all you need to do that in ONE Tool. This includes Windowprogramming ( meaning the interface to the user: Windows, Sheets, diallougeboxes, etc.), Database operability AND Reporting! Well, I see als well as many other PB users that some of the implementations and features PB offers are improvable. But thi...

selected row in the datawindow to displayed in the other datawindow
hi iam unable to display the data which is selected data in one datawindow to other datawindow. thnaks deva see IsSelected functions and transfer those rows to the other DW Regards Vasu "Vasu D" <vasu@i-vantage.com> wrote in message news:sDZOLqlpCHA.187@forums.sybase.com... > hi > iam unable to display the data which is selected data in one datawindow to > other datawindow. > thnaks > deva > > > Hello Vasu D, Are u using shared dw's? In this case you'll only have to use the function dw_secundary.scrolltor...

How to share the datawindow to inner datawindow of nested datawindow
How to share the datawindow to inner datawindow of nested datawindow. Ex -------- dw_1 is normal datawindow dw_nes is nested datawindow, both are placed one window dw_nes contain dw_child datawindow Question ---------------- i want to share dw_1 and dw_child. I have tried the below code, getting error dw_1.sharedata( dw_nes.object.dw_child) It's the dot notation. You will need to dw_nes.GetChild("dw_child", ldwc) where ldwc is a _local_ datawindowchild. <kzganesan@gmail.com> wrote in message news:4c64c402-bac5-4ee0-83aa-4ac08ffe43e5@r66g2000hsg.go...

Export datawindow select statement form all datawindows
I have a large application with a lot of DWs. DB is SQL Server 2000. Some DW use " (double quotes) in the select statement (not '), and now, with PB105 and oledb, this statements fails (MS native driver works) I know that " isn't a valid character for delimiting strings in SQL, but this large application was written by many people :) How can I find all " in all select statements in all DW within my application to do a replace. Thanks, Alex B. Hi Alex, I'm not sure wether some tool like PBL Peeper is capable...

datawindow
Add direct setting and getting capability for the datawindow where clause. This would have to be a string array in case of unions. Return ONLY the where clause(s) in the main select (in case of sub-selects, ignore the where clause or somehow list it as a subselect in the where or a subselect in the select). It would be great if I could just get and set this as a value and have the datawindow automatically change (or add) the where clause as specified. This is for setting the where clause dynamically at run time. Currently have to parse through the datawindow.table.select looking f...

Datawindows with child datawindows
I want to create a datawindow whose only data is three columns that will themselves be drop-down data windows. These child datawindows will be populated in PB script with dw_1.GetChild(). When I create that parent datawindow (New | Datawindow | Freeform), what do I choose in the next dialog box ("Which datasource would you like to use?") External? Thanks. That depends on how you want to populate and use the columns of the main DW. The DDDW is an edit style that assists the user in selecting a value for the column in the main DW. If you want to retrieve the values ...

DataWindow Within DataWindow
How Can I access/modify object/control from a Datawindow within Datawindow. When i access/modify object/control from a Datawindow i used dw_1.modify("name.text = 'new val'") but within another datawindow or child datawindow it doesnt work. Your description is difficult to understand due to the terminology. First, a datawindow is assigned to a control - either a datawindow control or a datastore. Second, the Modify function can only be called within a script. With that as a basis, can you rephrase the question and describe more precisely what you want to do? ...

DataWindows and datawindow objects
Difference between Datawindow and datawindowobjects? - datawindow is a control on a userobject or a window - datawindowobject (DWObject) is a control within a datawindow, defined in the dataobject of the datawindow. You refer to it as <datawindow-instance>.Object.<datawindow object> HTH, Ben (bede) <Santhoshi> wrote in message news:3f8bee8b.4631.846930886@sybase.com... > Difference between Datawindow and datawindowobjects? Not exactly; DW Control is a "container" that reserves real-estate on the surface of a visual object. DW Obj...

datawindows in composite datawindow used in datawindow.NET, are not sliding up
Hi, Problem: datawindows in composite datawindow used in "datawindow.NET", are not sliding up , if one dw is not visible or did not retrieve data. Resulting in unwanted spaces. The height of blank white space is equal to the height of the datawindow art design time. When I looked at the generated JavaScript, I noticed there is "div" for the datawindow with height 0.00in which is supposed to be not visible. Tried different options with html/xhtml/xml same result. But slides up ok when exported to pdf(using saveas) on web and in PB10.5 application. Also s...

modifiyin a datawindow in a datawindow
Hi, When I want to modify a datawindow, then it's just dw1.modify() But what do I have to do when I want to modify a datawindow in a datwindow ? I tried : dw1.dw2.modify() but that doesn't work. Anyone can help me out with this ? thanks. Regards, David get the dw via getchild & apply modify In article <4575976f.4dda.1681692777@sybase.com>, David says... > Hi, > When I want to modify a datawindow, then it's just > dw1.modify() > > But what do I have to do when I want to modify a datawindow > in a datwindow ? > I tried : dw...

Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow?
Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow? Would the following work? dw_nest.Object.DataWindow.Table.Select="select employee_id from ..." Please help. jungan@aboex.com GetChild () ??? "Jun Gan" <jungan@aboex.com> wrote in message news:40e48313$1@forums-2-dub... > Help! > > How to modify the sql select statement for a nested datawindow inside a > composite datawindow? > > Would the following work? > > dw_nest.Object.DataWindow.Table.Select="select e...

Can i modify the "Table.Select" syntax for DataWindow in a composite Datawindow ?
Hi everyone, For a normal datawindow (means not composite datawindow) control , it works very well: sVarSaveSyntax = dw_1.Describe("DataWindow.Table.Select") and dw_1.modify( "datawindow.table.select" ) = my_newSelectSyntax but in a composite datawindow, it does not work. So how can i get and modify the syntax of nested datawindow? or it's impossible at all? Thanks in advance! David Wu "David Wu" <milon@nbip.net> wrote in message news:LM6#5XBwBHA.307@forums.sybase.com... > Hi everyone, > > For a normal datawi...

I will print two datawindow in one page,and I use child datawindow.One master datawindow (dw_master)with two child datawindow(dc1 and dc2)
dw_master("dw_dc1",dc1) dw_master("dw_dc2",dc2) dc1 has a argument (arg1) dc2 has a argument (arg2) Arg1 and Arg2 are send by one column of dw_master. When I use retrieve() function,the datawindow can display normal,but when I print preview or use Zoom or printsetup() ,the childdatawindow dc2 can't display but dc1 normal! Why?? Thanks for your help! I'm having trouble understanding your question. Is this a composite or nested datawindow? On Mon, 6 May 2002 22:49:23 +0800, in powersoft.public.powerbuilder.datawindow spark <fenfei79@hot...

Inserting datawindow to another datawindow
Hello, what is the best (ok, any) method of inserting all visual objects from first datawindow to second datawindow, while keeping the objects from second datawindow on their place? Thank you for your time :-) Jakub Janda The best method would be to go back to design :-)) pbm_thisusuallydoesnothelp:-)) Philip Salgannik "Jakub Janda" <Jakub.Janda@empire.cz> wrote in message news:mRGyciwUAHA.202@forums.sybase.com... > Hello, > > what is the best (ok, any) method of inserting all visual objects > from first datawindow to second datawi...

Drop down datawindow within a datawindow
I have a datawindow that is attached to a PL/SQL stored procedure that is supposed to list records. Several of the fields in the list are identified as Style DropDownDW. The intent is that for these fields there will be multiple values available by using a vertical scroll bar and arrow. For example, a record in the window might have Name Address Medical Code Medical code would also be a drop down list box with multiple values. Is there a simple way to implement this in Powerbuilder 9? What kind of PB coding constructs would be necessary? Thanks Please post this question ...

Web resources about - datawindow: SELECT - sybase.powerbuilder.datawindow

Resources last updated: 1/2/2016 6:26:06 AM