help me please complex Sql Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help me please complex Sql Statement

Author

Topic

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-14 : 09:25:17

how to make it more faster and readable please help :

select kontoja, isnull((select sum(t1.debi/v1.kursi) from transaksionet t1, valutat v1 where t1.idur <> 0 and t1.valuta = v1.id and t1.kontoja = t.kontoja),0) as debi,
isnull((select sum(t1.kredi/v1.kursi) from transaksionet t1, valutat v1 where t1.idur <> 0 and t1.valuta = v1.id and t1.kontoja = t.kontoja ),0) as kredi,
isnull((SELECT sum(t1.debi/v1.kursi) - sum(t1.kredi/v1.kursi) from transaksionet t1, valutat v1 where IDUR = 0 and t1.kontoja = t.kontoja and t1.valuta = v1.id),0) as saldofill from transaksionet t, [plan] p where t.kontoja = p.account2 group by t.kontoja

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 09:49:44

Ok,

First, here's it formatted:


  SELECT  kontoja
	, ISNULL((SELECT  SUM(t1.debi/v1.kursi) 
  		    FROM  transaksionet t1
			, valutat v1 
 		   WHERE  t1.idur <> 0 
   		     AND  t1.valuta = v1.id 
   		     AND  t1.kontoja = t.kontoja),0) as debi
	, ISNULL((SELECT SUM(t1.kredi/v1.kursi) 
		    FROM  transaksionet t1
			, valutat v1 
		   WHERE  t1.idur <> 0 
		     AND t1.valuta = v1.id 
		     AND t1.kontoja = t.kontoja ),0) as kredi
	, ISNULL((SELECT SUM(t1.debi/v1.kursi) - SUM(t1.kredi/v1.kursi) 
		    FROM  transaksionet t1
			, valutat v1 
		   WHERE IDUR = 0 
		     AND t1.kontoja = t.kontoja 
		     AND t1.valuta = v1.id),0) as saldofill 
    FROM     transaksionet t
	, [plan] p 
   WHERE t.kontoja = p.account2 
GROUP BY t.kontoja

Second, it doesn't make sense to me.

What it says is, for every unique value in kontoja, assign the exact same value you've defined as debi, kredi and saldofill.

You will not get any different values, except for kontoja in your result set.

How does kontoja relate to the entire results based on your predicates in the SELECT (SELECT statements?

And as far a speed goes, I think the following should do excatly the same thing (you don't need the group by BTW):



SELECT kontoja, Debi, Kredi, saldofill  FROM 
 (SELECT  ISNULL(SUM(t1.debi/v1.kursi),0)  AS Debi
	, ISNULL(SUM(t1.kredi/v1.kursi),0) AS Kredi
    FROM  transaksionet t1
	, valutat v1 
   WHERE  t1.idur <> 0 
     AND  t1.valuta = v1.id 
     AND  t1.kontoja = t.kontoja) AS DerivedTable1
,(SELECT ISNULL(SUM(t1.debi/v1.kursi) - SUM(t1.kredi/v1.kursi),0) As saldofill 
    FROM  transaksionet t1
	, valutat v1 
   WHERE IDUR = 0 
     AND t1.kontoja = t.kontoja 
     AND t1.valuta = v1.id) AS DerivedTable2
,transaksionet t
, [plan] p 
   WHERE t.kontoja = p.account2

Brett

8-)

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 02:59:31

thanks for help but i am getting this message :

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-15 : 13:03:09

OH Shoot!

They're coorelated....hold on...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-15 : 13:09:28

OK,

How does this work for you?



SELECT t.kontoja, DerivedTable1.Debi, DerivedTable1.Kredi, DerivedTable2.saldofill  FROM 
 (SELECT  ISNULL(SUM(t1.debi/v1.kursi),0)  AS Debi
	, ISNULL(SUM(t1.kredi/v1.kursi),0) AS Kredi
	, t1.kontoja
    FROM  transaksionet t1
	, valutat v1 
   WHERE  t1.idur <> 0 
     AND  t1.valuta = v1.id 
GROUP BY  t1.kontoja) AS DerivedTable1
,(SELECT ISNULL(SUM(t1.debi/v1.kursi) - SUM(t1.kredi/v1.kursi),0) As saldofill 
	, t1.kontoja
    FROM  transaksionet t1
	, valutat v1 
   WHERE IDUR = 0  
     AND t1.valuta = v1.id
GROUP BY  t1.kontoja) AS DerivedTable2
,transaksionet t
, [plan] p 
   WHERE t.kontoja = p.account2
     AND t.kontoja = DerivedTable1.kontoja
     AND t.kontoja = DerivedTable2.kontoja

Brett

8-)

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-16 : 02:15:26

this works much faster

SELECT d1.kontoja, sum(d1.Debi), sum(d1.Kredi), sum(d1.saldofill) FROM

(SELECT kontoja,ISNULL(SUM(t1.debi/v1.kursi),0) AS Debi
,ISNULL(SUM(t1.kredi/v1.kursi),0) AS Kredi,0 as saldofill
FROM transaksionet t1, valutat v1 WHERE t1.idur <> 0 AND t1.valuta = v1.id group by kontoja
UNION SELECT kontoja,0 as debi, 0 as kredi, ISNULL(SUM(t1.debi/v1.kursi) - SUM(t1.kredi/v1.kursi),0) As saldofill
FROM transaksionet t1, valutat v1 WHERE IDUR = 0 AND t1.valuta = v1.id group by kontoja) d1

group by d1.kontoja

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources