While Inside Of A While?
Oct 16, 2000
I have a statement which might need a while inside of a while. The
start date - end date creates one record for a record insert. I have that working. But along with producing a record for every day there might be an
instance where something is dispersed 3 times a day for five days. I then need to create 3 records for every day for 15 records. This only happens on records if the daily dispersal is greater than 1. The code below works fine, but should I add a second while inside of the existing one for the @freq and increment it by one. Would an If or case inside of the while be better?
Thanks
set @freq = freq in table
set @nodays = datediff(day, @sdate - 1, @edate)
select @nodays
while @cnter < @nodays and
begin
--insert values
insert into PATIENT_MEDICATION_dispersal2_
values (@account_id,@caseid, @entcid, @ndcid, @sdate)
Set @cnter = @cnter + 1
set @sdate = @sdate + 1
end
View 2 Replies
ADVERTISEMENT
Sep 12, 2007
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
View 9 Replies
View Related
Nov 16, 2007
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
View 8 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
Jul 10, 2006
Hi all
As following I show my sql server query.Please just look at the blue code.How can I add a statement to do not read the code if value received is null, i.e., do not add INNER JOIN stm.Thanks a lot
string strCmd = "SELECT "; strCmd += " Codigo_cotacao as 'Cód. Proposta', "; strCmd += " Cod_empresa as 'Cód. Cliente', "; strCmd += " Nome_empresa as 'Nome Cliente', "; strCmd += " Negocios_atividades_propostas.Id_atividade_proposta as 'Cód. Atividade', "; strCmd += " Nome_atividade_proposta as 'Atividade', "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " Negocios_ramos.Cod_ramo as 'Cód. Ramo', "; strCmd += " convert(varchar,Data_cotacao,103) as 'Data Proposta', "; strCmd += " convert(varchar,Vigencia_cotacao_inic,103) as 'InÃc. Vigência', "; strCmd += " convert(varchar,Vigencia_cotacao_fim,103) as 'Térm. Vigência', "; strCmd += " Nome_status as 'Status', "; strCmd += " NVIdas_cotacao as 'Núm. de Vidas', "; strCmd += " Premio_cotacao as 'Prêmio Estimado', "; strCmd += " Nome_canal as 'Canal', "; strCmd += " Nome_corretor as 'Corretor', "; strCmd += " Nome_pac as 'PAC', "; strCmd += " Negocios_gerentes_canais.Nome_gerente as 'Gerente Canal', "; strCmd += " Negocios_gerente_beneficios.Nome_gerente as 'Gerente BenefÃcios', "; strCmd += " Nome_filial as 'Filial', "; strCmd += " Nome_regiao as 'Região', "; strCmd += " Nome_consultor as 'Consultor' "; strCmd += " FROM Negocios_cotacoes "; strCmd += " INNER JOIN Negocios_empresas ON Cod_empresa = Empresa_cotacao "; strCmd += " INNER JOIN Negocios_atividades_propostas ON AtivProp_cotacao = Negocios_atividades_propostas.Id_atividade_proposta "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " INNER JOIN Negocios_ramos ON Negocios_ramos.Cod_ramo = Ramo_cotacao "; strCmd += " INNER JOIN Negocios_status ON Id_status = Status_cotacao "; strCmd += " INNER JOIN Negocios_canais ON Cod_canal = Canal_cotacao "; strCmd += " INNER JOIN Negocios_corretores ON Cod_corretor = Corretor_cotacao "; strCmd += " INNER JOIN Negocios_pacs ON Cod_pac = Pac_cotacao "; strCmd += " INNER JOIN Negocios_gerentes_canais ON Negocios_gerentes_canais.Cod_gerente = GerenteCanal_cotacao "; strCmd += " INNER JOIN Negocios_gerente_beneficios ON Negocios_gerente_beneficios.Cod_gerente = GerenteBeneficios_cotacao "; strCmd += " INNER JOIN Negocios_filiais ON Negocios_filiais.Cod_filial = Filial_cotacao "; strCmd += " INNER JOIN Negocios_regioes ON Cod_regiao = Regiao_cotacao "; strCmd += " INNER JOIN Negocios_consultores ON Cod_consultor = Consultor_cotacao "; strCmd += " INNER JOIN Negocios_produtos ON Produto_cotacao = Id_produto "; strCmd += " WHERE Codigo_cotacao <> -1 "; if (hiddenddlEmpresa.Text != "Todas") strCmd += " AND Negocios_empresas.Cod_empresa = " + hiddenddlEmpresa.Text; if (hiddenddlCategoria.Text != "Todas") strCmd += " AND Negocios_categorias.Id_categoria = " + hiddenddlCategoria.Text; if (hiddenddlProduto.Text != "Todos") strCmd += " AND Negocios_produtos.Id_produto = " + hiddenddlProduto.Text; if (hiddenddlRamo.Text != "Todos") strCmd += " AND Negocios_ramos.Cod_ramo = " + hiddenddlRamo.Text; if (hiddenddlCorretor.Text != "Todos") strCmd += " AND Negocios_corretores.Cod_corretor = " + hiddenddlCorretor.Text; if (hiddenddlConsultor.Text != "Todos") strCmd += " AND Negocios_consultores.Cod_consultor = " + hiddenddlConsultor.Text; if (hiddenddlCanal.Text != "Todos") strCmd += " AND Negocios_canais.Cod_canal = " + hiddenddlCanal.Text; if (hiddenddlStatus.Text != "Todos") strCmd += " AND Negocios_status.Id_status = " + hiddenddlStatus.Text; if (hiddenddlRegiao.Text != "Todas") strCmd += " AND Negocios_regioes.Cod_regiao = " + hiddenddlRegiao.Text; if (hiddenddlGerenteCanal.Text != "Todos") strCmd += " AND Negocios_gerentes_canais.Cod_gerente = " + hiddenddlGerenteCanal.Text; if (hiddenddlFilial.Text != "Todas") strCmd += " AND Negocios_filiais.Nome_filial = '" + hiddenddlFilial.Text + "'"; if (hiddenddlAtividadeProposta.Text != "Todas") strCmd += " AND Negocios_atividades_propostas.Id_atividade_proposta = " + hiddenddlAtividadeProposta.Text; if (hiddenddlPAC.Text != "Todos") strCmd += " AND Negocios_pacs.Cod_pac = " + hiddenddlPAC.Text; if (hiddenddlGerenteBenef.Text != "Todos") strCmd += " AND Negocios_gerente_beneficios.Cod_gerente = " + hiddenddlGerenteBenef.Text; if (hiddentxtDataPropostaInic.Text != "" && hiddentxtDataPropostaFim.Text != "") strCmd += " AND Data_cotacao BETWEEN '" + hiddentxtDataPropostaInic.Text + "' AND '" + hiddentxtDataPropostaFim.Text + "'"; if (hiddentxtInicioVigenciaInic.Text != "") strCmd += " AND Vigencia_cotacao_inic BETWEEN '" + hiddentxtInicioVigenciaInic.Text + "' AND '" + hiddentxtInicioVigenciaFim.Text + "'"; if (hiddentxtDataPropostaFim.Text != "") strCmd += " AND Vigencia_cotacao_fim BETWEEN '" + hiddentxtFinalVigenciaInic.Text + "' AND '" + hiddentxtFinalVigenciaFim.Text + "'";
View 3 Replies
View Related
Jan 7, 2008
Is it possible to use IF inside a query, in the WHERE statement? I started with the query right below, but I onlye got error. After testing and rewriting a lot I ended up with the last query. But there hast to be a better, smarter, more elegant way to write this query? Any hint? ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all links from the requested category.-- ============================================= (@CategoryId int, @AdminFilter bit)AS SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId IF (@AdminFilter = 1) print 'AND Link.Hidden = @AdminFilter' ORDER BY Link.Title ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all NOT hidden links from the requested category.-- If in Administrators role the return ALL links (the hidden ones also).-- ============================================= (@CategoryId int, @AdminFilter bit)AS IF (@AdminFilter = 1) BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId ORDER BY Link.Title END ELSE BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId AND Link.Hidden = @AdminFilter ORDER BY Link.Title END Regards, Sigurd
View 4 Replies
View Related
Aug 7, 2000
I have a sql statement that has several OR statements in it which work fine. It looks like bottom below.
What I need to know is can you put a IF statement in a where clause like this. Such as
WHERE convert(datetime, patient_.df_admit_date, 101) > = @tdate or
if patient_.dru = "yes" convert(datetime, patinet_.df_admit_date, 101) > = @tdate - 8 or
WORKIN STATEMENT
select
PATIENT.ACCOUNT_ID,patient_.DF_PPD_POS_NEG, PATIENT.LAST_NAME, PATIENT.FIRST_NAME, PATIENT.MIDDLE_INIT, PATIENT.OTHER_ID_NUMBER,
PATIENT_.DF_ADMIT_DATE, PATIENT_.DF_PPD, PATIENT_.DF_PPD_POS_NEG, PATIENT_.DF_PPDB_DATE,
PATIENT_.DF_XRAY_DATE, PATIENT_.df_ppd_read, FROM
{ oj development.dbo.PATIENT PATIENT INNER JOIN development.dbo.PATIENT_ PATIENT_ ON
PATIENT.COMPANY_ID = PATIENT_.COMPANY_ID AND
PATIENT.DEPARTMENT_ID = PATIENT_.DEPARTMENT_ID AND
PATIENT.ACCOUNT_ID = PATIENT_.ACCOUNT_ID}
where
convert(datetime, patient_.df_admit_date, 101) > = @tdate or
convert(datetime,patient_.df_ppd, 101) >= @tdate - 2 or continued!!!
View 1 Replies
View Related
Jul 25, 2006
I am trying to do the following:
select * from table1
where createddate = '7/25/06'
and id = @temp
where @temp is char(1). The problem is @temp may be null or blank.
I didn't want to check @temp and then run the select statement.
How to check if @temp is not null or not empty inside WHERE clause and then run the select statement if @temp not empty?
Thanks for any help.
View 4 Replies
View Related
Jul 13, 2004
hai guys
how should we have to cal the store procedure inside the same store procedure.
for Example
Create procedure A
as
Begin
Select * from mytable
execute A
end.
is this the correct one
View 3 Replies
View Related
Sep 16, 2004
Hi:
I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
I ask this because I need a new sesssion with its own transaction.
Thanks,
Rui Ferreira
View 6 Replies
View Related
Jan 28, 2005
Hi :
Can anyone tell me if it is possible to get information like : servername/databasename inside an extended stored procedure ?
I checked the "srv_pfield" function but it only returns user/password information.
Thanks,
Rui
View 2 Replies
View Related
Dec 30, 2003
Hi, everyone. I was using ODBC everywhere in my code and now I'm considering using ADO in a new project. However, I don't want to throw all the old ODBC code away. Is that possible that I can use some wrapper to use ADO underneath while having a ODBC interface?
Thanks!
View 1 Replies
View Related
Feb 1, 2004
hello,
anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?
ur help is much appreciated!
thanks,
View 2 Replies
View Related
Apr 19, 2006
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
View 3 Replies
View Related
Dec 11, 2007
Hi ,
I have created in my sqlserver 2005 database a stored procedure with the following code.
///////////////////////////////////////////////////////////////
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[d_sty_print_menu_per_role_per_app2]
@menu_name VARCHAR(255) = NULL ,
@is_user VARCHAR(255) = NULL ,
@is_appl VARCHAR(255) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled)
AS
(--Anchor.
select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
(SELECT 'N' FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name --and
--PP.MENU_ITEM=tb1.menu_item
) as enabled
From sys_menu_item as tb1
where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
And tb1.item_parent= @menu_name
--Members
UNION ALL
select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item ,
(SELECT 'N' FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name -- and
-- PP.MENU_ITEM=tb1.menu_item
) as enabled
from sys_menu_item as tb2 , GetHierarchy
where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name
)
select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
From GetHierarchy
END
///////////////////////////////////////////////////////////////
So far so good.
The problem is in a specific part of the sql statement (which is also part of my business logic).
the following statement has a little problem.
(SELECT 'N' FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name --and
--PP.MENU_ITEM=tb1.menu_item
) as enabled
When I'm executing, it tells me that the Subquerry is returning more than one rows. I have tried to use TOP 1 but Sqlserver 2005 doesn't allow you to do that because you are inside a recursion.
I have tried to do this
(SELECT TOP 1 'N' FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name --and
--PP.MENU_ITEM=tb1.menu_item
) as enabled
But the system prevents me from doing that.
Any ideas ? How can I return only one row (I don't care which one) ?
Can I put this Sql statement in a function and then call it inside this recursion ? Is it permitted ?
I would mostly appreciated any help you can give me.
Thank you
zkar
View 2 Replies
View Related
Jan 15, 2008
Hi,
I have a quick question for you all...
If I use an OR statement inside the WHERE clause of a SELECT, should SQL Server evaluate both side of the OR or just the left hand side if it returns TRUE?
The reason I'm asking is that I have an SP the accepts a string parameter, this param is a search condition, say a name. The param is a nvarchar and can be null. In my SP I do this:
SELECT * FROM Customer
WHERE CustomerDeleted = 0
AND (
@searchText IS NULL OR CustomerID IN (SELECT ID FROM fn_GetSearchResults(@searchText))
)
The idea is that if the @searchText param is NULL then all Customers are return, otherwise the @searchText is used in a function to determine which customers match the criteria.
This only works if SQL stops evaluating the OR condition as soon as it comes accross a TRUE statement.
Thanks for any help
Graham
View 10 Replies
View Related
Jan 21, 2008
Hi,
I am wondering if it is possible ( I think I read it somewhere) to access the infomation inside CONTEXT_INFO inside CLR Code.
I am calling SET CONTEXT_INFO in my SQL Proc and I need to read the values back out inside a C# function.
Is this possible?
Thanks
Dave
View 4 Replies
View Related
Feb 28, 2007
Hello, inside of my SP i want to execute another SP, something like:
EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID
this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns).
Columns it returns:QResult , Threads , Answers
SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ?
or how do you get it?
Patrick
View 1 Replies
View Related
Sep 19, 2005
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View 4 Replies
View Related
Mar 25, 2006
I am using the createuserwizard, and have set up my own profile table which hold first name, last etc. In my CreateUserWizard, the field for Vendor is a dropdownlist that is to be populated from a list in a database. I set up the form without the createuserwizard, and everything went skippy, but when I placed it into the wizard, I now cannot access the control of the dropdownlist.
I have tried doing..
Using SqlConnection As New SqlConnection(WebConfigurationManager.ConnectionStrings("Personal").ConnectionString)Dim VendorID As DropDownListVendorID = CType(CreateUserWizard1.FindControl("VendorName"), DropDownList)
Dim MyReader As SqlDataReaderDim sel As String = String.Format("SELECT VendorID, Name FROM Vendors")Dim MyCommand As SqlCommandMyCommand = New SqlCommandMyCommand.CommandText = selMyCommand.CommandType = CommandType.TextMyCommand.Connection = SqlConnectionMyCommand.Connection.Open()MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)But when I go to databind, it says:Object reference not set to an instance of an object. VendorID.DataSource = MyReaderLine 30: VendorID.DataValueField = "VendorID"Line 31: VendorID.DataTextField = "Name"
View 3 Replies
View Related
Oct 23, 2000
I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated
declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate
END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor
View 2 Replies
View Related
May 16, 2002
begin
if @counter=2
update t_import_main set program2=@prog, g2=@gno,yr2=@yr, Program_code2=@pcode where uno=@oldu
case
when @pcode is null then
Program_code2=@gno
End
I get this error message
Incorrect syntax near the keyword 'case'.
What am i doing wrong??
View 2 Replies
View Related
Oct 12, 2005
Good day!
Just wanted to ask how can I execute a DTS package from inside a scheduled job.
I have 3 DTS Package, so instead of scheduling the 3 package separately...I created a job to execute the 3 packages in a single scheduled time.
My syntax inside the command window is:
EXEC DTSPackageName
But its not working.
Any help is greatly appreciated.
Thanks.
View 5 Replies
View Related
Feb 13, 2006
I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.
Exec sp_executesql N’exec procedurename {parameterlist}’, N’{parameter declaration}’, Parametervalues
Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.
Please help me.
Thanks!
View 3 Replies
View Related
Apr 4, 2007
Hello,
is it possible to build a loop for the following statement?
CREATE VIEW vwObjects as (
Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union
Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union
Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)
I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.
Thank you!
View 5 Replies
View Related
Mar 14, 2012
I have this SPROC that is executed by a SSIS every hour. Basically, between 1AM-6AM, I don't want the SPROC to execute what ever is inside. it still keeps running whatever is inside the IF statement.
TRUNCATE TABLE DataSubscription
DECLARE @StartTime AS VARCHAR(100)
DECLARE @EndTime AS VARCHAR(100)
SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'
IF GETDATE() NOT BETWEEN CAST(@StartTime AS DateTime) AND CAST(@EndTime AS DateTime)
BEGIN
-- Do stuff here.
END;
View 12 Replies
View Related
Feb 26, 2004
Hi ,
My reuierement is to execute a bcp to write the query output to a flat file. I am using the following syntax...
exec xp_cmdshell 'bcp "SELECT top 50 * tbl_xyz" queryout tbl_xyz_output.txt -c -S abc -U x -P x'
But I am getting the following error....
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
Establishing connection failed.
NULL
This bcp executes perfect if I am going to call from command prmomt.
Can anyone please help me in this....
Thanks in Advance.
-Mohit.
View 4 Replies
View Related
Jun 7, 2008
Hello,
I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?
View 1 Replies
View Related
Jun 7, 2008
Hello,
I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?
View 1 Replies
View Related
Jun 8, 2008
Hello,
I am trying to do something like this:
select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA
But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.
How do I do this?
View 2 Replies
View Related
Mar 4, 2014
Is it possible to do something like this:
Select a.address from person p
join Addresses a on p.AddressPointer =
Case when @UseNewAddress=1 then a.NewAddress else a.OldAddress end
It gives a syntax error but I'm wondering if there's another way to do it.
View 2 Replies
View Related
Mar 26, 2014
I have a simple table (SQL Server 2005) which stores employees clock-ins and clock-outs throughout the day:
+------------------------------------------+
¦ Employee ¦ PunchDateTime ¦ ActionType ¦
¦----------+------------------+------------¦
¦ John ¦ 2014/03/26 08:00 ¦ IN ¦
¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦
¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦
¦ John ¦ 2014/03/26 09:29 ¦ IN ¦
¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦
¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦
¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦
¦ John ¦ 2014/03/26 12:38 ¦ IN ¦
¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦
¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦
+------------------------------------------+
I want build a query that calculates time spent in and out. The end result should look like this:
+-------------------------------------------------------------+
¦ Employee ¦ PunchDateTime ¦ ActionType ¦ TimeIn ¦ TimeOut ¦
¦----------+------------------+------------+--------+---------¦
¦ John ¦ 2014/03/26 08:00 ¦ IN ¦ - ¦ - ¦
¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦ - ¦ - ¦
¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦ 00:40 ¦ - ¦
¦ John ¦ 2014/03/26 09:29 ¦ IN ¦ - ¦ 00:39 ¦
¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦ 02:23 ¦ - ¦
¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦ 01:26 ¦ - ¦
¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦ - ¦ 01:07 ¦
¦ John ¦ 2014/03/26 12:05 ¦ IN ¦ - ¦ 01:10 ¦
¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦ 04:16 ¦ - ¦
¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦ 05:07 ¦ - ¦
+-------------------------------------------------------------+
I need the total time spent IN and total time spent OUT, for each day.
The time spent will be calculated only for valid pairs of INs and OUTs, eliminating those that can't be paired (two or more consecutive INs/OUTs).
The results need to be by user, per one day. If the interval is longer than one day, the total time spent must be calculated for each day.
View 20 Replies
View Related
Apr 8, 2014
I need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:
select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble
where [KEY] = 'StringA'
The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.
I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.
View 2 Replies
View Related