DML Usage in SQL 2008 1

[VIJSQL01]

In this thread, i need to discuss about DML commands usage in SQL 2008.

 

[VIJSQL01]

hi,

I need to retrive data from a two tables joining each other. The tables has fields like state name, YYYYMM, HPI_12M_change and others. There is one column called Period no which has period numbers like -155 to 259. Here 259 is maximum period and The period will be updated monthly when we process new data. hence the period would be 260 next time. I need to retrive the data for the periods ranging between 193 and maximim period. I dont want to specify the maximum period as 259 instead i want mantion as Max(Period_no).Because i need to give this sample data to marketing folks every month after we process the data. Since the data volume is huge, i want to automate it by writing some stored procedure, instead of writing query manually everytime. I tried to write a query as mentioned below....

Select distinct G.state_name, S.YYYYMM, S.HPI_12M_Change from REAS_HPI_STATE_Final S, REAS_STATE_BE G
where S.Tier_Code in(11)
and G.STATE_Code=S.State_Code
and S.period_no between 193 and MAX(Period_No)

When i tried above, i got the error message saying ....

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

i understood the error and thought of defining some variable for Min and Max period number and supply it in he begining of SP execution. I tried this way...

alter procedure Test1 @MIN_Period as integer='', @MAX_Period as integer=''
as Begin
Begin
select distinct G.state_name, S.YYYYMM, S.HPI_1M_Change from REAS_HPI_STATE_Final S, REAS_STATE_BE G
where S.Tier_Code in(11)
and S.period_no between @MIN_Period and @MAX_Period
and G.STATE_Code=S.State_Code
order by S.YYYYMM
end
end;

after that i tried to create a view for this query and use bcp command to generate some .xls files. As we know, view doesnt allow us to declare any variables.

My whole intention is, i have to generate few data files by executing BCP command where the query would be stored in view.
I suspect that, i may be wrong with my writing the query(1st) or do i need to something else. Please advice on this.

Thanks,
VJ

 

[gmmastros]

I see that you are using max serial no. Do you need to? I mean, why not just use period_no >= 193



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

WOW!, thanks for the solution. Sometime my mind doesn't work for the solution for this type of simple queries. Now got the fix for my problem.

Thanks again for your solution......

 

[VIJSQL01]

hi,

Can anybody help me in creating a job for executing multiple stored procedures?. I am not sure, this can be done only by using Job OR we can use one more stored procedure it self. Please note that, i have defined a input parameter for one of the SP.

If anybody let me know the method to be followed, i will do some R&D and find solution on my own.

Thanks,
VJ

 

[gmmastros]

You can call multiple stored procedures from within another stored procedures. For example:

Create Procedure dbo.CallOtherProcedures
   @Param1 int,
   @Param2 VarChar(20)
As
SET NOCOUNT ON

Exec NameOfFirstStoredProcedure @Param1
Exec NameOfSecondProcedure @Param2
Exec NameOfLastProcedure

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

hi gmmastros,

Thanks for the quick reply. I understood the way to be followed. But i just want to make sure that, can i able to pass the input for one of the procedure? Because that is the first procedure i need to execute for which the input need to be supplied and rest of the procedures should be executed sequentially.
Also one more thing i want to get it confirm that, the order we have placed the stored procedure, will the execution also takes place in the same order.

Thanks,
Vijay

 

[gmmastros]

hi gmmastros

Please call me George. Everyone else does.

But i just want to make sure that, can i able to pass the input for one of the procedure?

The example code I posted shows you how to pass a parameter to the "main" stored procedure and then how to use that parameter when calling subsequent stored procedures.

Also one more thing i want to get it confirm that, the order we have placed the stored procedure, will the execution also takes place in the same order.

Yes. When you call multiple procedures, SQL will execute it in the order in which you code them, and it will wait for the first procedure to end before calling the next one.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

Awesome, I got what i wanted......

Thanks a ton for getting me the solution so fast.

Thank you, thank you.......

 

[VIJSQL01]

hi,
sorry for bothering you again. I do see in the above conversation, somebody have mentioned the sample code where it shows how to pass a parameter to the "main" stored procedure and then how to use that parameter when calling subsequent stored procedures. If you could provide me that code, that would be really help full for me.....

