Database Discussion Boards

Re: sql String seach based on other Column

kubben18-Apr-07 3:14

18-Apr-07 3:14

I guess I don't understand then. All three of those things are in the where clause. First you limit the result set by Extension and Num_key, then you limit it by display between 00274 and 01189. I am pretty sure the query I gave does what you are asking. It is doesn't please try to explain what you want again.

Ben

Re: sql String seach based on other Column

Vimalsoft(Pty) Ltd18-Apr-07 3:24

Vimalsoft(Pty) Ltd

18-Apr-07 3:24

Thanks Ben for your reply and patience. the code is correct it does not return an error but it return an Empty table.

i tested

select * from property
where Extension = 2 And Num_Key = '0196' separately and it returned 1239 Rows. but when i combine it with the code you gave me

select * from property
where Extension = 2 And Num_Key = '0196' and
Cast(Substring(Attrib_code,5,5) as bigint) between 00274 And 01189

it shows an Empty table by only displaying the Column names only

Please help man

thanks

Vuyiswa

Re: sql String seach based on other Column

kubben18-Apr-07 3:33

kubben

18-Apr-07 3:33

Well, then my guess would be in those 1239 rows there aren't any that have a value between 274 and 1189.

Try this
select * from property
where Extension = 2 And Num_Key = '0196'
order by attrib_code

That way you should be able to see if you have any between 274 and 1189

Hope that helps.
Ben

Re: sql String seach based on other Column

Vimalsoft(Pty) Ltd18-Apr-07 3:36

Vimalsoft(Pty) Ltd

18-Apr-07 3:36

Thanks man when i run it it gives me 1329 records.

Vuyiswa

Re: sql String seach based on other Column

Vimalsoft(Pty) Ltd18-Apr-07 3:41

Vimalsoft(Pty) Ltd

18-Apr-07 3:41

i mean the code you just gave me

select * from property
where Extension = 2 And Num_Key = '0196'
order by attrib_code

Vuyiswa

Re: sql String seach based on other Column

kubben18-Apr-07 3:56

kubben

18-Apr-07 3:56

So the question is in those rows that are returned look at the attrib_code and tell me if you see any in the range you are looking for. I am guessing that you will not find any in the range you said you wanted.

Ben

Re: sql String seach based on other Column

Vimalsoft(Pty) Ltd18-Apr-07 4:19

Vimalsoft(Pty) Ltd

18-Apr-07 4:19

Thanks man for your reply,i have used the wrong column, i should have used Lis_key, and i used Attrib_key, so when i enter that range its good its showing results. thanks man you are a star . my mistake for the wrong column. i saw your profile, i just uploaded my photo. am 25 turning 26 in September this year thanks man.

kind regards

Vuyiswa

Vuyiswa

Re: sql String seach based on other Column

Vimalsoft(Pty) Ltd18-Apr-07 3:54

Vimalsoft(Pty) Ltd

18-Apr-07 3:54

i mean the code you just gave me

select * from property
where Extension = 2 And Num_Key = '0196'
order by attrib_code

Vuyiswa

Vuyiswa

Re: sql String seach based on other Column(Thanks i Got it)

Vimalsoft(Pty) Ltd18-Apr-07 4:04

Vimalsoft(Pty) Ltd

18-Apr-07 4:04

Thank you man , my mistake it wasnt Attribute code column it was supposed to be Lis_Key. remember yesterday we did the Attribute code, thanks a lot man for you time, i will rate you as my mentor

kind regards

Vuyiswa

Vuyiswa

saving and then undoing the changes of an insert statment

steve_rm18-Apr-07 1:41

steve_rm

18-Apr-07 1:41

Hello

VS 2005

I am using typed dataset in my vb project.

In my window the user will insert a new customer

TA_Customer.insert(,,,,,,,)

but if they change their mind and decide they don't want to insert a new one. Is it possible to roll back the changes.

The customer wants be able to add a new customer, then if they change their mind, click close on the window and not commit the changes.

Many thanks for any ideas or code,

Steve

Re: saving and then undoing the changes of an insert statment

kubben18-Apr-07 3:03

kubben

18-Apr-07 3:03

I think the best you can do is to allow them to delete a new customer. I don't think you want to hold open any transactions on a customer table.

Ben

Re: saving and then undoing the changes of an insert statment

steve_rm18-Apr-07 3:59

steve_rm

18-Apr-07 3:59

Hello,

Thanks for your reply.

I was thinking of holding the data in the memory of the typed dataset then clearing this if they decided to close the form.

Any ideas about the above.

Steve

Re: saving and then undoing the changes of an insert statment

kubben18-Apr-07 5:05

kubben

18-Apr-07 5:05

In the dataset there is something called AcceptChanges(). You have to call this for any changed to be accepted. The problem is without doing this I am not sure that the data will show up anywhere. I guess, if you are just planning on having data in memory you can do that, but things can get pretty bad if you start assigning id's. You can start to run into situations where two people have the app open and both create a new customer, both get assigned the same id. Now you have a problem.

