Please help me with my research problem [modified] - Database Discussion Boards

I guess "MYFLG" is not a column of table1.
what result he wants to get is that rows "

not in  where  COLUMN1 = 1 AND COLUMN2 = 0

"
If so,Why don't use this

Select * from TABLE1 where PK not in ( Select PK From TABLE1 Where COLUMN1 = 1 AND COLUMN2 =0)

modified on Wednesday, October 13, 2010 10:37 PM

I don't think so, if you see the posters' query, it has

WHERE MYFLG =0

Cheers,
Karthik

but he alse got this Error infomation "got an error: Invalid column name 'MYFLG'".

@Karthik. A

thank you for your suggestion. yes. Alegria_Lee is correct MYFLG is not a column it is an alias from my case statement. sorry for my explanation not in details.

anyway. this is the actual query.

SELECT CASE WHEN SMPTABLE.RPFLG = 1 AND SMPTABLE.NDFLG= 0 THEN 
1 ELSE 0 END AS MYFLG
FROM  [TBMASTER] ,SMPTABLE,ANLTABLE,WKTABLE
WHERE (SMPTABLE.CERTCODE BETWEEN 1 AND 5 OR
SMPTABLE.CERTCODE BETWEEN 11 AND 15 or
SMPTABLE.CERTCODE = 17) AND
(SMPTABLE.DDATE IS NOT NULL) AND
(SMPTABLE.CERTNNO = 0 OR
SMPTABLE.CERTNNO IS NULL) AND ((SMPTABLE.DIVCODE = 1) OR
(SMPTABLE.DIVCODE = 5))
AND (ANLTABLE.ROWNO = 1) AND (ANLTABLE.KMKCD <> 1998)
AND [TBMASTER].SAMPLECD = SMPTABLE.SAMPLECD
AND ANLTABLE.SHINO=SMPTABLE.SHINO
AND  SMPTABLE.ORDNO = ANLTABLE.ORDNO
AND WKTABLE.ORDNO =SMPTABLE.ORDNO
AND ((WKTABLE.SECT=31) OR (WKTABLE.SECT =51))
AND ([ANLTABLE].ENVFLG = 0)

the result of MYFLG temporary columns are

MYFLG
0
0
0
0
1
1
0
1
0

; then i want to query again the result for those MYFLG = 0.
that is what my approach is subquery.
SELECT (SELECT).

:: i will try the NOT IN statement given by Alegria_Lee.

C# コードMicrosoft End User
2000-2008

「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Here is it

WITH myTable as 
(
--Put here all your  query
)
 select * from myTable where MYFLAG=0

For more about

WITH

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

this is what i'm looking for.
thank you so much. i tried already and it's working. great job! Thumbs Up | :thumbsup:

C# コードMicrosoft End User
2000-2008

「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」

You are welcome. Smile | :)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Hello guys.

First up, please know that it has been almost a decade since I have been anywhere close to a database, be it at work or outside. Anyway I never did anything substantial there except have a table and read/write data. So go easy on flaming if this turned out to be a n00b question or suchlike.

I have to model/analyze some data for some research and I need your help to see how I can go about that.

First some definitions

1. There is a set called S subscripted with n that I write as Sn where 1 <= n <= 100
2. Every Sn (i.e S for all n) is a subset of a larger set called US
3. None of the Sn is intersecting
4. It should be possible to name every Sn

Now the problem

1. I have An and Bn which have one to one mapping
2. I have Cn that has many to one mapping with Bn (each element of Bn maps to one or more elements of Cn)
3. There are also other sets like Dn, En, Fn etc that have one to one mapping with Bn only and are unrelated to any other set directly

I want to be able to do these

1. Given any element from UA, the relevant An (the set) must be got. If An exists, then the corresponding Bn element must be got
2. Similarly, given any element from UB, the relevant Bn must be got. If Bn exists, either from this step or step (1), all the corresponding Cn must be got along with the corresponding ones from Dn, En, Fn etc (the sets that must be considered will be told )
3. Alternatively, any Sn must be extracted by name and all the relations for all its elements must got (i.e if An then Bn as per (2), if others then Bn only)

[EDIT] Some stats - Each set will have at most 500 elements and the super set will have at most 2000. And the set count may be at most 100*50=5000 in all.

I will be having an app for the front end that will do the creating, editing and querying of the database. I will be using MS Access for the backend as it is kinda “free” and already there. I will refresh on my SQL commands as I know that it will be necessary for interacting programmatically.

So can you guys please help me? I need broad ideas like designing it etc more than the "code" itself. I wouldn’t mind if you suggest any other “free” databases that can make my solution simpler. I suppose using ODBC or DAO (that’s what I last remember) is going to act as a black box and I can use any dbase.

And oh, I don’t know .NET. Will it be a chore then?

Feel free to ask me back anything about my problem that you couldn’t understand.

Thanks for your time.

...byte till it megahertz...

my donation to web rubbish

modified on Wednesday, October 13, 2010 5:08 PM

It sounds like your dataset is not that large, so storing the data in Access sounds fine.

