SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Answering a SQL Query Question from a Recruiter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/25/2001 :  14:58:26  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Earlier we published a question about a SQL query that a recruiter asked a job candidate. I presented a couple of different options but didn't really think it could be done . . . until Alexander Netrebchenko sent me the solution. Here's his email and the script . . . and a job if I had one to give him :)

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 04/05/2001 :  21:28:16  Show Profile  Reply with Quote
Solution

Bravo !! Well done!!

Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 05/10/2001 :  01:03:06  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
date clusters in 1 query.

Group it by DateDiff .

Go to Top of Page

Robin
Starting Member

Canada
9 Posts

Posted - 10/24/2001 :  15:30:43  Show Profile  Visit Robin's Homepage  Reply with Quote
This does not function at all as far as I can tell, perhaps you can help? When I swap out Tables with select statements as per the example, and run the resulting stored proc through my syntax checker then I am unable to access any regular tables in the stored proc. I get an error message that the specified table or view does not exist.

I am including the 'FROM' claus here so you can see what I'm doing, but, as far as I can tell, this doesn't work.

FROM VIPClients.dbo.Accounts_Master (NOLOCK),
(Select * From Billing_Exceptions (NOLOCK) Where Billing_Exceptions.Property_ID Like @PropID And Billing_Exceptions.GDS_Code Like LTrim(Billing_Table.GDS_Code)) As GDSExcept,
(Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code) As IntExcept,
(Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Rate Like LTrim(Billing_Table.Rate_Code)) AS RateExcept

INNER JOIN VIPLook.dbo.Country_Continent_Lookup (NOLOCK) On VIPClients.dbo.Accounts_Master.Property_Country = VIPLook.dbo.Country_Continent_Lookup.Country_ID
INNER JOIN VIPLook.dbo.State_Province_Table (NOLOCK) On VIPClients.dbo.Accounts_Master.Property_State = VIPLook.dbo.State_Province_Table.State_ID
INNER JOIN VIPClients.dbo.Basic_Invoice_Data (NOLOCK) ON VIPClients.dbo.Accounts_Master.Property_ID = VIPClients.dbo.Basic_Invoice_Data.Property_ID
INNER JOIN VIPLook.dbo.Exchange_Rate_Lookup (NOLOCK) On VIPClients.dbo.Basic_Invoice_Data.Rates_Currency = VIPLook.dbo.Exchange_Rate_Lookup.Currency_ID
INNER JOIN Billing_Table (NOLOCK) On VIPClients.dbo.Accounts_Master.Property_ID = Billing_Table.Property_ID
INNER JOIN VIPMain.dbo.Property_Master (NOLOCK) On VIPClients.dbo.Accounts_Master.Property_ID = VIPMain.dbo.Property_Master.Property_ID

LEFT JOIN VIPClients.dbo.Billing_Exceptions (NOLOCK) ON VIPClients.dbo.Accounts_Master.Property_ID = VIPClients.dbo.Billing_Exceptions.Property_ID

Robin Olsen
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 10/25/2001 :  00:19:49  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
I noticed that you are qualifying most of your base tables with 3-part names (database.owner.table), however, inside your SELECTs you don't... And it does seem like you are dealing with multiple DBs there. If you are trying to compile this proc in any database, other than VIPClients, SQL Server is not going to know where to look for Billing_Exceptions and Billing_Table tables.
If that's the case, try using the fully qualified name (VIPClients.dbo.Billing_Exceptions) in your SELECTs, and the error should go away.

Oh, and one other thing (nothing to do with your syntax error)... Is there a reason why you use LIKE instead of = in the WHERE clauses of your SELECTS... I don't think you are doing any pattern matching, so = will achieve the same result, but will be more efficient.

Edited by - izaltsman on 10/25/2001 00:36:31
Go to Top of Page

Robin
Starting Member

Canada
9 Posts

Posted - 10/25/2001 :  17:35:20  Show Profile  Visit Robin's Homepage  Reply with Quote
Thanks, I'll imp[lement your suggestions and let you know

Robin Olsen
Go to Top of Page

Robin
Starting Member

Canada
9 Posts

Posted - 10/25/2001 :  17:42:03  Show Profile  Visit Robin's Homepage  Reply with Quote
Hello izaltsman

I've tried the suggestions as far as what to do about using the select statement to substitute a table name, no go.

Everytime I try to implement, I lose access to VIPClients.dbo.Accounts_Master and Billing_Table. I have 'VIPClients.dbo.' inside my select statement and it is still no good. This has me kinda baffled because it obviously works for others judging by some of the replies here. I can't seem to make it happen.

Weird, I'm not trying to be difficult, I wish I could get this to work as this solution would solve many issues I have. Too bad I can't get it to work. Can anyone help out on this?

Robin Olsen
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 10/26/2001 :  01:47:36  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
That has to work! You must have overlooked some minor detail somewhere.
Try to build your FROM clause from the ground up... In other words, take the SELECT statement for your first derived table, paste it into a fresh query analyzer window and see if it runs. Debug it until it does. Then put parentheses around it, indicate an alias, and try to SELECT * FROM it... Then take the SELECT statement for the second derived table, and if it runs by itself, try to join it with the first etc...
Building your query bit by bit like that should help you pinpoint the error.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000