Ben

INNER JOIN on NUll Data problem

Sam Heller18-Apr-07 0:20

Sam Heller

18-Apr-07 0:20

<br />
SELECT  [DrawID],<br />
    COALESCE(CA.[ForeName], 'Not drawn') As Winner,<br />
FROM Draws D<br />
INNER JOIN CompetitionA CA<br />
ON D.WinnerA = CA.CompAID<br />
WHERE CompID = @CompID<br />
ORDER BY DrawnOn ASC;<br />

I am using the following code as above. It does work but not properly. There are three rows that I would like to return from the database but I only get one. I know the reason for this is because only one of them has been given a 'D.WinnerA' value. The other rows will get this value in time but at the moment they do not have it. Therefore the line:

<br />
INNER JOIN CompetitionA CA<br />
	ON D.WinnerA = CA.CompAID<br />

is ruling two of the three required rows out. What is the best approach to this problem?

Thanks in advance

Re: INNER JOIN on NUll Data problem

Pete O'Hanlon18-Apr-07 0:50

Pete O'Hanlon

18-Apr-07 0:50

Change the INNER JOIN to a LEFT JOIN.

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

Re: INNER JOIN on NUll Data problem

Sam Heller18-Apr-07 1:25

Sam Heller

18-Apr-07 1:25

Great thanks for that. Iv'e neaver really fully understood the other JOIN methods. What is the exact reason for using LEFT JOIN? Strictly for this reason?

Thanks

Re: INNER JOIN on NUll Data problem

Pete O'Hanlon18-Apr-07 1:53

Pete O'Hanlon

18-Apr-07 1:53

It is. Basically they work like this (assuming that you have two tables A and B):

SELECT * FROM A
INNER JOIN B
ON A.ID = B.FKID

This returns records where there is a match in both A and B.

SELECT * FROM A
LEFT JOIN B
ON A.ID = B.FKID

This returns ALL records from A regardless of whether or not there is a matching record in B. Similarly, RIGHT JOIN returns all records from B regardless as to whether or not there is a matching entry in A.

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

Re: INNER JOIN on NUll Data problem

Sam Heller18-Apr-07 2:03

Sam Heller

18-Apr-07 2:03

Oh i see. That makes alot of sense i guess. Thanks for that

Re: INNER JOIN on NUll Data problem

Colin Angus Mackay18-Apr-07 2:51

Colin Angus Mackay

18-Apr-07 2:51

Sam Heller wrote:
Iv'e neaver really fully understood the other JOIN methods.

This summary of the join types[^] might help.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: INNER JOIN on NUll Data problem

Pete O'Hanlon18-Apr-07 4:03

Pete O'Hanlon

18-Apr-07 4:03

I should have realised that you would have a summary somewhere. Nice one. Big Grin | :-D

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

Sql Search, can i use = in the Substring

Vimalsoft(Pty) Ltd17-Apr-07 22:50

Vimalsoft(Pty) Ltd

17-Apr-07 22:50

Am now Searching certain range in a character string Attrib_Code,
which has more Characters Example 019600197/1/R.PV000000. i have more than 50 thousand recards with the same First Four numbers. they differ from "00197", the other parts are not important for me. i want to search a range from "000197" till "000274", how do i search the rages in strng? inside substring.? Confused | :confused:

Vuyiswa

Re: Sql Search, can i use = in the Substring

Krish - KP17-Apr-07 23:21

Krish - KP

17-Apr-07 23:21

try this

SELECT * FROM tblA
WHERE cast(substring(Attrib_Code, 5, 5) as bigint) between 197 and 274

Regards
KP

Re: Sql Search, can i use = in the Substring

Vimalsoft(Pty) Ltd18-Apr-07 2:29

Vimalsoft(Pty) Ltd

18-Apr-07 2:29

select * from property
where Cast(Substring(Attrib_code,5,5) as bigint)
between 00274 And 01189 Man the Code works thanks man, i forgort to ask you something again, the is a Column , i need to Extract those who are in Extension 6, i tried extent the query but i get a lot of errors, your experience will help.

Thanks man

Vuyiswa

Re: Sql Search, can i use = in the Substring

Krish - KP18-Apr-07 18:03

Krish - KP

18-Apr-07 18:03

not getting.
Can you please explain in detail.

Regards
KP

SUGGEST ON STORED PROC

adnanrafiq17-Apr-07 21:13

adnanrafiq

17-Apr-07 21:13

Hi All,

I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this
CREATE PROCEDURE dbo.GetAll

(
@SQLQuery nvarchar(max)
)

AS
SET NOCOUNT ON

EXEC sp_executesql @SQLQuery

THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know.
Many thanks,
adnan

Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com

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