sql dwh problem: queries

Showing posts with label queries. Show all posts

Monday, March 26, 2012

Dynamic query

Hi,
In my stored procedure I have differnet paramters and based on the paramters
different queries needs to be executed against the database.as these queries
share some parts ,I decided to use a dynamic query,so I declared a variable
(DECLARE @.DynamicQuery Varchar(5000)) and based on parameters I append
differnt strings to it (@.DynamicQuery=@.DynamicQuery+ 'string') .The problem
is that this string limits to 750 characters only and it can not hold my
whole query which is more than 750 characters.Why is that so?
Thanks
Here is my sp:
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
WITH RECOMPILE --Throw out the query plan on every execution of this
storedprocedure due to its dynamic nature
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SET @.DynamicQuery = 'SELECT T1.FILEID AS FileID,T1.CURRENT_CGA_REGION_CODE
AS Affiliate_Code,MIN(T1.RECORD_TIMESTAMP) AS
File_ProcessDate,COUNT(T1.FILEID) AS Total_Records,COUNT(CASE
T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID
END) AS NOfRows_with_Biz_Critical_Error,COUNT(CA
SE T1.ERROR_STATUS WHEN 2
THEN T1.FILEID END) AS NOfRows_with_Warning,COUNT(CASE
T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID
END) AS Updated_Different_20,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN
T1.FILEID END) AS Updated_Different_21,COUNT(CASE T1.BIZPROCESSING_STATUS
WHEN 30 THEN T1.FILEID END) AS Matched_NoAction,'
SELECT LEN(@.DynamicQuery )
IF @.ENROLLMENT_TYPR =1 --CURRENT/FUTURE ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,1)
AS Unknown_Active_Members,'
END
ELSE --HISTORY ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,2)
AS Unknown_Active_Members,'
END
SET @.DynamicQuery = @.DynamicQuery +
'T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS
File_Type
FROM IF_C1TRANSFORM T1 LEFT OUTER JOIN IF_FILE T2 ON
T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3 ON
T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID'
IF @.AFFILIATEID <> 1
BEGIN
SET @.DynamicQuery=@.DynamicQuery +
'WHERE T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID'
END
SET @.DynamicQuery=@.DynamicQuery +
'GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME'
SELECT @.DynamicQuery
GO"Ray" <RayAll@.microsft.com> wrote in message
news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In my stored procedure I have differnet paramters and based on the
> paramters
> different queries needs to be executed against the database.as these
> queries
> share some parts ,I decided to use a dynamic query
You should not use a dyniamic query for the purposes of code reuse. Use
dynamic SQL reluctantly and only for good reasons.
Here, this won't hurt a bit...
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SELECT
T1.FILEID AS FileID,
T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
COUNT(T1.FILEID) AS Total_Records,
COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
NOfRows_with_Biz_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
NOfRows_with_Warning,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
Updated_Different_20,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
Updated_Different_21,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
Matched_NoAction,
dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cur
rentAcademicYear,@.CurrentSeesion,case
@.ENROLLMENT_TYPR when 1 then 1 else 2 end)
AS Unknown_Active_Members,
T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
FROM IF_C1TRANSFORM T1
LEFT OUTER JOIN IF_FILE T2
ON T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3
ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
--David|||I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
want to have a where clause ,how dose it work for this purpose?
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u9krc39VFHA.1152@.tk2msftngp13.phx.gbl...
> "Ray" <RayAll@.microsft.com> wrote in message
> news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> You should not use a dyniamic query for the purposes of code reuse. Use
> dynamic SQL reluctantly and only for good reasons.
> Here, this won't hurt a bit...
> CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
> @.AFFILIATEID NUMERIC (8) = 1,
> @.ENROLLMENT_TYPR INT=1 -- 1
> AS
> SET NOCOUNT ON
>
> DECLARE @.CurrentAcademicYear NUMERIC(5)
> DECLARE @.CurrentSeesion NUMERIC(5)
> DECLARE @.DynamicQuery VARCHAR(5000)
>
> SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
> dbo.CURRENT_SESSION
> SELECT
> T1.FILEID AS FileID,
> T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
> MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
> COUNT(T1.FILEID) AS Total_Records,
> COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
> NOfRows_with_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
> NOfRows_with_Biz_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
> NOfRows_with_Warning,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
> Inserted_NewMember,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
> Updated_Different_20,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
> Updated_Different_21,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
> Matched_NoAction,
> dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.C
urrentAcademicYear,@.CurrentSeesion,case
> @.ENROLLMENT_TYPR when 1 then 1 else 2 end)
> AS Unknown_Active_Members,
> T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
> FROM IF_C1TRANSFORM T1
> LEFT OUTER JOIN IF_FILE T2
> ON T1.FILEID=T2.[FILE_ID]
> LEFT OUTER JOIN IF_CONTENT_TYPE T3
> ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
> WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
> GROUP BY
> T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
>
> --David
>
>|||"Ray" <RayAll@.microsft.com> wrote in message
news:%23xjDS%239VFHA.584@.TK2MSFTNGP15.phx.gbl...
>I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
>want to have a where clause ,how dose it work for this purpose?
>
If it adversely affects the execution plan of your query to have the where
clause there, you have a couple of options without resorting to dynamic SQL.
For a query with ony two real variants, I would just code each one as a
static query. That way each one has an appropriate cached plan, and the
whole procedure is much more readable than with dynamic SQL.
David|||>> In my stored procedure I have different paramters and based on the
paramters
different queries needs to be executed against the database.as these
queries share some parts ,I decided to use a dynamic query, <<
Please stop programming and read any basic Software Engineering book.
Look up coupling and cohesion. This is far more fundamental than just
SQL programming. You do not know how to program.
Dynamic SQL is slow, dangerous and an admission that your design is so
weak that a random future user should have more control over it than
you did at design time. I like to name these errors the "Get
BritneySpearsOrSquid" procedures.
Put the core query into a VIEW.|||I so appreciate your help and **thanks for the nice wording** (Excellent way
of helping the others;-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116018663.230642.241190@.g14g2000cwa.googlegroups.com...
> paramters
> different queries needs to be executed against the database.as these
> queries share some parts ,I decided to use a dynamic query, <<
> Please stop programming and read any basic Software Engineering book.
> Look up coupling and cohesion. This is far more fundamental than just
> SQL programming. You do not know how to program.
> Dynamic SQL is slow, dangerous and an admission that your design is so
> weak that a random future user should have more control over it than
> you did at design time. I like to name these errors the "Get
> BritneySpearsOrSquid" procedures.
> Put the core query into a VIEW.
>|||On Fri, 13 May 2005 09:25:55 -0700, Ray wrote:
(an exact copy of a message he also sent 15 minutes earlier)
Hi Ray,
I already replied to your previous message. Please don't multi-post!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dynamic Query

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vananHi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegroups.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vanan
Hi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegrou ps.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>
sql

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.

kalai vanankalaivanan wrote:

Quote:

Originally Posted by

how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.

The issue is caching query plans.

Quote:

Originally Posted by

does dynamic query have advantage over ad hoc queries and in what way.

They're more powerful - if you need it, and if you can afford the
disadvantages.

See http://www.sommarskog.se/dynamic_sql.html for more info.|||kalaivanan wrote:

Quote:

Originally Posted by

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.
>
kalai vanan

If you pass object name as parameters, you have no choice other than
using dynamic sql
Make sure you read the suggested article fully

Madhivanan

dynamic query

Dynamic Queries Errors in Report Manager

I am trying to replicate the use of Dynamic Queries in SQL Server 2000
Reporting Services documented in the "Hitchhiker's Guide to SS 2000 RS."
I have done everything the book has told me to do. I can build and run the
project in test mode, and deploy to my test server
(http:\\localhost\ReportServer) without error.
I have already set up the datasouce for the application in the Report Server
when I try to run the deployed project. Every time I run the project I get
the following:
1) An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
2) Query execution failed for data set 'dsCustomers'.
(rsErrorExecutingCommand) Get Online Help
3) Line 1: Incorrect syntax near '='.
I think it's pointing at my Data set in my data tab, but am not sure why it
will point there when it worked in test mode!
Does anyone know how to fix this problem?Hello,
I am guessing that by dynamic queries you mean queries with parameters.
I am using them with a teradata db and to get the parameters to work in
the query I have to precede the query with =" and end it with ". To
insert the parameters you put "+Parameter!Name.Value+".
I hope this helps,
Lilja|||Lilja, first I want to thank you for lending a hand. I appreciate it very
much.
Second, I want to restate you comments in my own words. For a dynamic query
with Parameters, I will start my statement with an equals sign (=).
To the left of the equal sign there will be nothing. To the right of the
equals I need to put the equation (which calls an assembly outside of my
project) in between double quotes: ="proj.class.method()".
Whenever I want to add a parameter to the equation I drop out of the string,
and add the literal with a + before and after the parameter:
="proj.class.method("+Parameter!Name.Value+")"
When I run the project in local debug mode, it works fine. When I change
the settings to Debug, give it a path "http://localhost/ReportServer" and
deploy it, I still get the same error. Is there something else that could be
the problem?
My equation runs over into a second line. Does it need the VB underscore to
carry the line over? ' _ '
Thank you very much again.
KurT
"kisa" wrote:
> Hello,
> I am guessing that by dynamic queries you mean queries with parameters.
> I am using them with a teradata db and to get the parameters to work in
> the query I have to precede the query with =" and end it with ". To
> insert the parameters you put "+Parameter!Name.Value+".
> I hope this helps,
> Lilja
>

