SQL Statement problem

I am trying to pull certain information from a database by using the ISQL 
window, but Powerbuilder keeps locking up when I type the following code, 
could someone please show me what I'm doing wrong (assuming that all of the 
spelling is correct)?  Thanks!!!

SELECT t0.code, t0.billcode
FROM billcode t0
WHERE t0.code in (SELECT distinct t1.code
                  FROM app_charges_old t1
                  WHERE t1.invoice_number IN (SELECT t2.invoice_number
                                              FROM invoice_summary_old t2
                                              WHERE t2.invoice_date = 
'2002/10/31'
                                              AND t2.total_new > 0));
0
Tom_S
11/27/2002 12:39:15 AM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

4 Replies
590 Views

Similar Articles

[PageSpeed] 18

What version/build.  Early 7.0 / 8/0 builds have problems with nested
selects.

--
Terry Dykstra (TeamSybase)
Please state PB / OS / DB versions in your post.
SDN: http://www.sybase.com/developer/


<Tom_S> wrote in message
news:CF88BEBF06376AD00003981D85256C7E.0003982D85256C7E@webforums...
> I am trying to pull certain information from a database by using the ISQL
> window, but Powerbuilder keeps locking up when I type the following code,
> could someone please show me what I'm doing wrong (assuming that all of
the
> spelling is correct)?  Thanks!!!
>
> SELECT t0.code, t0.billcode
> FROM billcode t0
> WHERE t0.code in (SELECT distinct t1.code
>                   FROM app_charges_old t1
>                   WHERE t1.invoice_number IN (SELECT t2.invoice_number
>                                               FROM invoice_summary_old t2
>                                               WHERE t2.invoice_date =
> '2002/10/31'
>                                               AND t2.total_new > 0));


0
Terry
11/27/2002 4:32:33 PM
Are you sure that PB isn't "locking up" because the query is going out to
lunch?   Using doubly nested IN() clauses with a SELECT DISTINCT doesn't
sound too efficient...

How about this correlated subquery instead?

Select t0.code, t0.billcode
  from BillCode T0,
  where exists(
   select 1 from
         App_Charges_Old T1, Invoice_Summary_Old T2
     where T1.invoice_number = t2.invoice_number
        and T1.code = T0.code
        and t2.invoice_date = '2002/10/31'
        and t2.total_new > 0) ;

--
Paul Horan[TeamSybase]
VCI   Springfield, MA
www.vcisolutions.com


<Tom_S> wrote in message
news:CF88BEBF06376AD00003981D85256C7E.0003982D85256C7E@webforums...
> I am trying to pull certain information from a database by using the ISQL
> window, but Powerbuilder keeps locking up when I type the following code,
> could someone please show me what I'm doing wrong (assuming that all of
the
> spelling is correct)?  Thanks!!!
>
> SELECT t0.code, t0.billcode
> FROM billcode t0
> WHERE t0.code in (SELECT distinct t1.code
>                   FROM app_charges_old t1
>                   WHERE t1.invoice_number IN (SELECT t2.invoice_number
>                                               FROM invoice_summary_old t2
>                                               WHERE t2.invoice_date =
> '2002/10/31'
>                                               AND t2.total_new > 0));


0
Paul
11/27/2002 4:55:59 PM
I'm using build 7.0
0
Tom_S
11/29/2002 3:45:51 PM
This works perfect!  Thanks
0
Tom_S
11/29/2002 4:05:26 PM
Reply:

Similar Artilces:

Please Help me:SQL statement failed: (-301) Internal database error ??? -- transaction rolled back SQL statement failed: (-101) Not connected to a database
Hi all, I am using Adaptive Server Anywhere 8 version 8.0.2.4239. I am able to sync successfully between 2 desktops with the same version. When I am trying to sync with my pocket pc device version 8.0.4122, Towards the end of Synchronization (nearly 95% completed) ,I am getting this error "E. 05/29 19:28:31. SQL statement failed: (-301) Internal database error ??? -- transaction rolled back E. 05/29 19:28:31. SQL statement failed: (-101) Not connected to a database" The log file contains E. 05/29 19:28:31. SQL statement failed: (-301) Internal database er...

