A simple Query Problem!!!!!

[sohaib]

Hi Guyz!!

I have a very simple problem......In one of my table i have more than 3 fields but in those 3 particular fields like (HomeAddress,EmailAddress,FaxNo)

I just want to make a query which i m not able to do uptil now...anywayz i m trying my level best but i put a question in front of you people if any one had done a simple solution for that problem which i have written below....

The query should be in such a manner that if any of the client has given any type of address it should be included at only one field or we can named as "Contact" not all lists should be included..

I mean to say that :-

Name Location Contact

John London 044-9259400(Fax No) (suppose)
Smith Sydney smithonly4u@yahoo.com (suppose)
Michael New York Street # 23, Shopping Avenue, New York(Suppose)


So i have written a few examples so that it can work....if all of the three fields have been given so i hope so query should be in that manner it will first prefer (Email Address then Fax No and then Home Address)

I think so i m very much clear in my point of view...
so is there any guy who can solve my query with your efficient query......

ok

Have a nice day

Bye

 

[TechGnome]

Access? SQL Server? Oracle? What DBMS are you using? That will go a long way to help solving the problem.

Tg

 

[Gambit_NI]

how about something like this then?? - this is for SQL Server

DECLARE @ID int
 
IF @EmailAddress IS NOT NULL
	 BEGIN 
	 SELECT @ID = 0
	 END
ELSE
	 BEGIN
			if @fax IS NOT NULL
				 BEGIN
				 SELECT @ID = 1
				 END 
			ELSE
				 BEGIN
				 IF @HomeAddress IS NOT NULL
					 BEGIN
					 SELECT @ID = 2
					 END
				 END
	 END
 
 
INSERT INTO tbl_Contact([Name],Location,Contact)
VALUES (@Name, @Location, 
CASE @ID 
WHEN 0 THEN @EmailAddress
WHEN 1 THEN @fax
WHEN 2 THEN @HomeAddress
ELSE '' END)

 

[TechGnome]

Where are your variables defined.... and where are they comming from?

Tg

 

[sohaib]

Hi Guyz!!

Thnx for your reply!!!....Actually i m using SQL Server.....

Whatever the code given by Gambit_NI i think so it is used for only inserting into the new table...

In my scenario, I want a report on the basis of predefined table...when it call on the report it should be work in that scenario which you people know very well.....

I think so it will be easily if i will use "View of a table" for this purpose...Call out only particular fields.....

I hope for the better solution....wait for good response and suggestion....

I hope Tg you already understood that my all variables are actually coming from the database not elsewhere....

If there is still ambiguity then tell me i will try to tell you clearly...

Ok Bye

 

[TechGnome]

OK, I think I understand now....

Would this work for you?

INSERT INTO tbl_Contact([Name],Location,Contact)
SELECT
  [Name],
  Location,
  ISNULL(EMailAddress, ISNULL(Fax, ISNULL(HomeAddress, 'NONE')))
FROM YourTable_HERE

I'm assuming there's more to the code than what you have posted, and it probably includes a cursor of some type that is extracting the data into the variables. This is very inefficient, and it's better to do it all in a select if at all possible.

Tg

 

[sohaib]

Hi Tg!!

It's good to hear from you...

I have applied your scenario in my program but it is not working accordingly...

I got few email addressess and NONE keyword in the Contact (Name of the field) Column...

Actually, I want the result in such scenario if a person doesn't have an email address then his Fax# should be displayed and even if both of them are not present then HomeAddress will displayed in the same column named as "Contact"....and if none of them are present then only 'NONE' Keyword is appropriate to show in the end.....in the same column....

HOpe for the positive response from you soon...

Ok Bye.

 

[TechGnome]

Right.... that's what the ISNULL(EMailAddress, ISNULL(Fax, ISNULL(HomeAddress, 'NONE'))) is all about....

If EMailAddress is null then it will go to Fax... and if that is null it will go to HomeAddress and if that is null too, then it will display "NONE"

Now, if you are getting blanks where you expect something, then the field (one of them) may actualy be blank and not just simply NULL.

If that's the case... then we may need to try something a little more creative.... gimmie a sec..

Tg

 

[TechGnome]

OK, This one does a better check for blank (non-NULL) fields:

INSERT INTO tbl_Contact([Name],Location,Contact)
SELECT
[Name],
Location,
CASE
	WHEN LTRIM(RTRIM(ISNULL(EMailAddress, ''))) <> '' THEN EMailAddress
	WHEN LTRIM(RTRIM(ISNULL(Fax, ''))) <> '' THEN Fax
	WHEN LTRIM(RTRIM(ISNULL(HomeAddress, ''))) <> '' THEN HomeAddress
	ELSE 'NONE'
END
FROM YourTable_HERE

I added LTRIM, RTRIM to account for spaces in the fields

Let me know if you need help in deciphering what I've done

Tg

 

[sohaib]

Hi Tg!!

Tht's was really good now i m getting my desired result.......Actually the problem in my previous attempts which i understood was related to "Null" field i think so if any "Null" was present in the Fax# field then it will directly move towards the "NONE" field....


Thanx a lot....

I hope for better consultation with you in future!!!

ok

Have a nice day

BYe