Database

Fetch DB2 table data into MS-Excel

17-Apr-08 1:33

Then the result you have shown does not match the data in the table.

Sql Query

~V~16-Apr-08 1:44

~V~

16-Apr-08 1:44

I m facing a problem in this query

select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
where(a.eventtype='Single Month' or(a.eventtype='All Month' and <br />
(select substring(convert(nvarchar,edate,102),9,4) from event where eventtype='All Month')>( select substring(convert(nvarchar,getdate(),102),9,4)) ))order by a.eventdateselect * from event<br />
<br />

In event table there are some fields like eventid,eventdate,eventtype n etc
In user_info there are userid, name n etc fields

i wanna get all record when
1> eventtype is Single Month and the eventdate is in current month n eventdate is heigher than currentdate
2> eventype is All Month and eventdate is heigher than current date

eventid -----eventdate ------- eventtype
1 -----2008-04-15 00:00:00 ----- All Month

2 -----2008-04-20 00:00:00 ----- Single Month

3 -----2008-04-16 00:00:00 ----- Single Month

4 -----2008-04-16 00:00:00 ----- All Month

The answer shd be eventid 2,3,4

The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth

Re: Sql Query

Ashfield16-Apr-08 4:43

Ashfield

16-Apr-08 4:43

Try this

select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
WHERE a.eventdate > getdate()<br />
AND (<br />
(a.eventtype = 'Single Month' and left(convert(varchar,getdate(),102),7) = left(convert(varchar,edate,102),7))<br />
OR (a.eventtype = 'All Month')<br />
)

Bob
Ashfield Consultants Ltd

Jay_se15-Apr-08 22:57

Jay_se

15-Apr-08 22:57

Hi All,

I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.

Also, I need the validation in the Excel sheet itself. (DB2 database credetials)

Can you pls help me to get start?

Thanks,
Jeyakumar Smile | :)

Jey

modified on Wednesday, April 16, 2008 6:11 AM

Re: Fetch DB2 table data into MS-Excel

ChandraRam16-Apr-08 1:27

Re: Fetch DB2 table data into MS-Excel

16-Apr-08 1:27

Jay_se wrote:
I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.

Also, I need the validation in the Excel sheet itself. (DB2 database credetials)

I believe Excel VBA does ADO, so you should be able to connect to the DB2 database and do your stuff. For connection information, look up www.connectionstrings.com[^].

Jay_se16-Apr-08 19:49

Jay_se

16-Apr-08 19:49

Thanks ChandraRam, It is helpful. Smile | :)

Jey

Regarding sum of a column

mrcsn15-Apr-08 21:41

15-Apr-08 21:41

Hi ,

im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).

can any body tell me the query for this ,

Thank u.
C.S.n

Mycroft Holmes15-Apr-08 22:21

Mycroft Holmes

15-Apr-08 22:21

I feel I have the wrong handle on your problem but...

If you want the additional of col1 + col2 then it is the same method as the multiply
If you are after the sum of all the records preceding the current record then you need to look into running totals (depends on your DB)

if you want the sum of the 2 cols included in the result set you need to do the query 2 times, 1 to get the total into a var and the seconed to include it in your result set

You really need to define your problem a little clearer. Remember we have not gone through the process of getting where you are stuck and do not have the code in front of us.

Never underestimate the power of human stupidity
RAH

mrcsn21-Apr-08 19:56

21-Apr-08 19:56

Hi ,

i will explain where i stuck ..

i'm having two tables transaction and pricing . in transaction table i'm

having number of seats column and price/seat in pricing . here i want a

column which displays (no. of seats * price/ seat ) and sum of all these

rows as next row or separate column.i'm getting(no. of seats * price/seat)

using join but im not getting how to do sum of all these rows .

can u tell me this pls ..

thanks
csn

Pete O'Hanlon15-Apr-08 23:24

Pete O'Hanlon

15-Apr-08 23:24

Depending on whether you are trying to just add the two columns together, or add the sum of the columns together, you could either do:

select tbl1.column1 + tbl2.column as summation

select sum(tbl.column1 + tbl2.column) as total

Deja View - the feeling that you've seen this post before.

My blog | My articles

ChandraRam16-Apr-08 1:23

16-Apr-08 1:23

mrsuman wrote:
im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).

can any body tell me the query for this ,

Is this what you are looking for:

select tbl1.column1,tbl2.column2,tbl1.column1*tbl2.column2 as Result, sum(tb1.column1*tbl2.column2) as Total

This query will have the Total column as the same value for all the rows.

mrcsn17-Apr-08 2:51

17-Apr-08 2:51

Hi,

Sorry for late reply .

here im having price of seat in one table and no. of seats in another table.
i want to get result(Price * No. Of Seats ) as amount to pay as one cloumn and total of these values as another column.

i think now u can understand my problem ..

can u tell me the query how can i do this .

thanks .

mrcsn21-Apr-08 20:00

21-Apr-08 20:00

i will explain where i stuck ..

i'm having two tables transaction and pricing . in transaction table i'm

having number of seats column and price/seat in pricing . here i want a

column which displays (no. of seats * price/ seat ) and sum of all these

rows as next row or separate column.i'm getting(no. of seats * price/seat)

using join but im not getting how to do sum of all these rows .

can u tell me this pls ..

thanks
csn

ChandraRam21-Apr-08 20:10

21-Apr-08 20:10

Can you give an example output you want from this query?

mrcsn21-Apr-08 20:27

21-Apr-08 20:27

Thanks for u r reply ..

ex :
result
------
200
350
500
400
250

in next row or separate column i want sum of these rows .

ChandraRam21-Apr-08 20:53

sqldmo backup issue (Cannot open backup device)

21-Apr-08 20:53

If having it in the same row is Ok, then my earlier query should work for you (providing of course that you specify the correct join condition).

JoZ CaVaLLo15-Apr-08 21:36

JoZ CaVaLLo

15-Apr-08 21:36

Hi...

I'm trying to get programmatically a backup file of my database, here is the code:

<br />
//Get a temporary file<br />
string BakFilePath = System.IO.Path.GetTempFileName();<br />
<br />
//create a backup class instance<br />
SQLDMO.Backup bak = new SQLDMO.BackupClass();<br />
//set the backup device = files property ( easy way )<br />
bak.Devices = bak.Files;<br />
//where to store<br />
bak.Files = "[" + BakFilePath + "]";<br />
//set the database to backup<br />
bak.Database = "MyDB";<br />
bak.BackupSetDescription = "Something";<br />
<br />
//perform the backup<br />
bak.SQLBackup(srv);<br />

Under VS2005 c# SQL Server 2005, in debug mode there are no problem.
When installing on a win xp with SQL Express 2005, I have the following error:

<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\Documents and Settings\JOZ\Local Settings\Temp\tmp19.tmp'. Operating system error 5(Access denied).<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.<br />

I like sqldmo backup because is really quick... but this issue is driving me crazy... Can somebody help me please? $Shucks | :-\$

Life is not short... the problem is only how you organize yourself

Re: sqldmo backup issue (Cannot open backup device)

SimulationofSai16-Apr-08 1:24

SimulationofSai

16-Apr-08 1:24

Maybe your SQL Server 2005 service is running under an account which has permissions to the backup folder, which the account used by SQL Express does not.
Try giving write permissions to the account under which SQL Express runs.

SG
Cause is effect concealed. Effect is cause revealed.

Connection of MS-Access 2007 to VC++2005

Le@rner15-Apr-08 19:56

Le@rner

15-Apr-08 19:56

Hi all,

I m new in VC++, i m working on MFC-SDI document type application,
in my application i want to use MS-Acess 2007 Database,
but i dont have any knowledge about this.
please anyone can tell me at starting how an i use Access databse in my application.

thanks in advance.

Re: Connection of MS-Access 2007 to VC++2005

ChandraRam16-Apr-08 1:12