Also, per your suggestion i have ceated main procedure which will run all other mini stored procedures.

Create Procedure QA_VALIDATION_SETUP
@TempLoad nvarchar(1000), @SourceLoad nvarchar(1000), @UpdatePeriod nvarchar(1000), @UpdateAgoVal nvarchar(1000),
@InsertData nvarchar(1000), @UpdateChangeVal nvarchar(1000), @TrunUnWantTab nvarchar(1000), @MKTFiles nvarchar(1000)
AS

Begin
Exec HPI_Source_Temp_Load @TempLoad
Exec HPI_Source_Data_Load @SourceLoad
Exec Update_Periods_Source_Data @UpdatePeriod
Exec Update_Ago_Values @UpdateAgoVal
Exec Insert_Data_Final_Tables @InsertData
Exec Update_Change_Values @UpdateChangeVal
Exec Truncate_Tables @TrunUnWantTab
Exec GenerateMKTFiles @MKTFiles
End;

Also, i need an help on displaying a message. In the above context if you take first procedure, i need to display a message saying 'Temp tables are loaded successfully' soon after its successfull execution and simillarly for other SPs also.

Also i need your help on avoiding showing Out put when i execute a BCP command.Here i tried SET NOCOUNT ON, unfortunately its not working.
E.g. Please go through below query and result set.

BCP:

exec master..xp_cmdshell 'bcp "Select * from REAS.dbo.State_12MC_SFC" queryout "\\10.48.164.48\datamove1\vijay\QA MKTG Source Files\State_12MC_SFC.xls" -U"hpi" -P"hpi" -T -c'

OUTPUT Message:

output
NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
NULL
3484 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 468 Average : (7444.44 rows per sec.)
NULL

Thanks for your patience...
Vijay

 

[VIJSQL01]

hello,

I am facing a problem in transposing data outputs from a table from row to columns.
I have to transpose the values which are in rows to the columns.
E.g.
StateName DataPeriod 1MonthChange
Alabama 262 10.65%
Alaska 262 07.54%
Arizona 262 03.00%
California 262 22.06%
. . .
. . .
. . .
. . .

i want above data to be get as a query result in the following manner.

StateName Period1, Period2, Period3, Period4
262, 261, 260, 259
Alabama 10.65% 08.56% 06.66% 22.34%
Alaska 11.56% 13.56% 45.66% 00.34%
Arizona 06.45% 17.32% 23.87% 15.67%

and so on.....

Is it possible to do that way? Please help.
Eagerly waiting for your suggestion.

Thanks,
Vijay






Alabama

 

[markros]

Take a look at using PIVOT in SQL Server. If you always want to show only 4 periods, then it's a simple problem.

PluralSight Learning Library

 

[VIJSQL01]

hello,

Thanks for the reply. I tried using PIVOT clause for this. Unfortunately, i am facing some syntax issue in my query statement.

The query which i tried:

