Showing posts with label word. Show all posts
Showing posts with label word. Show all posts

Monday, March 12, 2012

how to get result from subtracting select statement

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......>>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?

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"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