Query problem. Joining two queries


 After two days of trying to put two queries together, I feel frazzled and further from where I need to be. Actually I could not even get anything to really run with out errors especially with missing right parens..

If you could help, I would greatly appreciate it.

Below are two separate queries. Query 1 returns all rows in the database.  Query 2 currently returns 1 row because the where statement is set up for the one row, but I need to change so that it some how links to Query 1 and the two fields derived from Query 2 will be listed for each row coming back in Query 1.  I thought I could link on client.id or client.accountnumber (client.id is an indexed field i believe).   I hope you can help. 

This query, returns all records in the database:

Select client.Id, client.accountnumber, client.Namelast,
       dmlocation.city ||', '||dmlocation.state as CityState,
       client.salesTerritory_client ||'-'|| dmuser.namefirst_user ||' '|| dmuser.namelast_user as Territory,
       MaxDates.LastRun, client.creditrisk, client.customercategory 
    from client 
         Left join fctclientcoverage on fctclientcoverage.client_id = client.id 
         Left join dmlocation on fctclientcoverage.location_id = dmlocation.id 
         Left join dmuser on dmuser.id = client.id 
         Left join (Select to_char(Max(dmdate.calendardate),'MM/DD/YY') as LastRun, Client.Id
                from dmdate, client, fctadorder 
                where dmdate.id = fctadorder.lastinsert_date_id and client.id = fctadorder.primaryorderer_client_id 
                group by client.id) MaxDates ON client.id = MaxDates.Id
   where(fctclientcoverage.Ccoverrecordstopdate Is Null) 
       Order by client.namelast;

Query 2, only returns 1 row, so for each row returned above, the two fields selected in this query should accompany each row. 
But how to link these two selects using the client.accountnumber   (or perhaps by client.id)?

Select booked.CurRev, booked.LastRev from (
Select (sum(Case When dmDate.CalendarDate >= '29-DEC-2008' and dmDate.CalendarDate < '02-FEB-2009'
                Then fctinsertchargedetail.Amount_insertDetail Else 0 End)) As CurRev,
       (sum(Case When dmDate.CalendarDate >= '29-DEC-2007' and dmDate.CalendarDate < '02-FEB-2008'
                Then fctinsertchargedetail.Amount_insertDetail Else 0 End)) As LastRev
       from fctAdorder 
         Inner Join client On fctAdorder.primaryorderer_client_id = client.id
         Inner Join fctinsertion On fctAdorder.id=fctinsertion.fctAdorder_id
         Inner Join fctinsertchargesummary On fctinsertion.id=fctinsertchargesummary.insertion_id
         Inner Join dmDate On fctinsertion.insert_date_id=dmDate.id
         Inner Join fctinsertchargedetail On fctinsertchargesummary.id=fctinsertchargedetail.insertchargesummary_id
      WHERE client.accountnumber = '12345678' and 
      dmDate.CalendarDate >= '29-DEC-2007' And dmDate.CalendarDate < '02-Feb-2009') booked;


2/18/2009 6:02:31 PM
asp.net.oracle 2269 articles. 0 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 32

I see, nobody replied...

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
2/19/2009 2:23:56 AM
2/19/2009 1:36:59 PM

Yes, maybe we need a separate Qracle SQl forum.

2/19/2009 1:38:27 PM

Similar Artilces:

Query about Queries
Hello all, As a relatively new user to Sybase I am hoping someone can point me in the right direction. We are getting ready to go live with our database and I am wondering what types of queries I should be running for system performance purposes. If anyone has any suggestions they would be more than appreciated. Thanks in advance, Jerry ...

How to Query A Query
I have been trying to build a crosstab report that reports everydate within a selected range across the top and specific row information. 7/1 7/2 7/3 . . . Name Detail1 Detail2 Detail3 Name Detail1 What I get as output is only the dates that contain information in the rows. I can get the information I need by using a query within a query (nested). Can a nested query be done in Informaker 5? What about version 6 or 7? Thanks for your help Convert your SQL to syntax, and you can write anything you want. So if your DB support...

Query a Query
I have a result set in a query. Is it possible to query this query? (no specific SQL dialect, can it be done generically?) Petros -- PETROS GAVRIELIDES wrote: > I have a result set in a query. > Is it possible to query this query? > > (no specific SQL dialect, can it be done generically?) There is a generic answer to your question: Yes, there is a construct called a sub-query that allows you, in 1 SQL statement, to query from a query. But, generically speaking, not all DB engines support this construct. For instance, Firebird only added it just over a y...

How to query a query...
I'm working on converting an Access DB that I created into an ASP.NET application (and attempting to learn ASP at the same time...). One of my queries was very complex and required running a different query first, and then working off of that one. Any suggestions on the best way to do this in ASP? The following is a sample of code I'm using so you can see the type of data-binding I'm using. Thanks for the help! myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Server.MapPath("/EquipmentCheckout.mdb")) strS...

Rogue queries, cancelling queries: some problems
The other day I wrote a really dump query in SQL Advantage that brought 11.5.1 (and the PC) to its knees, and I thought I should ask if this is a known issue and/or if this indicates a misconfiguration. I've been able to repeat this reliably. The query was: select a.Degree_Year,sum(b.Giving) from d_si_ a,fgf_ttl b group by a.Degree_Year having a.AFIS_ID=b.AFIS_ID and b.Year=1997 Even as the query plan was being shown to me the system began to bog. After 10 minutes I did query/cancel. Nothing happened for at least 15 minutes, during which time "Cancelling..." w...

querying from open query join table
hey all i am trying to query from a stored Procedure found from a remote server...and join the results with data found on a table another database.... my sql statment looked like that: select col1,col2,..,local.colx from openquery(LSname,'exec storedproc') where openquery(LSname,'exec storedproc').colx=local.colx i know it is wrong..please hel  OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, you can covert your stored procedure to function an extended by using a four-part name:select * from  LSname.MYdataBase.dbo.M...

? Joining two Queries
Right now I have two Queries that I've created: What I'm wanting to do is graft everything from Query1 onto Query2 but only where Query1.UserID = Query2.UserID . Any suggestions on how I would go about doing this? --Query 1 ( SELECT PC1.ID, PC1.UserID, PC1.ModuleID, PC1.ModifiedDate FROM Projex2_0_0_Cores PC1 WHERE PC1.ModuleID = 369 ) --Query 2 ( SELECT PC2.UserID, MAX(PC2.CreatedDate) as CreatedDate FROM Projex2_0_0_Cores PC2 WHERE PC2.ModuleID = 369 GROUP BY PC2.UserID, PC2.ModuleID ) -Tony Valenti...

Joining two queries
 Hello I have two queries that I would like help joining: Query #1SELECT     UserName, (SELECT  COUNT(*) AS Expr1  FROM Jokester WHERE (InvitedBy = aspnet_Users.UserName)) AS invsFROM         aspnet_Users Query #2 SELECT     UserName, SUM(Hits) AS HitsFROM         (SELECT     UserName,                        &nb...

Problem with Oracle query
Hi all, I have a problem with the following statement: $dbh=3DDBI->connect( "dbi:Oracle:$db", "$user", "$pw") or die "Fehler $DBI::errstr\n"; $file=3D"/home/klaue/webtower_fs1/data/user/t4y/data/Customers_and_opport= unities/Tarsom/daten/b2c-holz kollektion sequenz_vs2.txt"; $sep=3D";"; @tables=3D("eiche", "buche", "ahorn", "esche", "birke", "kiefer", "kirschbaum", "merbau", "kempas", "nussbaum"); open(FILE, "<...

Problem with Oracle queries
As a follwoup message - when I run a query against Orcale I have a column that contains numbers but IM shows the number only on ever 11th row and every thing in between is is either zero or garbage. I am using the Oracle 2.5 ODBC driver and cannot find another driver within IM and there are no downloads on the Powersoft site. Any help? -- GIPSON & ASSOCIATES http://www.forgottenlane.com 817.790.3547.tel Use the Oracle 2.0 ODBC driver. But better yet, use the native Oracle driver that comes with IM. Do a custom install of IM and select from the native drivers the desired Or...

Query and Sub-Queries
I have a SELECT statement that has two sub queries in it. The 'where' clause in the main query filters a TRADE_DATE field, for example: ...... where TRADE_DT >= '2003/01/01' and TRADE_DT <= '2003/01/31'. Since I only have one date field(TRADE_DATE), I want the sub-query to query where TRADE_DT <= '2003/01/31'. But its not working. I have to hard code the '2003/01/31'. Now I know I can make a dynamic view in VB, but an ideas of how I can do it in SQL? Here's my statement: select T.BROKER_SEQ_NO,B.BROKER_NAME,B.B...

Query Plan Query
We have a sql query that is causing a few issues in terms of performance as the optimisor is choosing to table scan where an index will certainly yield significant gains. As proven when snapshotting the query and adding an index hint manually. Unfortunatley since the query is dynamically created by a 3rd party application, we are unable to influence this with index hints and are dependant on the optimiser. The table that is table scanning is partitioned (has also hust been repartitioned to address a slight inbalance across partitions) and has 7.6M records. It has been recently r...

Exacute query against query
I have a query which selects all dates from my database between 2 date values which is then entered into my dataset. I thin want to run through the dataset totalling the value of a certain column but only if dates match e.g. 27/02/2007          2.21 27/02/2007          3.10 27/02/2007          2.10                           Tota...

Query of Queried data
I am new to asp.net after working with coldfusion.  I have created a datagrid from a query of a sql database.  I am interested to know how can I create another datagrid which will process a query of the data in the original grid.  So essentually it is a query of a query. Thank you   You want to filter on the result of the first query? The easiest way I can think of, is to put the data in a datatable, then use the select and dataview features of the datatable. Hi,uusquintsYou can filter the datatable in the code-behind and bind the result to the 2nd datagird:...

Web resources about - Query problem. Joining two queries - asp.net.oracle

Problem novel - Wikipedia, the free encyclopedia
Working class, or proletarian novels are often also social problem novels . This was in many ways a reaction to rapid industrialization , and ...

Wee problem with credibility of studies
Perhaps the creepiest science experiment ever conducted took place in a lavatory.

Audi says fixing diesel emission problem is simple
... Green , Audi , Porsche , Volkswagen , Emissions , Diesel , Luxury Audi believes a simple fix might be enough to solve the emissions problem ...

The FBI finally gives a clear explanation about its problems with encryption
The FBI is still banging the drum for tech firms to give it a "backdoor" to encrypted devices and this time it's offered a clear explanation ...

"I say, folks, you know, I’m sorry I did this to you, but you’ve got to get used to it. It’s one of those ...
"I’m going to win. ... You know, I’m not one of these other guys that goes down. I don’t go down. I go up." Donald Trump says get used to it ...

Trump: If GOP Does Not Stick to Our Deal ‘They’re Going to Have Problems’
Trump: If GOP Does Not Stick to Our Deal 'They're Going to Have Problems'

Guilty verdict for Oklahoma cop in serial rape trial: A national problem
Christian Science Monitor Guilty verdict for Oklahoma cop in serial rape trial: A national problem Christian Science Monitor A former Oklahoma ...

3 and Out: Three big picture problems for the Patriots
... form Search 51° Main menu Home Celtics Patriots Red Sox Bruins Revolution More WRITERS Watch Shop NECN 3 and Out: Three big picture problems ...

In Ghana, the global problem of e-waste has local consequences
Consumers have a nasty habit of throwing out their electronics as soon as newer, shinier models become available, and they rarely ever do so ...

Ted Cruz denies questioning Donald Trump's judgment. Problem is, he did it on tape.
Did Ted Cruz question whether Donald Trump has the judgment to be president or did he not? Cruz, who has cuddled up to Trump throughout the campaign, ...

Resources last updated: 12/14/2015 9:47:25 AM