Solved

Query syntax

Posted on 2014-11-17
6
170 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 50

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

717 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