Monday, March 12, 2012
how to get result from subtracting select statement
2 select statement to get a result.this is due to not knowing how to
use coding in mssql...i'm using visual studio 2005...this is the select
statement...
SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
how can i deduct those to...let say
<<<if
sum1-sum2>0 then
sum3
else
sum4......>>Select case sum1-sum2>0,sum3,sum4 end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa)
wiraperkasa wrote:
> maybe my word will be confusing for you all..actually i want to deduct
> 2 select statement to get a result.this is due to not knowing how to
> use coding in mssql...i'm using visual studio 2005...this is the select
> statement...
> SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> how can i deduct those to...let say
> <<<if
> sum1-sum2>0 then
> sum3
> else
> sum4......>>|||Sorry,Some typos in my Previous mail.Please correct that by this query
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil ='76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)|||i have rewrite the code but still i got and error..this is the select
statement..
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)
the error said that "incorrect syntax near ')'..."could somebody help
me again with this:)|||Forgot to add Alias
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A|||thank you rajdeep...but now its say "invalid column name 'sum3' and
'sum4'..."|||I mean sum3 and sum4 are the column names which you want to
show.replace with appropriate column names as you wish
I just mentioned them for your reference
wiraperkasa wrote:
> thank you rajdeep...but now its say "invalid column name 'sum3' and
> 'sum4'..."|||i change it to this..
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A
and at the end its only show sum1 and sum2...i still lost..plz help
me..rajdeep..what do u mean by the last post?replace it with the column
from the table?i'm confuse..coz sum3 and sum4 should be another
field that are created from the "case" statement right..or am i
wrong...i'm totally lost:(
Friday, February 24, 2012
How to get list (text) of all tables and columns?
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?
--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal
It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION
If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.
Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
Hello,
To get a list of user tables use this query:
select name from ssysobjects where type = 'u'
And for a list of columns related for all objects (tables,views,sps)
select name, object_name(id) from sysindexes
The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.
Regards,
-Manoj