SQL,delphi2010
Hi guys i have big problem if you can help me.
I HAVE TO GET THIS TABLE:
RESULT TABLE
TRZ N_TRZ KON N_KON 2011
--------------------------
1001 DOOOO 2300 EXP2 300
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1002 DOOOO 7500 EXP 200
1002 BOOOO 2300 EXP 100
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
AND I HAVE TO USE THESE TABLES TO GET TABLE UP?
TABLE 1
DATE TRZ VRS VRSR CASH
--------------------------
05.01.11 1001 F 100
13.02.11 1001 K 100
05.03.11 1001 L 100
12.04.11 1002 F 100
05.05.11 1002 F 100
05.05.11 1002 _ B 100
05.05.11 1002 _ H 100
05.11.11 1001 D 100
05.11.11 1002 K 200
15.01.11 1001 D 100
23.03.11 1001 N 100
05.01.11 1002 K 200
28.04.11 1001 F 100
05.01.11 1002 V 1000
17.06.11 1001 F 100
05.01.11 1002 H 1500
16.01.11 1001 F 100
12.03.11 1001 N 100
TABLE 2
KON N_KON
--------------
2300 EXP2
7500 EXP
7411 EXP3
7510 EXP1
7511 EXP4
7561 FXP6
TABLE 3
VRS KON
------------
F 7500
L 7500
K 7510
D 2300
N 2300
V 7411
H 7511
B 7610
TABLE 4
TRZ N_TRZ
--------------
1001 DOOOO
1002 BOOOO
IF YOUL LOOK IN TABLE 1 YOU WILL SEE THAT THERE ARE SOMEWHERE IN FIELD
"VRS" VALUE "_"
IF THE VALUE IS "_" THEN IT SHOULD LOOK IN FIELD VRSR TO SERACH IN TABLE 3
FOR "KON".
AND I HAVE ONE MORE PROBLEM I HAVE CHECKBOXES FOR YEARS
FOR EXAMPLE 2009, 2010, 2011.
IF I CHECK FOR EXAMPLE 2009, 2010 AND 2011 THE TABLE SHOULD LOOK LIKE THIS
TABLE 5
AND THESE TABLES (TABLE 1)ARE IN ANOTHER DATABASESAND ARE CALLED
and these
dabases are called (KUPID09, KUPID10, KUPID11), AND OTHER TABLES
(TABLE 2, TABLE 3, TABLE 4) ARE SAME IN EVERY DATABASE.
TABLE 5
TRZ N_TRZ KON N_KON 2009 2010 2011
--------------------------
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1001 DOOOO 2300 EXP2 300
1002 DOOOO 7500 EXP 200
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
1002 BOOOO 2300 EXP 100
and values hve to be in fileds 2009, 2010
I DON'T KNOW HOW TO SOLVE BUT I WOULD BE GRATEFUL IF YOU CAN MANAGE IT,
THX A LOT guys
P.S. HOPE YOU UNDERSTAND WHAT I WROTE
This is more sql then delphi.
I HAVE TO GET THIS TABLE:
RESULT TABLE
TRZ N_TRZ KON N_KON 2011
--------------------------
1001 DOOOO 2300 EXP2 300
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1002 DOOOO 7500 EXP 200
1002 BOOOO 2300 EXP 100
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
AND I HAVE TO USE THESE TABLES TO GET TABLE UP?
TABLE 1
DATE TRZ VRS VRSR CASH
--------------------------
05.01.11 1001 F 100
13.02.11 1001 K 100
05.03.11 1001 L 100
12.04.11 1002 F 100
05.05.11 1002 F 100
05.05.11 1002 _ B 100
05.05.11 1002 _ H 100
05.11.11 1001 D 100
05.11.11 1002 K 200
15.01.11 1001 D 100
23.03.11 1001 N 100
05.01.11 1002 K 200
28.04.11 1001 F 100
05.01.11 1002 V 1000
17.06.11 1001 F 100
05.01.11 1002 H 1500
16.01.11 1001 F 100
12.03.11 1001 N 100
TABLE 2
KON N_KON
--------------
2300 EXP2
7500 EXP
7411 EXP3
7510 EXP1
7511 EXP4
7561 FXP6
TABLE 3
VRS KON
------------
F 7500
L 7500
K 7510
D 2300
N 2300
V 7411
H 7511
B 7610
TABLE 4
TRZ N_TRZ
--------------
1001 DOOOO
1002 BOOOO
IF YOUL LOOK IN TABLE 1 YOU WILL SEE THAT THERE ARE SOMEWHERE IN FIELD
"VRS" VALUE "_"
IF THE VALUE IS "_" THEN IT SHOULD LOOK IN FIELD VRSR TO SERACH IN TABLE 3
FOR "KON".
AND I HAVE ONE MORE PROBLEM I HAVE CHECKBOXES FOR YEARS
FOR EXAMPLE 2009, 2010, 2011.
IF I CHECK FOR EXAMPLE 2009, 2010 AND 2011 THE TABLE SHOULD LOOK LIKE THIS
TABLE 5
AND THESE TABLES (TABLE 1)ARE IN ANOTHER DATABASESAND ARE CALLED
and these
dabases are called (KUPID09, KUPID10, KUPID11), AND OTHER TABLES
(TABLE 2, TABLE 3, TABLE 4) ARE SAME IN EVERY DATABASE.
TABLE 5
TRZ N_TRZ KON N_KON 2009 2010 2011
--------------------------
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1001 DOOOO 2300 EXP2 300
1002 DOOOO 7500 EXP 200
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
1002 BOOOO 2300 EXP 100
and values hve to be in fileds 2009, 2010
I DON'T KNOW HOW TO SOLVE BUT I WOULD BE GRATEFUL IF YOU CAN MANAGE IT,
THX A LOT guys
P.S. HOPE YOU UNDERSTAND WHAT I WROTE
This is more sql then delphi.
Last Comment
Geert G
homework ?
do you have database links between the primary and secondary databases ?
ASKER
no
ASKER
i wish it is homework
first get the unsummarized data
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
from table1 T1
join table4 T4 on T4.N_TRZ = T1.TRZ
join table3 T3 on
((T3.VRS = T1.VRS) and (T1.VRS <> '_'))
or
((T3.VRS = T1.VRSR) and (T1.VRS = '_'))
join table2 T2 on T2.KON = T3.KON
then just summarize by year
in delphi you can set the column for a specific year visible according to the state of the corresponding checkbox
before or after loading the data
grids like quantumgrid from devexpress have this capability
with dbgrid, set the width to 0 for that column to hide it
SELECT A.TRZ, A.N_TRZ, A.KON, A.N_KON,
SUM(CASE WHEN YEAR(A.DATE) = 2009 THEN A.CASH END) [2009],
SUM(CASE WHEN YEAR(A.DATE) = 2010 THEN A.CASH END) [2010],
SUM(CASE WHEN YEAR(A.DATE) = 2011 THEN A.CASH END) [2011]
FROM (
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
from table1 T1
join table4 T4 on T4.N_TRZ = T1.TRZ
join table3 T3 on
((T3.VRS = T1.VRS) and (T1.VRS <> '_'))
or
((T3.VRS = T1.VRSR) and (T1.VRS = '_'))
join table2 T2 on T2.KON = T3.KON) as A
GROUP BY A.TRZ, A.N_TRZ, A.KON, A.N_KON
in delphi you can set the column for a specific year visible according to the state of the corresponding checkbox
before or after loading the data
grids like quantumgrid from devexpress have this capability
with dbgrid, set the width to 0 for that column to hide it
ASKER
Great thx but i only manage for one year and it works fine for one year, but i didn't understand the rest.
the years are in different databases (KUPID09, KUPID10, KUPID11), and i will have probably every next year new database (KUPID12, KUPID13, KUPID....).
So my question is how to add year column (2010, 2011) .
I need to use table KUPID09.dbo.T1, KUPID10.dbo.T1, etc...
It is not problem if I take every database but i don't know how to get all datas from every database.
I manage for one year, but how then collect for another year datas in the same table
the years are in different databases (KUPID09, KUPID10, KUPID11), and i will have probably every next year new database (KUPID12, KUPID13, KUPID....).
So my question is how to add year column (2010, 2011) .
I need to use table KUPID09.dbo.T1, KUPID10.dbo.T1, etc...
It is not problem if I take every database but i don't know how to get all datas from every database.
I manage for one year, but how then collect for another year datas in the same table
pick one database as your primary database
add the others as linked servers
(only works if there is a wan/lan connection possible between the servers)
add the others as linked servers
(only works if there is a wan/lan connection possible between the servers)
ASKER
I have everything on same server but different databases.
ASKER
i don't know how to do it.
either change your connection to the other database
or run "use dbname" in your current connection to change it to the other database
then run the query again
if you have access with 1 account to all databases, you can do it as you say
with KUPID09.dbo.T1, etc
or run "use dbname" in your current connection to change it to the other database
then run the query again
if you have access with 1 account to all databases, you can do it as you say
with KUPID09.dbo.T1, etc
ASKER
yes i can change to another database, but if i do that i will lost my first query
for example
1. query for kupid09
TRZ N_TRZ KON N_KON 2009
--------------------------
1001 DOOOO 7500 EXP
1001 DOOOO 7510 EXP1
1001 DOOOO 2300 EXP2
1002 DOOOO 7500 EXP
1002 BOOOO 7411 EXP3
1002 BOOOO 7510 EXP1
1002 BOOOO 7511 EXP4
1002 BOOOO 7561 FXP6
1002 BOOOO 2300 EXP
2.query for kupid10
TRZ N_TRZ KON N_KON 2010
--------------------------
1001 DOOOO 7500 EXP
1001 DOOOO 7510 EXP1
1001 DOOOO 2300 EXP2
1002 DOOOO 7500 EXP
1002 BOOOO 7411 EXP3
1002 BOOOO 7510 EXP1
1002 BOOOO 7511 EXP4
1002 BOOOO 7561 FXP6
1002 BOOOO 2300 EXP
3. query for kupid11
TRZ N_TRZ KON N_KON 2011
--------------------------
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1001 DOOOO 2300 EXP2 300
1002 DOOOO 7500 EXP 200
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
1002 BOOOO 2300 EXP 100
i can put them in one table if i use TClientDataSet, but i want to get them from one query.
without TCLientDataSet
Can I make query in every kupid and just join next year to existing table?
p.s. i have access to all databases
for example
1. query for kupid09
TRZ N_TRZ KON N_KON 2009
--------------------------
1001 DOOOO 7500 EXP
1001 DOOOO 7510 EXP1
1001 DOOOO 2300 EXP2
1002 DOOOO 7500 EXP
1002 BOOOO 7411 EXP3
1002 BOOOO 7510 EXP1
1002 BOOOO 7511 EXP4
1002 BOOOO 7561 FXP6
1002 BOOOO 2300 EXP
2.query for kupid10
TRZ N_TRZ KON N_KON 2010
--------------------------
1001 DOOOO 7500 EXP
1001 DOOOO 7510 EXP1
1001 DOOOO 2300 EXP2
1002 DOOOO 7500 EXP
1002 BOOOO 7411 EXP3
1002 BOOOO 7510 EXP1
1002 BOOOO 7511 EXP4
1002 BOOOO 7561 FXP6
1002 BOOOO 2300 EXP
3. query for kupid11
TRZ N_TRZ KON N_KON 2011
--------------------------
1001 DOOOO 7500 EXP 400
1001 DOOOO 7510 EXP1 200
1001 DOOOO 2300 EXP2 300
1002 DOOOO 7500 EXP 200
1002 BOOOO 7411 EXP3 1000
1002 BOOOO 7510 EXP1 400
1002 BOOOO 7511 EXP4 1500
1002 BOOOO 7561 FXP6 100
1002 BOOOO 2300 EXP 100
i can put them in one table if i use TClientDataSet, but i want to get them from one query.
without TCLientDataSet
Can I make query in every kupid and just join next year to existing table?
p.s. i have access to all databases
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thx a lot,
thats great i manage it but just one small thing,if i put in line three T4.N_TRZ = T1.TRZ
it gives me nothing, but if i put T4.TRZ = T1.TRZ then it gives me results but multiply some rows.
thats great i manage it but just one small thing,if i put in line three T4.N_TRZ = T1.TRZ
it gives me nothing, but if i put T4.TRZ = T1.TRZ then it gives me results but multiply some rows.
ASKER
eh, yes and if i exclude T4.N_TRZ from line 1
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
then it gives me ok, but without n_trz ofcourse-
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
then it gives me ok, but without n_trz ofcourse-
Delphi
Delphi is the most powerful Object Pascal IDE and component library for cross-platform Native App Development with flexible Cloud services and broad IoT connectivity. It provides powerful VCL controls for Windows 10 and enables FMX development for Windows, Mac and Mobile. Delphi is your choice for ultrafast Enterprise Strong Development™. Look for increased memory for large projects, extended multi-monitor support, improved Object Inspector and much more. Delphi is 5x faster for development and deployment across multiple desktop, mobile, cloud and database platforms including 32-bit and 64-bit Windows 10.
60K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