select state_code, home_price_index from
(select home_price_index from REAS_HPI_STATE_Final) UP
PIVOT(SUM(home_price_index) For YYYYMM IN(200501 AND
MAX(YYYYMM)) P
order by state_code
GO

The error i am getting:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '200501'.

Thanks,
Vijay

 

[VIJSQL01]

Now i am able to retrive the same by using below query when it is static by using PIVOT clause. But when i put range for the field YYYYMM, it is throwing some syntax error.

Static query:i am able to retrive the data

select * from
(select state_code,YYYYMM,home_price_index from REAS_HPI_STATE_Final where tier_code in(0)
and state_code in('00','01')) up
PIVOT
( max(home_price_index) For YYYYMM IN([200501])) as P

But after putting range(dynamic) for YYYYMM:i am not able to retrive the data.

tried option1:
select * from
(select state_code,YYYYMM,home_price_index from REAS_HPI_STATE_Final where tier_code in(0)
and state_code in('00','01')) up
PIVOT
( max(home_price_index) For YYYYMM between 200501 and max(YYYYMM)) as P

Error:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'between'

tried option2:

select * from
(select state_code,YYYYMM,home_price_index from REAS_HPI_STATE_Final where tier_code in(0)
and state_code in('00','01')) up
PIVOT
( max(home_price_index) For YYYYMM IN(YYYYMM between 200501 and max(YYYYMM))) as P

Error i am getting:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'between'

I am confused. Please help me on this.

Thanks in advance,

 

[markros]

No wonder you're getting errors as your code is not syntactically correct. It looks like you need to use dynamic SQL, but I think you first need to master the regular PIVOT (not dynamic).

Start from BOL and try all the samples. Then try writing your query as regular PIVOT (use 200501 and 200502 for now). Once you can do this and show me the correct code, we'll go with dynamic PIVOT as it's a bit more complex (but just a tiny bit).


PluralSight Learning Library

 

[VIJSQL01]

hi markros,

i did try with static PIVOT which worked for me. Like i mentioned in my previous post, i m facing problem with my dynamic one. My doubt is, is it possible to use range like ' between 200501 and max(yyyymm)' in PIVOT clause. If possible how to use it(because the way i am using, i am getting error).If not possible what would be alternative for this.
I guess dynamioc PIVOT is involved with writing some SP.

Please help on this.

Following code works for me absolutely....

select * from
(select state_code,YYYYMM,home_price_index from REAS_HPI_STATE_Final where tier_code in(0)
and state_code in('00','01')) A
PIVOT
( max(home_price_index) For YYYYMM IN([200501], [200502], [200503], [200504])) as P

Result set:
state_code 200501 200502 200503 200504
00 169.8546 173.0733 176.7008 179.2819
01 125.2771 126.3244 127.2348 128.4458

Thanks,

 

[markros]

Great. No, you can not use a range in PIVOT clause (otherwise we would not need dynamic).

If you don't know the YYYYMM in advance, we need to use dynamic PIVOT.

Here is how:

declare @SQL nvarchar(max), @Cols nvarchar(max)
set @Cols = stuff((select ', ' + quotename(YYYYMM)
from (select distinct YYYYMM from REAS_HPI_State_Final) X
order by YYYYMM for XML PATH('')),1,2,'')

set @SQL = 'select * from
(select state_code,YYYYMM,home_price_index from REAS_HPI_STATE_Final where tier_code in(0) 
and state_code in('00','01')) src
PIVOT
( max(home_price_index) For YYYYMM IN( +@Cols +')) as Pvt'

execute (@SQL)

From the top of my head. You can also PRINT @SQL first to verify it.


PluralSight Learning Library

 

[markros]

In the code above, add your WHERE clause to CTE (derived table in the first statement) also, I forgot.

PluralSight Learning Library

 

[VIJSQL01]

Thanks markros, this code is much usefull. I tried to change it according to my requirenment.

The following code i tried and its working fine for me.

declare @SQL nvarchar(max), @Cols nvarchar(max)

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* from
(select state_code,Period_no,HPI_12M_Change from REAS_HPI_STATE_Final where tier_code in(0)
) src
PIVOT
( max(HPI_12M_Change) For Period_no IN( '+@Cols +')) as Pvt,
REAS_STATE_BE r
where pvt.STATE_CODE = R.STATE_Code'

execute (@SQL)

But unfortunately its not sufficing complete requirement. I need change values which i am retrieving should be multiplied by 100. Because my comparision file is in percentage. When i use * operator, its throwing syntax error.

I tried in the following way, which is obviously throwing error.

declare @SQL nvarchar(max), @Cols nvarchar(max)

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* from
(select state_code,Period_no,HPI_12M_Change*100 from REAS_HPI_STATE_Final where tier_code in(0)
) src
PIVOT
( max(HPI_12M_Change*100) For Period_no IN( '+@Cols +')) as Pvt,
REAS_STATE_BE r
where pvt.STATE_CODE = R.STATE_Code'

execute (@SQL)

Error i am getting,

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '*'.

Please let me know how to cope with this.

Thanks,

 

[markros]

Try

declare @SQL nvarchar(max), @Cols nvarchar(max)

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* from
(select state_code,Period_no, HPI_12M_Change*100 as [Percents] from REAS_HPI_STATE_Final where tier_code in(0) 
) src
PIVOT
( max([Percents]) For Period_no IN( '+@Cols +')) as Pvt 
INNER JOIN REAS_STATE_BE r
ON pvt.STATE_CODE = R.STATE_Code'

execute (@SQL)

You multiply by 100 in the Source table, you don't need to multiply again.

PluralSight Learning Library