szenczi
Posts: 67
|
| Posted: 09/10/2008, 6:21 AM |
|
In one of my report, generated by CCS Report Builder I've got a problem.
First I used the next query which is working:
SELECT arjegyzek.dij AS arjegyzek_dij, hallgatok.nev AS hallgatok_nev, tanar.nev AS tanar_nev, fizetendo, kulonbozet, pluszora, havidij, havioraszam, evhonap, tanfolyam, tanfkod, telepules, oktforma
FROM (((((tanfhallg INNER JOIN tandij ON tandij.hazon = tanfhallg.hazon AND tanfhallg.tfolyazon = tandij.tanfolyam) INNER JOIN hallgatok ON hallgatok.hazon = tanfhallg.hazon) INNER JOIN tanfolyam ON tanfolyam.tfolyazon = tanfhallg.tfolyazon) LEFT JOIN arjegyzek ON arjegyzek.arjkod = tandij.pluszoradij) INNER JOIN tanar ON tanfolyam.tanazon = tanar.tanazon) INNER JOIN telepules ON telepules.irsz = tanfolyam.tanfirsz
WHERE tandij.fizetve = {Expr0} AND tanfolyam.tanazon = {s_tanar} AND tanfolyam.oktforma = {s_oktforma} AND ( tandij.evhonap >= '{s_monthbegin}' AND tandij.evhonap <= '{s_monthend}' )
But the next isn't get any result:
SELECT arjegyzek.dij AS arjegyzek_dij, hallgatok.nev AS hallgatok_nev, tanar.nev AS tanar_nev, fizetendo, kulonbozet, pluszora, havidij, havioraszam, evhonap, tanfolyam, tanfkod, telepules, oktforma,
havidij / havioraszam as origoradij
FROM (((((tanfhallg INNER JOIN tandij ON tandij.hazon = tanfhallg.hazon AND tanfhallg.tfolyazon = tandij.tanfolyam) INNER JOIN hallgatok ON hallgatok.hazon = tanfhallg.hazon) INNER JOIN tanfolyam ON tanfolyam.tfolyazon = tanfhallg.tfolyazon) LEFT JOIN arjegyzek ON arjegyzek.arjkod = tandij.pluszoradij) INNER JOIN tanar ON tanfolyam.tanazon = tanar.tanazon) INNER JOIN telepules ON telepules.irsz = tanfolyam.tanfirsz
WHERE tandij.fizetve = {Expr0} AND tanfolyam.tanazon = {s_tanar} AND tanfolyam.oktforma = {s_oktforma} AND ( tandij.evhonap >= '{s_monthbegin}' AND tandij.evhonap <= '{s_monthend}' )
The only difference is the highlighted row. I tried these in an SQL administrator program and there is working both of them!
Why?
Thanks, Norbert
_________________
CCS5.1, PhP, MySQL |
 |
 |
quasimidi
Posts: 151
|
| Posted: 09/10/2008, 8:17 AM |
|
Hi Norbert!
Did you tried this?
SELECT arjegyzek.dij AS arjegyzek_dij, hallgatok.nev AS hallgatok_nev, tanar.nev AS tanar_nev, fizetendo, kulonbozet, pluszora, havidij, havioraszam, evhonap, tanfolyam, tanfkod, telepules, oktforma,
(havidij / havioraszam) AS origoradij
FROM (((((tanfhallg INNER JOIN tandij ON tandij.hazon = tanfhallg.hazon AND tanfhallg.tfolyazon = tandij.tanfolyam) INNER JOIN hallgatok ON hallgatok.hazon = tanfhallg.hazon) INNER JOIN tanfolyam ON tanfolyam.tfolyazon = tanfhallg.tfolyazon) LEFT JOIN arjegyzek ON arjegyzek.arjkod = tandij.pluszoradij) INNER JOIN tanar ON tanfolyam.tanazon = tanar.tanazon) INNER JOIN telepules ON telepules.irsz = tanfolyam.tanfirsz
WHERE tandij.fizetve = {Expr0} AND tanfolyam.tanazon = {s_tanar} AND tanfolyam.oktforma = {s_oktforma} AND ( tandij.evhonap >= '{s_monthbegin}' AND tandij.evhonap <= '{s_monthend}' )
_________________
RS |
|
|
|
szenczi
Posts: 67
|
| Posted: 09/11/2008, 3:02 AM |
|
The result is same.
Norbert
_________________
CCS5.1, PhP, MySQL |
|
|
|
quasimidi
Posts: 151
|
| Posted: 09/11/2008, 3:14 AM |
|
Norbi,
Could you post the db structure for this project, please?
I created a sample db table with 3 field :rowid, havidij, havioraszam
Created a sample query:
SELECT (teszt.`havidij` / teszt.`havioraszam`) AS vazzeg FROM teszt
Works perfectly!
Robert (Bp) :)
_________________
RS |
|
|
|
szenczi
Posts: 67
|
| Posted: 09/11/2008, 4:56 AM |
|
As I mentioned, in SQL manager (I use SQLyog) the query works perfectly. My problem is occur only in the CCS report. I wonder what's the difference or CCS why not handle the math operation? I can choose the generated field name 'origoradij' from the drop down list in the designer but the result is empty. (Not only this field but the whole report will be empty.)
Norbert
_________________
CCS5.1, PhP, MySQL |
|
|
|
szenczi
Posts: 67
|
| Posted: 09/11/2008, 5:08 AM |
|
Esztergom :)
_________________
CCS5.1, PhP, MySQL |
|
|
|
quasimidi
Posts: 151
|
| Posted: 09/11/2008, 5:25 AM |
|
Try to use the query builder's custom query option.
In that case, you have to specify the where parameters, and you can write your own query.
On the other side, you might have to check the generated php code, and check the generated sql.
_________________
RS |
|
|
|
szenczi
Posts: 67
|
| Posted: 09/11/2008, 5:55 AM |
|
...
WHERE tandij.fizetve = {Expr0} AND
tanfolyam.tanazon = {s_tanar} AND tanfolyam.oktforma = {s_oktforma} AND ( tandij.evhonap >= '{s_monthbegin}' AND tandij.evhonap <= '{s_monthend}' )
The source of the problem is in the highlighted rows! But I don't know yet what is that.
Something is different in the behaviour with the math op. (Normally I get no rows instead of all rows.)
Norbert
_________________
CCS5.1, PhP, MySQL |
|
|
|
quasimidi
Posts: 151
|
| Posted: 09/11/2008, 6:06 AM |
|
Hmmm... is the tandij.evhonap field type varchar? Then the comparison in your WHERE statement will not work. In this case you have to change the tandij.evhonap filed type to DATE.
Hope this helps.
_________________
RS |
|
|
|
szenczi
Posts: 67
|
| Posted: 09/11/2008, 6:46 AM |
|
No, it's a date.
The original query (in the first post) is working well. The difference is occur only after inserting the math op.
I solve it in another way bit I"d like to know the solution.
Thanks,
Norbert
_________________
CCS5.1, PhP, MySQL |
|
|
|
|
