Solved

Query syntax

Posted on 2014-11-17
6
169 Views
Last Modified: 2014-11-18
I have written a table value function defined as:
CREATE FUNCTION [dbo].[ReadControlMeasurement]
(
	-- Add the parameters for the function here
	@pat_id int,
	@StartMonth int,
	@EndMonth int 
)

Open in new window

I want to call it for all the patients in my database that are women.

i can get all the women from the database using

select pat_id from Patients where gender = 'W'

Open in new window

i was thinking along the lines of using a cte:

with Women as
( select distinct pat_id from Patients where Gender = 'W' )

select B.* from Women W inner join dbo.ReadControlMeasurement( W.pat_id, 0, 1 ) B on W.pat_id = B.pat_id

Open in new window

but this does not seem to have a valid syntax.

Any ideas?
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
ID: 40448358
Try using CROSS APPLY instead of INNER JOIN
0
 

Author Comment

by:soozh
ID: 40448374
when i use cross apply the Query seems to take for ever
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 40449150
Can you explain what you want to achieve.
 Also with ReadControlMeasurement function definition
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40449210
Can't really understand the need for a function. And that function as you posted it's malformed.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40449327
It would take for ever. That's because using a function within a query is not very fast.

Try rewriting the function as a view, perhaps, or maybe just a single query,
0
 

Author Closing Comment

by:soozh
ID: 40449759
there was an error in my function but after i fixed that Cross apply worked
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question