Database Discussion Boards

irshu1-Jul-04 1:05

1-Jul-04 1:05

Any master in SQL Queries

Help: Complicated query

I have a table, as similar to this

ID Status Deleted

1 Repeat y
2 Repeat y
3 Repeat N
4 Repeat N
5 sUBMIT y
6 sUBMIT y
7 sUBMIT N
8 sUBMIT N
9 Pending Y
.
.
.
.

from the table like this i have to retrive distinct records where

delted = N

eg: in the above table after executing query result should be

ID Status Deleted

3 Repeat N
7 SUBMIT N
9 Pending Y

Re: Master in SQL please Help

Xiangyang Liu 刘向阳1-Jul-04 1:12

Xiangyang Liu 刘向阳

1-Jul-04 1:12

irshu wrote:
from the table like this i have to retrive distinct records where

delted = N

eg: in the above table after executing query result should be

ID Status Deleted

3 Repeat N
7 SUBMIT N
9 Pending Y

The query does not seem to be complicated, but it is not clear what you want. You said "deleted = N" and your result set has a record with "deleted = Y". Please explain.

My articles and software tools

Re: Master in SQL please Help

irshu1-Jul-04 18:53

irshu

1-Jul-04 18:53

say if i have records with same status field and with deleted ='N' and delted = 'Y' then i should select one record with deleted = 'N' and i should not select deleted = 'Y'
and say if i have records with the same status and with deleted ='y' and No records with delted='N' with the same status then i should select delted ='Y'

and one more thing in this is, ID is the primary Key

Re: Master in SQL please Help

Xiangyang Liu 刘向阳2-Jul-04 3:59

Xiangyang Liu 刘向阳

2-Jul-04 3:59

Wow, that is complicated, but not impossible. Here is the SQL statement that will get what you want (using union and nested query):

SELECT min(ID), Status, 'N'
FROM myTable
WHERE Status in (select distinct Status from myTable where Deleted = 'N')
GROUP BY Status

UNION

SELECT min(ID), Status, 'Y'
FROM myTable
WHERE Status in (select distinct Status from myTable where not Status in (select distinct Status from myTable where Deleted = 'N'))
GROUP BY Status;

My articles and software tools

Re: Master in SQL please Help

Xiangyang Liu 刘向阳2-Jul-04 4:03

Xiangyang Liu 刘向阳

2-Jul-04 4:03

Oops, the following is simpler:

<br />
SELECT min(ID), Status, 'N'<br />
FROM myTable<br />
WHERE Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status<br />
<br />
UNION <br />
<br />
SELECT min(ID), Status, 'Y'<br />
FROM myTable<br />
WHERE not Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status;<br />

My articles and software tools

Re: Master in SQL please Help

Anonymous4-Jul-04 20:05

Anonymous

4-Jul-04 20:05

Thank you very much for helping in this regard.

with regards
irsh

Re: Master in SQL please Help

irshu4-Jul-04 20:06

irshu

4-Jul-04 20:06

Thank you very much for helping in this regard.

with regards
irsh

Thank you,

irshu4-Jul-04 20:06

irshu

4-Jul-04 20:06

Thank you very much for helping in this regard.

with regards
irsh

MS SQL Server date format

spn30-Jun-04 20:26

spn

30-Jun-04 20:26

Does MS SQL Server support dates in 'mmyy' format?
If so, how??

Re: MS SQL Server date format

michanne4-Jul-04 17:48

michanne

4-Jul-04 17:48

Left(Convert(char(6),getdate(),12),4)

michanne

Re: MS SQL Server date format

spn6-Jul-04 19:51

spn

6-Jul-04 19:51

Thanks...
But i want the data to retain datetime datatype, so that i can apply datetime functions to the data.

ACCESS DB to SQL

youssef30-Jun-04 12:02

youssef

30-Jun-04 12:02

Hi,

I have an ACCESS DB populate by an automation.
I am looking for reading this database and populate it in a sql database.

For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this).

After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB.
And populate new records to the appropriated TABLE in SQL DB.

If you have any suggestions for my question it is great.

youssef

Re: ACCESS DB to SQL

Colin Angus Mackay30-Jun-04 14:04

Colin Angus Mackay

30-Jun-04 14:04

I'm not sure I quite understand what you are trying to achieve. Why are you continuing to have an Access database? Why not migrate it all to SQL Server and leave it in there? If you need to access the data in Access then why not link the tables back from SQL Server?

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

My Blog

Re: ACCESS DB to SQL

youssef30-Jun-04 21:51

youssef

30-Jun-04 21:51

Thanks for your answer.

I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license).

So, I used Linked servers in Entreprise Manager.
But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ?

youssef

Re: ACCESS DB to SQL

RichardGrimmer30-Jun-04 22:40

RichardGrimmer

30-Jun-04 22:40

youssef wrote:
the customer wont migrate to SQL Server (probably for license)

cough, MSDE, cough cough....

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: ACCESS DB to SQL

youssef1-Jul-04 1:31

youssef

1-Jul-04 1:31

So, I find all of my questions.

Now I would like to use a TRIGGER for populate with my Linked server (ACCESS) my Table in SQL.

How can I do or is it possible ?

youssef

TRIGGER with a VIEW from a Linked Servers

youssef3-Jul-04 13:19

youssef

3-Jul-04 13:19

Hi,

