Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 16, 2001

Subquery Stumper

By Steve Jones

Introduction

I do not use subqueries that often, but they are definitely a tool in my T-SQL drawer. I understand (I think) how subqueries work and can read them to determine what the purpose of the query is. There are times that a subquery will fit a situation better than any join that I can come up with.

That being said, I am not a guru or an expert on subqueries. There are better resources for very complicated and elegant subquery solutions to problems. However, I came across an issue recently where a subquery was behaving strangely and thought it would make interesting reading once I determined the problem. As an FYI, I did not solve this problem in the first half hour or so when I looked at it. I was busy and had to let it go for a couple days and when I came back to it, another good 30-60 minutes was spent determining the cause of the problem and verifying that I had solved it.

The Problem

A developer recently sent me an email where he was asking what the difference was between the following two queries:

declare @this_id int
set @this_id = 100
select sum(ol.price)
 from OrdLineDtl od, OrdLine ol
 where od.TypID = ( select TypID
					 from Attr
					 where AttrName = 'Item'
					)
 and od.AttrVal = @this_id
 and od.OrdLineID = ol.OrdLineID

go
declare @this_id int
set @this_id = 100
select sum(oi.price)
from OrdLineDtl od, OrderLine ol
where od.TypID = 53
	and od.AttrVal = @this_id
	and od.OrdLineID = ol.OrdLineID
These two queries prooduce the following result from Query Analyzer:
On the messages tab, I get:
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'just for me.' to a column of data type int.

(1 row(s) affected)
While on the Results tab, I get:
Sum
----------
1995.0000

The two statements are valid statements. The schema for the tables involved is as follows:

Create Table Attr
(  TypID  int,
   AttrName  char(50)
)

Create Table OrdLine
(  OrderItemID	int,
   ProductCode varchar( 50),
   Price money
)

Create Table OrderLineDtl
(  OrderItemID	int,
   TypID int,
   AttrVal varchar( 50)
)
The AttrID of 53 does indeed match the AttrName of 'Item'.

Analysis

Spend a few minutes trying to figure this one out. The analysis is on page 2.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date