|
View previous topic :: View next topic
|
| Author |
Message |
sandiptcs
New User
.jpg)
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
I have 2 tablea TAB1 and TAB2
TAB1 has following attributes
ACC-NUM(key)
VALUE-DATE
AMOUNT-1
TAB2 has the following attributes
ACC-NUM(key)
VAL-DATE
AMOUNT-2
How can I write a SQL QUERY that will fetch the amounts for a particular a/c from both these table sorted by the the date attribute in both these tables
I want the amounts retrieved from either TAB1 or TAB2 at a time and in the ascending order of value date(i.e VALUE-DATE or VAL-DATE)
I tried using ORDER BY MAX(VALUE-DATE,VAL-DATE), but it is not sorting correctly. |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| Wouldn't something like select column-name as new-name to both tables allow you to be able to max just a single name |
|
| Back to top |
|
|
sandiptcs
New User
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
| I could not understand your explanation. Could you please clarify a little bit |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| What is your select statement? Please post it wrapped in "code". |
|
| Back to top |
|
|
sandiptcs
New User
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
| Code: |
The sql query is
SELECT A.BEDRAG,
B.VALUE_DATE,
A.VALUTADATUM,
B.SUBTOTAL_AMOUNT,
FROM TMUTATION A, TSUBTOTAL B
WHERE B.SUB_ACCOUNT = 423708724 AND
A.BANKRKGNR = 423708724
ORDER BY MAX(A.VALUTADATUM, B.VALUE_DATE ), |
I want the output in SPUFI to fetch all records from both TMUTATION and TSUBTOTAL , sorted in ascending order on value date
The value date is present in both the tables TMUTATION and TSUBTOTAL
But this query is not working as I want it to
[/code] |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| Code: |
SELECT A.BEDRAG,
A.VALUTADATUM as vdate,
A.SUBTOTAL_AMOUNT as samt,
FROM TMUTATION A
WHERE A.BANKRKGNR = 423708724
union
SELECT B.BEDRAG,
B.VALUE_DATE as vdate,
B.SUBTOTAL_AMOUNT as samt,
FROM TSUBTOTAL B
WHERE B.SUB_ACCOUNT = 423708724
ORDER BY vdate |
Trying to manuver thru bookmanager on an underpowered PC is a pain....
The manual says only one will be returned, I don't understand which, maybe a where vdate = max(vdate)??? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Please clarify what you need by
| Quote: |
from both these table sorted by the the date attribute in both these tables
|
If you sort by the date in table a you'll quite likely get different results than sorting by the date in table b 
Please give examples of both tables input data and how you'd like the output to be returned. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
Sandip...
This is your problem area... You should not use MAX clause like that...
| Code: |
| ORDER BY MAX(A.VALUTADATUM, B.VALUE_DATE ), |
Instead you can put ORDER BY to have ascending or descending preference rather than giving MAX or MIN.
Try below query... It should work, if I understood what you meant to do... Default for ORDER BY is Ascending...
| Code: |
SELECT A.ACC_NUM, A.VALUE_DATE, B.VAL_DATE, A.AMT_1, B.AMT_1
FROM TABLE_1 A,
TABLE_2 B
WHERE A.ACC_NUM = B.ACC_NUM
ORDER BY A.VALUE_DATE, B.VAL_DATE; |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Priyesh,
Ordering by date-a and date-b will execute but i'm not sure if the results will be what Sandip is looking for. The modified query will result in a major/minor sort on the date fields which may be exactly what is desired.
I may be reading a bit too much in the original post, but i had this feeling that the desired output would use one date or the other to order-by depending on which was more/less recent (i.e. the max of the 2).
If Sandip will post example data and the desired results, we can be more helpful. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
William,
Sounds like what you have is what sandip wants, to ?concatenate? the two tables together.
| William wrote: |
The manual says only one will be returned, I don't understand which, maybe a where vdate = max(vdate)???
|
without knowing exactly what you are looking at, I?ll add this addendum.
?UNION? will eliminate duplicate rows, so only one will be returned,
?UNION ALL? will return ALL rows, even if duplicate. |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
Thanks David, I caught that
| Quote: |
?UNION? will eliminate duplicate rows, so only one will be returned,
?UNION ALL? will return ALL rows, even if duplicate. |
but I still haven't figured out how to guarantee the larger date value....  |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
William,
Maybe I'm not reading enough into this. If there are two dates, both rows will be returned and you are 'ORDER'ing by date.
One of the problems ?sandip? has with the code he posted is that he?s getting a Cartesian Product of the two tables. I also don?t understand what he is trying to do with the ORDER BY MAX(? but says he want to ORDER by vdate ascending?? |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| I understoud that he was looking for the amount entry that had the largest/latest date. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
| This should not be difficult, but wife and dinner are calling. Continue tomorrow? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Again, i may be way off the mark, but my initial guess was/is that the desired output is the "right date" and both amounts one "result".
If some good sample data data and expected output were posted, we'd not have to guess. . . . |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
sandip,
Can you give use some examples of date for both tables and expexted results? This would help us a great deal. As you can see there is some variation in the way different people read the requirements.
thanks, |
|
| Back to top |
|
|
sandiptcs
New User
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
Hi,
Many thanks for the discussion so far.
To clarify my requirement
I have the amount field (BEDRAG) and the date field (VALUEDATUM/VALUE_DATE) in both the the tables
I want to have an SQL query so that it gives me a output of all amounts from both the tables sorted by value date(from both the tables)
The output from the query below is what I desire.
| Code: |
SELECT A.BEDRAG as samt,
A.VALUTADATUM as vdate,
FROM TMUTATION A
WHERE A.BANKRKGNR = 423708724
union all
SELECT B.BEDRAG as samt,
B.VALUE_DATE as vdate,
FROM TSUBTOTAL B
WHERE B.SUB_ACCOUNT = 423708724
ORDER BY vdate |
I guess this query will work if I declare a cursor and embed in a COBOL program as shown below
| Code: |
DECLARE CURSOR ABC ON
SELECT A.BEDRAG as samt,
A.VALUTADATUM as vdate,
FROM TMUTATION A
WHERE A.BANKRKGNR = :var1
union all
SELECT B.BEDRAG as samt,
B.VALUE_DATE as vdate,
FROM TSUBTOTAL B
WHERE B.SUB_ACCOUNT = :var2
ORDER BY vdate |
But there is one problem. The number columns in both the tables joined by UNION has to match and their datatype must be the same . I wanted to SELECT another additional field called TIMESTAMP from TMUTATION table only but , with a mismatch in the number of colums the query doesnot work.
I dont know how ORDER BY MAX(VALUE_DATE, VALUEDATUM) will work |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Please also post what you want the output data to look like. |
|
| Back to top |
|
|
sandiptcs
New User
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
ok here is what I would like my data to be
TMUTATION table
BEDRAG VALUEDATUM
300 27-9-2006
500 27-12-2006
100 01-01-2007
TSUBTOT table
BEDRAG VALUE_DATE
1000 12-12-2006
200 10-11-2006
150 02-01-2007
desired output
Value date amt
27-9-2006 300
10-11-2006 200
12-12-2006 1000
27-12-2006 500
01-01-2007 100
02-01-2007 150
Please also note my concern in my last post
| Quote: |
| But there is one problem. The number columns in both the tables joined by UNION has to match and their datatype must be the same . I wanted to SELECT another additional field called TIMESTAMP from TMUTATION table only but , with a mismatch in the number of colums the query doesnot work. |
|
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
sandip,
for the rows that come from the table without the timestamp, what do you want in the timestamp for thoughs columns? spaces, null? |
|
| Back to top |
|
|
sandiptcs
New User
Joined: 29 Nov 2006
Posts: 19
Location: amsterdam
|
|
|
|
for the rows that come from the table without timestamp
i would like have spaces in the timestamp
OR
Just curious, is it possible that I have some hardcoded values in them, like 1701200600000000 ....  guess it would not be possible |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Sandip,
One solution would be to declare 2 cursors with the ordered-by-date - one for each table.
Then fetch rows from the cursors the same way you would process 2 flat files that you wanted to match. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Like This?
| Code: |
SELECT *
FROM SESSION.TMUTATION
;
+---------------------------------------------------+
| BEDRAG | VALUEDATUM | TIMESTAMP |
+---------------------------------------------------+
1_| 300 | 2006-09-27 | 2006-09-27-14.02.26.472344 |
2_| 500 | 2006-12-27 | 2006-12-27-16.32.41.678235 |
3_| 100 | 2007-01-01 | 2007-01-01-09.14.11.924119 |
+---------------------------------------------------+
SELECT *
FROM SESSION.TSUBTOT
;
+----------------------+
| BEDRAG | VALUE_DATE |
+----------------------+
1_| 1000 | 2006-12-12 |
2_| 200 | 2006-11-10 |
3_| 150 | 2007-01-02 |
+----------------------+
SELECT VALUEDATUM AS DT,
BEDRAG AS AMT,
TIMESTAMP AS TS
FROM SESSION.TMUTATION
UNION ALL
SELECT VALUE_DATE AS DT,
BEDRAG AS AMT,
CAST(NULL AS CHAR(26)) AS TS
FROM SESSION.TSUBTOT
ORDER BY DT
;
+---------------------------------------------------+
| DT | AMT | TS |
+---------------------------------------------------+
1_| 2006-09-27 | 300 | 2006-09-27-14.02.26.472344 |
2_| 2006-11-10 | 200 | ? |
3_| 2006-12-12 | 1000 | ? |
4_| 2006-12-27 | 500 | 2006-12-27-16.32.41.678235 |
5_| 2007-01-01 | 100 | 2007-01-01-09.14.11.924119 |
6_| 2007-01-02 | 150 | ? |
+---------------------------------------------------+
SELECT VALUEDATUM AS DT,
BEDRAG AS AMT,
TIMESTAMP AS TS
FROM SESSION.TMUTATION
UNION ALL
SELECT VALUE_DATE AS DT,
BEDRAG AS AMT,
'1701200600000000'
FROM SESSION.TSUBTOT
ORDER BY DT
;
+---------------------------------------------------+
| DT | AMT | TS |
+---------------------------------------------------+
1_| 2006-09-27 | 300 | 2006-09-27-14.02.26.472344 |
2_| 2006-11-10 | 200 | 1701200600000000 |
3_| 2006-12-12 | 1000 | 1701200600000000 |
4_| 2006-12-27 | 500 | 2006-12-27-16.32.41.678235 |
5_| 2007-01-01 | 100 | 2007-01-01-09.14.11.924119 |
6_| 2007-01-02 | 150 | 1701200600000000 |
+---------------------------------------------------+
|
|
|
| Back to top |
|
|
|
|
