aspose file tools*
The moose likes JDBC and the fly likes Help with SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help with SQL" Watch "Help with SQL" New topic
Author

Help with SQL

Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
hi,

I have two tables




I want to have a report (web page)

Report January 1st, 2005 to January 31, 2005




and so forth....


How to do that in SQL? I could have use Vectors to store the two results and comparing date and
orderNo for getting totals.

Plz help me with this...

Thanx in Advance,

Maki Jav

[added code tags]

[ September 12, 2005: Message edited by: Jeanne Boyarsky ]
[ September 18, 2005: Message edited by: Maki Jav ]

Help gets you when you need it!
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I can't understand your table layout or desired report.
Can you try using the [ code ] tags to help format your question?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30929
    
158

Maki,
You could do something like this:


There may be a simpler approach though.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
I did not laid my Report like that. How come the positions of my values changed?

I wrote

01/01 1000 EMPTY 1000
02/01 EMPTY 3000 3000
03/01 2500 1600 4100

In simple words, I meant that on one fine day only advance in lieu of order
was received,
so the total on that day was 1000. The next day some advance was received for sales,
so the total was 3000 (order=0 sales=3000). Then it so happened that on 3rd day, we had
money coming in for order and sales so we got a total of 4100 that day.

These are three conditions which we may face on any particular day.
The morale is: I have to pick up the two advances for each day within the dates defined
by the user and show the values and the sums ie totals.

I hope you got my point...

ps:I have tried to execute the query but my msaccess query runner is giving me error
message at select in from clause

Maki Jav
[ September 11, 2005: Message edited by: Maki Jav ]
Murugan Patham
Greenhorn

Joined: Oct 16, 2004
Posts: 20
[Order Table]
dated
advance

[Sales Table]
InvoiceNo
orderNo
dated
total
advance
balance




You got to change the field name and Table name accordingly.
Murugan Patham
Greenhorn

Joined: Oct 16, 2004
Posts: 20
oops I didn't look at Jeanne's reply.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30929
    
158

Originally posted by Maki Jav:
I did not laid my Report like that. How come the positions of my values changed?

Without formatting, everything in the row looks like one big line. I added formatting to the report (as Dave suggested) because it was tough to figure out what was going on. Of course the spaces weren't there, so I couldn't do it exactly right.

For the future, note that you can go back and edit your posts too.
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
Murugan Patham and Jeanne what if there is no entry for one table on a particular day
and yet the other table has an entry. This is what I am asking for..
How to find and sum where dates match and just show entries from either
table (and Total for every day of course) when no such match exists!


Should I query the two tables against each single date and and use java to add up
the two [advance] fields and print it as TOTAL? Would it not be too much a heavy on database?

Thank you,


Maki Jav
[ September 18, 2005: Message edited by: Maki Jav ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30929
    
158

Maki,
In that case, you can use an outer join. The syntax varies by database.
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
Can you plz give me an example?

Thanx in advance,

Maki Jav
[ September 20, 2005: Message edited by: Maki Jav ]
Joel McNary
Bartender

Joined: Aug 20, 2001
Posts: 1824

In general, it is:



Note that while the outer join syntax above is ANSI SQL standard, databases vary on how they will accomodate this. This query will work fine on Oracle 9i and greater, which supports both the LEFT OUTER syntax and the nested query syntax.

Perhaps

This eliminates the subqueries and should work on most databases out there -- except ORACLE prior to 9i.
[ September 21, 2005: Message edited by: Joel McNary ]

Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
Joel will it work when say an entry against a date exists in only one table and not the other?

what will be returned when a date exists in the sales table and not the order table? and vice versa?

Thank you very much,

Maki Jav
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
I have come to conclusion that I need to do it in Java
using two resultsets, I will query each table for a particular date
and sum totals for that day getting values of the two advances.

In this way I will be saved of complex queries...

The situation I am facing here is of four type for each day...

1000 +null= 1000
null + 500=500
1000 + 500=1500
null + null=0

I hope that I am on the right track...

Thank you in advance,

Maki Jav
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
I have done it!!!

Thanks to randym... he has helped me with the queries...

What I have learned, I want to share with others at Javaranch...

I am using msaccess 98 and jdk1.3

I have used UNION ALL to do the job...

I have used CallableStatement to access required stored query...

CallableStatement cstmt = con.prepareCall(sql);

I was in for a surprise when calling {call SROrderUSale Query(?, ?, ?, ?)}

I had two queries in database:
1) SROrderUSale
2) SROrderUSale Query
While they were two different queries in m$access database but
Java was calling SROrderUSale for word "SROrderUSale Query"
maybe it is accepting word "Query" as relevant to database because:

if we code:

DatabaseMetaData dbmd=connection.getMetaData();
System.out.println("All Procedures Are "
+(dbmd.allProceduresAreCallable()?"Callable":"Not Callable"));
System.out.println("Procedures are Called "+dbmd.getProcedureTerm());

We see that Stored Procedures are called "Query" in access. So I
changed the second query to SROrderUSaleQuery in database and my Java code.
Now it is working as expected.


MY first Stored Procedure namely SROrderUSale was:
--------------------------------------------------

SELECT orderDate as [date],sum([Order].Advance) as ordamount,
0 as salamount, ('Order') as TableName from [Order]
WHERE orderDate Between BegDate And EndDate
GROUP By orderDate

UNION ALL

SELECT saleDate as [date], 0 as ordamount,sum(Sales.Advance) as salamount,
('Sales') as TableName from Sales
WHERE saleDate Between BegDate And EndDate
GROUP By saleDate
ORDER BY [date];
----------------------------------------------------

Though I have a field TableName but I did not use it further down the line.
It is useful to get info where is the data is coming from.


MY second Stored Procedure namely SROrderUSaleQuery that was calling
SROrderUSale was :

----------------------------------------------------

SELECT date, Sum(ordamount) AS orderamount, Sum(salamount) AS salesamount,
Sum([ordamount])+Sum([salamount]) AS Total
FROM SROrderUSale
GROUP BY date;
----------------------------------------------------


BegDate (beginning date) and EndDate (ending date) are parameters that
are taken in at runtime. If you run query in database you will be asked
once for each date. You will have to set it twice from the code as they
are used as many times in the first query.

setString(1, "12-Mar-06"); //BegDate
setString(2, "14-Mar-06"); //EndDate
setString(3, "12-Mar-06"); //BegDate
setString(4, "14-Mar-06"); //EndDate

Now some discussion of errors:

Setting prepareCall(sql) worked for me only.
I could not do anyting else with ResultSet or Concurrency.
The following are the settings and exceptions:

con.prepareCall(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
java.sql.SQLException:
[Microsoft][ODBC Microsoft Access Driver]Optional feature not
implemented

con.prepareCall(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}

con.prepareCall(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}

con.prepareCall(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}

The Test Class is ....


[ September 28, 2005: Message edited by: Maki Jav ]
Maki Jav
Ranch Hand

Joined: May 09, 2002
Posts: 437
Hi,

My post stopped more than a year ago. I hope that the info here have been helpful to you all. I made a lot of effort on this...
;)

Maki Jav
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with SQL