New errors access MS SQL Server Database on (CREATE DATABASE and Batch SQL Statement)
------=_NextPart_000_002B_01C2F2CB.0C201FF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running ActiveState perl 5.8.0 on a WIN2K box with MS SQL Server = 2000. Recently I updated DBD-ODBC to v1.05 and DBI to v1.35 Now I get the following errors executing some sql commands CREATE SQLSTMT:[CREATE DATABASE POMS_SER ON (NAME =3D 'POMS_SER_DAT', = FILENAME =3D 'd:\Database\POMS\POMS_SER_Data.MDF', SIZE =3D 10MB) LOG ON = ( NAME =3D 'POMS_SER_LOG', FILENAME =3D = 'd:\Database\POMS\POMS_S...

Problem in executing Sql statement with pb6 & Oracle 8 (Native database driver)
When executing sql statements in pb6 (Version 6.0.00) with Oracle 8 native database driver (Beta) the following problem was encountered: Table definition: Colum name datatype Key limits_tablename varchar2 (50) yes limits_colname varchar2 (100) yes limits_lastcounter float limits_datatype char(1) limits_desc varchar2 (50) Code : String ls_datatype, ls_tabname Ls_tabname = �A� Select limits_datatype into:ls_datatype From cmis.limits Where limits_tablename = :ls_tabname; After the above piece of code was executed Ls_datatype remained null and Value of Sqlca....

Inserting SQL statements into a SQL Server 2000 database
Hi all Am attempting to create an audit table that will allow me to keep tracke of who is doing what where and when. On thing that i want to insert into this table, is the actual command that was executed, however when i look at the data that i inserted all that i get is "System.Data.SqlClient.SqlCommand" in the field. Any suggestion on how to overcome this problem. Mike55 Hello Mike55 if I understand you right, you like to record each request to have the possibility to control who do what and when. I think a good possibility is to create a httphandler to record all...

Problem with analytic sql function (The OVER SQL construct or statement is not supported)
Hi All!  Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.” Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter w...

Problem with PowerBuilder dynamic cursor in sql-Anywhere and performance problems
Please help ! I have developed a statistic program with PowerBuilder Vers. 6. This program gives the user much freedome in choosing filters (for the where-clause) and the fields to retrieve from the database. Therefore I use the possibility of dynamic cursors, because I am only able to build the select-statement at runtime, when the user has entered his parameters. When using the 16-Bits Data engine of SQL_Anywhere the program works without any troubles. When using the 32-Bit engine I receive the following Error-Message: SQLSTATE = S1000 [Sybase][ODBC Driver] General err...

SQL-Statement problem
Dear all i am having a problem with SQL-Statement-1.005, whereas SQL-Statement-0.1020 is working fine. SQL ERROR: Mismatched parentheses in WHERE clause! SV = RV(0x810cad0) at 0x829105c REFCNT = 1 FLAGS = (PADBUSY,PADMY,ROK) RV = 0x83844e4 DBD::CSV::st execute failed: dbih_getcom handle 'DBD::CSV::Statement=HASH(0x83844e4)' is not a DBI handle (has no magic) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. I think i tracked this down to Parser.pm the cleansql routine which skips closing parens, but not the corresponding openings: wa...

SQL Statement Problem
Hello all, Let's see if we have some pro's out here or not. This could be simple actually, but I don't know how... I have two tables, "Projects", and "Tasks." Now what I am trying to do is list the projects on a page. And I have managed to do that nicely. And, while it's looking, I actually have it open up another recordset inside of that one (which is looping) and have it lookup the tasks status percentage. So, when looking at one record, it looks like this: Projects - ID Title Status 1 Sam...

Problem with sql statement
Hi all I've got some trouble with a sql statement. What I have got: 4 Tables 1. tFuel------------------------FuelId | FuelDescription ... 1 | Diesel  2 | SomeOtherKindOfFuel 2. tFuelPrice------------------------GasStationId | FuelId | Price ...1 | 1 | 1.451 | 2 | 1.30 3. tGasStation------------------------GasStationId | GasStationGroupId | Name ...1 | 2 | Millers Gas Station |2 | 1 | Chucky's Gas Station | 4. tGasStationGroup------------------------GasStationGroupId | Name ...1 | BP2 | Shell So, what I want to achive is the following I'd like to show the user a list of all...

SQL Problem in powerbuilder
Dear all, I have put the following script to insert a new record to db. But after run the script, nothing is inserted. CAN anyone help me? insert into psap_education (psap_no, psed_seq, psed_grade, psed_school, psed_years, psed_create_user_id, psed_create_date) values (5098,1,'GRADE1','ABC SCHOOL','2002', 5097,'2002/9/6') using sqlca; COMMIT using sqlca; Have you checked the value of sqlca.sqlcode before you issue the commit? If its value is -1 then an error occurred (for example duplicate primary key). You can determine the error that occu...

SQL statement problems
Having problems with our SELECT statements in both Webforms and client/server application that I wasn't having in the various demo versions. I am using "SNC" (SQL Native Client installed) connection. Connected to DB with transaction object called "SQLCA2". This code for debugging purposes works: select count(*) into :ll from users using SQLCA2; This actual code does NOT work: SELECT count (*) INTO :l_i_count FROM users WHERE user_id = :a_s_user_id using sqlca2; From debugger: sqlcode=0 sqldbcode=0 sqlerrtext="" Any ideas would b...

Problems whith SQL query. I need to store ' char in sql database
Hi,Im trying to store a sql query in sql, see example below. But it keeps bugging me around 'Adobe' in the example. Anyone who know how to store the stringSelect app_Number as [Applikationsnummer], App_status as [Applikationstatus], Collector as [Insamlare] from Applikations WHERE App_Producer='Adobe'In a sql database?   INSERT INTO [View] (View_Name, View_Query) VALUES ('Henriks','Select app_Number as [Applikationsnummer], App_status as [Applikationstatus], Collector as [Insamlare] from Applikations WHERE App_Producer='Adobe'') Thanks in advance Henrik Alstersjo You n...

A problem about SQL Statement
Hello, I've got a problem about a Select statement with the following criteria: Data Table Key Num1 Num2 A 1 2 A 1 2 A 2 3 B 0 0 B 1 1 C 2 1 C 1 2 I would like to select all row of a key if the (sum of num1 - sum of num2) with that key != 0 In the above case, the outcome would be: A 1 2 A 1 2 A 2 3 Could any body advise what the SQL should be? Many thanks!   Hi,newbie Try Like This I have shown the select query in bold   CReate table Test_Sql ([Key] varchar(20),[Num1] int, [Num2] i...

SQL statement problems
Hey gang, I'm trying to work out why on earth my SQL statement refuses to work. I've tested it on my db within Access, yet working off the website itself, it just refuses to return anything when there should be 7 or so listed. Can anyone see anything wrong with my statement (warning, it's long): strConnection = @"Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + (string) Server.MapPath("GAAP.mdb"); strSqlCommand = "SELECT STUDENT.STUDENT_ID, STUDENT.STUDENT_LAST_NAME, STUDENT.STUDENT_FIRST_NAME, UNIT_STUDENT.STUDENT_EMAIL, UNIT_STUDENT.STUDENT_PASSWO...

Web resources about - SQL Statement problem - sybase.powerbuilder.database

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Innovation statement: what you need to know about Malcolm Turnbull's plan
What you need to know about the newly released innovation statement.

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Industry reacts to Malcolm Turnbull's innovation statement
First impressions in the tech and start-up industries are largely positive.

Malcolm Turnbull's statement to UN climate conference in Paris
Malcolm Turnbull joined other world leaders in Paris for the United Nations climate talks on Monday. This is his statement.

Freddie Gray case: Officer William G. Porter's recorded statement played in court - Baltimore Sun
Officer William G. Porter told investigators in a recorded interview played for jurors Friday that he did not believe that Freddie Gray was truly ...

Obama releases emotional statement on Planned Parenthood shooting - Business Insider Deutschland
... Parenthood shooting in Colorado Springs, Colorado. REUTERS/Jonathan Ernst President Barack Obama. President Barack Obama released a statement ...

Jury Sees Officer’s Video Statement In Freddie Gray Trial
More key testimony as the prosecution continues to call witnesses in the trial of city police officer William Porter.

Resources last updated: 12/8/2015 1:18:15 PM