Dynamic queries

Hi,

This question has been asked probably million times, but it sems that I cannod find right answer on search engines.

I need to send parameters to my stored procedure, but not only parameters. For example in where clause I have something like:

where myID = 12

I need to be able to filter results on myID, but one time I need it to be eqal to 12 and other time I need it to be different (<>) from 12. Some time I even need to add another condition like myID2 = 1. Can I solve this without additional procedures?

I believe that I saw solution in3-Tier ArchitectureTutorial Series few weeks ago but it seems that something changed, and I cannot find it anymore. Can anyone help?

you should be able to do this without additional procedures,

why not build a sort of "query builder"

you would start with as much of the sql string as is commin in all queries so

string strSql = "select * from myTable where myID";

then depending on logic in your app (select Equals or not) you start building your query string

if(ddEqualsOrNot.SelectedValue == "equals"){
strsql+= "="; //adds on an equals
}else if(ddEqualsOrNot.SelectedValue == "not equal"){
strsql+= "!=" //adds not equals
}

then finish off the sql line
strSql += " @.myId;";

then you can build the same command or dataAdapter object and add @.myId as a parameter. depending on if they picked Equals or not equals your string would finally look like

"select * from myTable where myId = @.myId;"

select * from myTable where myId != @.myId;

hth,

mcm