You will be amazed at how easy it is to develop queries in Access to get the results you need, if you find the data to be too large or you need to share this database with others, you may have to upgrade to something more substantial, ie MS-SQL.

Start with Access, it should give you enough to prototype with ... maybe even solve your problem completely.

Good luck.
Thumbs Up | :thumbsup:

Thanks for your response.

I think each set will have at most 500 elements and the super set will have at most 2000. And the set count may be about 100 in all. So I might be able to use Access.

Could you please give some pointers on the design? Some brief thing about tables etc will surely help me.

...byte till it megahertz...

my donation to web rubbish

1. if setting up servers for transactional replication, is it possible for the subscriber server to delay the publisher server?

For example, the publisher server is under heavy load and inserting/deleting a large amount of rows; I realize that the publishing server will be a little slower due to sending these updates to the subscriber server(s), but does the publishing server have to wait or stop processing while the subscribing servers are committing the same transactions?

Or are the sent transactions to the subscriber queued and processed at its own pace - not impacting/stopping the publishing server? I understand that a distributor server cab queue these commands to the subscribers, but does the action of queuing ever pause the production/publisher server in any way?

2. When doing queries on a subscriber database (since it's read only), does the database become unavailable while committing transactions sent from the distributor or publishing server(s)?

Thanks for any help. Just looking to gain some insight on replication.

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

1. Technically No. Although if the distributor is in the same server, there'll be the inevitable resource waits. The publisher DB transactions themselves are not delayed per se, but the underlying resource contention can delay the transaction as a whole. Queuing is managed by the distributor. The publisher actually does nothing. The distributor will monitor the DB log of the publisher and replicate those changes to the subscribers. If the distributor is located in a separate physical server, there wouldn't be any waits.

2. Nope

SG
Aham Brahmasmi!

I have made one class library in that lib I have a function where I am changing the parameters for the dataset query.

I'm using Oracle SQL Developer for all of my Oracle database development currently. Do you recommend any other tools out there? Prefer free, but willing to pay for a great dev tool.

Thanks!

Check these out ...

http://www.toadworld.com/Freeware/ToadforOracleFreeware/tabid/558/Default.aspx[^]

http://squirrel-sql.sourceforge.net/[^]

Maybe I have been at it too long today, but am I missing something simple here?

DECLARE @SomeLongName VarChar(100) = 'Some long text'
DECLARE @temp TABLE(
    someCode VarChar(10)
    )

DECLARE @Sql VarChar(1000) =
        'INSERT @temp SELECT MAX(SomeCode) ' +
        'FROM MyTable ' +
        'WHERE [Name] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''

EXEC (@Sql)

I get 'must declare the table variable @temp' when I try to run this?

Please put me out of my misery... $Shucks | :-\$

(edit: Small typo in Sql)

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

modified on Wednesday, October 13, 2010 3:52 AM

This way should works:

DECLARE @SomeLongName VARCHAR(100) 
SET @SomeLongName = 'Some long text'        
CREATE TABLE #temp
(
	someCode VARCHAR(500)
)                
DECLARE @Sql VARCHAR(1000) 
SET @Sql = 'INSERT into #temp SELECT MAX(SomeCode) FROM MyTable WHERE [Name] LIKE ' +
    
    CHAR(39) + LEFT(REPLACE(@SomeLongName, ' ', ''), 3) + ' %' + CHAR(39) + 
    ' select * from #temp drop table #temp'

EXEC (@Sql)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

My sinvere thanks. Thumbs Up | :thumbsup:

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

You are welcome Smile | :)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Now, what if i want to do this in a UDF? (Can't use temporary tables in a UDF Frown | :(

)

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

I think you'll find you can't use dynamic sql in a UDF so the temp table is moot.

As the boy was useful it is polite to up vote his answer [edit] I must have missed the vote [\edit].

Never underestimate the power of human stupidity
RAH

Because you are using the execute the temporary table needs to be in the database tempdb.

I have even had to use CREATE TABLE ##Temp... to make an execute work.

This works fine in a SSMS query - it wont work in a UDF because of the dynamic sql.

I think I may have to look at using a trigger or something...as I want to use the result to create a field when a new row is added to a table.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

This one does execute.

DECLARE @SomeLongName VarChar(100) = 'Some long text'    
               
    DECLARE @Sql VarChar(1000) 
    SET @Sql='DECLARE @temp TABLE( someCode VarChar(10) )' 
	SET @Sql=@Sql+
            'INSERT @temp SELECT MAX(SomeCode) ' +
            'FROM MyTable ' +
            'WHERE [Name] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''
               
    EXEC (@Sql)

The problem here is the scope in which the sql statement executes. The @temp table variable declared is different from the @temp variable used in the inline query as the scope of both the queries are different. To make the query work we need to run the query in same scope ie declare the table variable using the same SQL satements as one used for the query.

When you fail to plan, you are planning to fail.

SELECT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6582 row(s) affected

SELECT DISTINCT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6473 rows(s) affected

How can I get just the 109 rows that are duplicates? ie: the opposite of DISTINCT?

Thanks

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!