I have an ACCESS DB (.mdb) generate by an Automation.
Now, I would like to:

1/ Copy all datas from the ACCESS DB in my SQL Database.
I using DTS. All are working.

But, after copying, I would like at each time a new record is inserted in the ACCESS DB, I copy this Record in my SQL DATABASE (synchronize the SQL DATABASE).
For this, I create LINKED SERVERS for the ACCESS DB (Named LOG).
In my SQL DATABASE, I create a VIEW that read one TABLE from LOG (linked ACCESS DB).
Now, I would like to create a TRIGGER from this VIEW for retrieve each INSERT and copy this insert in my SQL DATABASE.

Please find the CODE below :

CREATE TRIGGER trgLog on [dbo].[VIEW_LOG]
INSTEAD OF INSERT
AS

BEGIN
declare
@Log_Date datetime,
@Log_Input smallint,
@Log_Level smallint,
@Log_Milliseconds smallint,
@Log_Output smallint,
@Log_Panel smallint,
@Log_Response smallint,
@Log_Status smallint

set @Log_Date ='05/07/2004 00:00:00'
set @Log_Input =1
set @Log_Level =1
set @Log_Milliseconds =1
set @Log_Output =1
set @Log_Panel =1
set @Log_Response =1
set @Log_Status =1

exec InsertLog @Log_Date,@Log_Input,
@Log_Level,@Log_Milliseconds,
@Log_Output,@Log_Panel,
@Log_Response,@Log_Status

END

So in this code I Insert a test message after hat a select "insert".
But when I refresh my SQL DATABASE, I see nothing whereas I the ACCESS DATABASE the AUTOMATION insert record.

Can You Help me about that ?

Best Regards

youssef

ODBC apparently has a statement length limit...

CherezZaboro30-Jun-04 9:53

CherezZaboro

30-Jun-04 9:53

How to overcome that? I'm using MFC ODBC classes. I need sometimes to send a prety long statements. Is there a way to lift the limit?
Confused | :confused:

Re: ODBC apparently has a statement length limit...

Steven Campbell30-Jun-04 13:38

Steven Campbell

30-Jun-04 13:38

I don't know, but you should also be aware that the underlying DBMS may also have a statement limit, so even if you do override the ODBC limit, you may still be stuck.

my blog

Porting data from SQL Server to Oracle over ODBC

pankajdaga30-Jun-04 6:55

pankajdaga

30-Jun-04 6:55

Hi everyone,

I have an ODBC question. I have two databases on the network. One is running under SQL Server and the other is running under Oracle. There is a schema on the Oracle database that has the exact schema as the SQL Server database (more or less). What I want to do is copy the contents of each table from SQL Server to Oracle. I know there must be a way to do this with a view or a script. I cannot use any tool like Oracle migration or anything else and it has to go over ODBC.

Can someone suggest a way to do this? Could Access be used for something like this? I am afraid I am extremely inexperienced with database stuff.

Thanks. Any help would be greatly appreciated.

Sincerely,

Pankaj

Without struggle, there is no progress

Re: Porting data from SQL Server to Oracle over ODBC

RichardGrimmer30-Jun-04 22:43

RichardGrimmer

30-Jun-04 22:43

Could you write an app to do it? Could open two connections and zap the data across from one to the other....

Suspect that you won't be able to do it with a view or script, since running on two different RDBMS..

Just out of interest, why can't you use Migration (Or DTS in SQL), and why ODBC Only?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: Porting data from SQL Server to Oracle over ODBC

pankajdaga2-Jul-04 3:40

pankajdaga

2-Jul-04 3:40

Hi Richard,

Thanks for replying. Actually I did ultimately use DTS. The thing was that Oracle has a 30 character limitation on the database identifiers. My source database had big table, view, procedure names etc. Also, there is no auto-numbering in Oracle the way SQL Server has it.

However, I did manage to solve the problem using DTS. I was having a bad day and I was kicking myself for not thinking about DTS. I wrote a script that created the tables on the target database and used ODBC with DTS to copy the data over. The I ran a script which created the sequence and triggers and started auto numbering on the Oracle tables.

Best always,

Pankaj

Without struggle, there is no progress

Re: Porting data from SQL Server to Oracle over ODBC

RichardGrimmer2-Jul-04 4:00

RichardGrimmer

2-Jul-04 4:00

pankajdaga wrote:
Also, there is no auto-numbering in Oracle the way SQL Server has it.

Oracle relies on Sequences for autonumbering - similar, but the Select My_Sequence.nextval from dual had to be used....apologies if you already knew that one lol!

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: Porting data from SQL Server to Oracle over ODBC

pankajdaga2-Jul-04 5:53

pankajdaga

2-Jul-04 5:53

Yeah, I found that out online. But thanks for pointing it out to me. I was never a database fan. After using Oracle tools I came to the realization that the job of a database administrator is one that I will never take Poke tongue | ;-P

Also, all the limitations on the length of tables names and other identifiers is something I found strange. Probably something that has dragged on since the days of the first release. It is about time they removed these non sensical limitations.

Cheers,

Pankaj

Without struggle, there is no progress

insert binded values

usamadba30-Jun-04 2:28

usamadba

30-Jun-04 2:28

hello every body
i have a small question
How can I insert a row using values from binded text boxes without supplying every column value of my DataRow

Best Regards
UsamaDBA

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.