|||

This is what first came in my mind, but I just prefer to use Stored procedures over sql in code behind files.

However, for your proposed solution I have one question are you sure that I should use "!="? Is it "<>"?

|||

<> is fine.

Also you can contruct sql script in stored procedure.

declare @.sql_statementnvarchar(1024)--initset @.sql_statement ='select * from ';--build sql here-- print sql and execprint @.sql_statementexecsp_executesql @.sql_statement

dynamic queries

Hi,

I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A

if Param1=A, then I need to use query1 for the report

if param1 = B, then I need to use query2 for the report.

I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.

thanks

Hi,

you can use a stored procedure for this, using the inside procedural logic for deciding which query should be executed.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

dynamic queries

Hello All,
I are just starting to use reporting server (2003) and I have hit a
problem. The reports I have created so far have had 3 or 4 sets of possible
parameters, and I have created a different report based on each set. i.e.
agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
The problem now is the next report has far, far too many parameter to
possibly create a report for each permutation. I can't figure out how to
dynamically create the where clause based on which parameters the report
gets passed. Just to clarify, the entire where clause needs to be dynamic,
not just the value.
I need something like this
SELECT blah
FROM table
WHERE IIF(Params!series <> null, "series=Params!series", "")
but this doesn't work.
any help is greatly appreciated,
CraigThe best option for this is to make use of Dynamic queries where you
build your queries dynamically inside the stored proc. You can pass the
superset of all parameters to the SP and check inside the SP. Refer
example below
Inputs to SP;
in_Name
in_Age
in_Sex
in_AcctNum
var str = 'Select * from ACCT_MASTER where'
if (in_name != null)
str = str + 'Name = ' + in_Name
etc.. etc...
finally execute the sp as
exec(str)
This is one of the way of satisfying your requirement|||YOu can also do
="SELECT blah FROM table "
& IIF(Parameters!Series.Value is nothing, ""," Where series='" &
Paramters!Series.Value & "'")
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Craig L" wrote:
> Hello All,
> I are just starting to use reporting server (2003) and I have hit a
> problem. The reports I have created so far have had 3 or 4 sets of possible
> parameters, and I have created a different report based on each set. i.e.
> agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
> agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
> The problem now is the next report has far, far too many parameter to
> possibly create a report for each permutation. I can't figure out how to
> dynamically create the where clause based on which parameters the report
> gets passed. Just to clarify, the entire where clause needs to be dynamic,
> not just the value.
> I need something like this
> SELECT blah
> FROM table
> WHERE IIF(Params!series <> null, "series=Params!series", "")
> but this doesn't work.
> any help is greatly appreciated,
> Craig
>
>

Monday, March 26, 2012

Dynamic query

Dynamic Query

dynamic query

dynamic query

dynamic query

dynamic query

Dynamic Queries Errors in Report Manager

Dynamic queries

dynamic queries

dynamic queries

sql dwh problem

Blog Archive

About Me