• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Help with SQL

 
Ranch Hand
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I can't understand your table layout or desired report.
Can you try using the [ code ] tags to help format your question?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maki,
You could do something like this:


There may be a simpler approach though.
 
Maki Jav
Ranch Hand
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

[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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
oops I didn't look at Jeanne's reply.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maki,
In that case, you can use an outer join. The syntax varies by database.
 
Maki Jav
Ranch Hand
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you plz give me an example?

Thanx in advance,

Maki Jav
[ September 20, 2005: Message edited by: Maki Jav ]
 
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Maki Jav
Ranch Hand
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic