Analysis :: Difference Between A Set Of Tuples And A Set Inside A Tuple

May 8, 2015

I wanted to understand the basic difference between the following two MDX queries which give two different outputs - 

1. Set of Tuples -

Query-
SELECT
[Measures].[Internet Order Count] ON 0
FROM
[ADVENTURE WORKS]
WHERE

[code]....

2. Tuples in a Set -

Query-
SELECT
[Measures].[Internet Order Count] ON 0
FROM
[ADVENTURE WORKS]

[code]...

From the output i am getting on the MDX, i understand that the analogy is not correct. But I am not able to understand the difference between the same.

View 2 Replies


ADVERTISEMENT

What Is Tuple / Tuples

May 9, 2006

Hi all,

what is tuple / tuples.
plz discuss in detail.
thanx

Sajjad

View 5 Replies View Related

Analysis :: SSAS Named Set With A Two Dimension Attribute Tuple

Jul 15, 2015

In my SSAS Cube I have created a dynamic named set "top 10 e-learnings by language" which consists out of a set of tuples. Each tuple has two attributes out of the same base dimension "training": attribute 1 is "sprache" (language) and attribute 2 is "training text".

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 eTrainings pro Sprache]
AS Generate(
{ [Training].[Sprache].[Sprache].Members },
TopCount(
EXISTING { [Training].[Sprache].CurrentMember * [Training].[Training Text].[Training Text].Members },
10,
[Measures].[Teilnahmen eTraining]
)
), DISPLAY_FOLDER = 'Training' ;

Normally a named set would be automatically visible in Excel Pivot under the dimension you used to create the named set, but it seems that named sets with tuples which have more than one attribute are placed in a separate folder "Sets" in between the measures and dimensions.Additionally in the SSAS cube browser this named set is not visible at all.Is there any way to tell the named set in which dimension it should appear or any workaround?

View 2 Replies View Related

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

View 6 Replies View Related

Cannot Operate On A Set With More Than 4,294,967,296 Tuples.

Apr 4, 2007

Below is an error that I run into often. Can someone tell me what is causing this and how to resolve it?



Executing the query ...

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

Execution complete





Here is the MDX that caused the error:





With

Member [Measures].[Total Count] as

'[Measures].[Crash Count]'

Member [Measures].[Total Fatal] as

'[Measures].[Fatal Crashes]'

Member [Measures].[Total Injury] as

'[Measures].[Injury Crashes]'

Member [Measures].[Total A Injury] as

'[Measures].[A Injury Crashes]'

Member [Measures].[Total B Injury] as

'[Measures].[B Injury Crashes]'

Member [Measures].[Total C Injury] as

'[Measures].[C Injury Crashes]'

Member [Measures].[Total 0 Property Damage] as

'[Measures].[0 Property Damage Crashes]'

SELECT

NON EMPTY { [Measures].[Total Count],

[Measures].[Total Fatal],

[Measures].[Total Injury],

[Measures].[Total A Injury],

[Measures].[Total B Injury],

[Measures].[Total C Injury],

[Measures].[Total 0 Property Damage],

[Measures].[Vehicle Count],

[Measures].[Person Count],

[Measures].[Fatalities],

[Measures].[0 Injuries],

[Measures].[A Injuries],

[Measures].[B Injuries],

[Measures].[C Injuries],

[Measures].[Injuries] }

ON COLUMNS,

NON EMPTY {[Crash].[Case Nbr].[Case Nbr] *

[Date].[Calendar Date].[Date] *

[Crash].[Weather].[Weather] *

[Crash].[Road Defects].[Road Defects] *

[Crash].[Collision Type].[Collision Type] *

[Crash].[Light].[Light] }



DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM

(select strtoset('{[Crash].[County].&[Morgan] }')

on columns From

(SELECT STRTOMEMBER('[Crash].[TS Crash Mile Station].[5.28]') :

STRTOMEMBER('[Crash].[TS Crash Mile Station].[5.77]')

on columns From

(select {descendants([Crash].[Case Nbr].Currentmember)}

on Columns from

[SDM_Reports] ))) where ([Reportability].[Is Reportable].&[

View 4 Replies View Related

Insertion And Updates On 20.000.000 Tuples Table.

Jun 23, 2006

Hi,I have a table with 20.000.000 of tuples.I have been monitoring the performance of the insertion and updates,but not convince me at all.The table have 30 columns, what and 12 of it, are calcultated column.The test that i do was this:1 Insertion with all the columns and calculing the calcultated columnsin the insertion sentence.1 insertion and all the columns calculated in @vars..1 insertion with the basic fields, and 10 updates.And the result was that the last test was the most performant.What is your opinion?

View 4 Replies View Related

SSRS 2005 Tuples Error

Jul 28, 2007

Hi,

I have created a cube with three dimensions and one fact. There is no problem While dragging the fields in the Browser window of the cube but the same fields dragging into the SSRS report i am getting the following message:

"The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples".


Can you please help me out.

Thanks
Dinesh

View 3 Replies View Related

4294967296 Tuple Limit And 32 Bit Architecture

Sep 5, 2007

We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:

Example


WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}

Error


The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.



We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.

Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?


View 2 Replies View Related

Selecting A Field In Every Tuple & Making It Bold

Nov 19, 2007

Hi,

i have a table, that i am trying to compare values. I want to highlight the cheapest field's contents (price) for each tuple. The field itself could be different for each field depending on its price. the table i have is below;

Pos |AlbumTitle |Artist |Virgin | Woolworths | hmv
-----+---------------+--------------+-------+-------------+---------
1 | Confessions |Usher | 2.99 | 9.99 | 13.79
2 | Back Home | Westlife | 2.00 | 5.99 | 3.19
3 | Spirit |Leona | 5.99 | 2.99 | 13.99
4 | Trust Me |Craig David| 7.99 | 11.99 | 6.63
5 | Sawdust |Killers | 22.99 | 10.99 | 8.39
6 | Greatest Hits |Spice Girls| 12.99 | 0.99 | 13.79

im not sure if this would be a good format for the table, i cant figure out how to find out which field is the lowest value and how at all i would be able to make the value itself bold or stand out somehow.

Any help/advice would be welcome!!!
Rich

View 3 Replies View Related

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?

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

EXEC Inside CASE Inside SELECT

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

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

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

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.

We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services

Jun 12, 2015

How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
 
I have table:

"Incoming goods"

Create table Income (         
ID int not null identity(1, 1)            
[Date] datetime not null,             
GoodID int not null,               
PriceDeliver decimal(18, 2) not null,               
PriceSalse decimal(18, 2) not null,               
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),             
CONSTRAINT FK_IncomeGood foreign key (GoodID)  references dbo.Goods ( ID )            
)

I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?

View 2 Replies View Related

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?

Nov 24, 2015

I am using

SSAS: version 2008
Excel: version 2013

I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:

DateKey:
    - Type: System.Integer
    - Value: yyyyMMdd
Date:
    - Type: System.DateTime
DateStr0:
    - Type: System.String
    - Value: dd/MM/yyyy (note: I am not using US culture)
    - Example: 01/11/2015  
DateStr1:
    - Type: System.String
    - Value: %d/%M/yyyy (note: I am not using US culture) 
    - Example: 1/11/2015  

Filtering on date is working fine:

Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:

I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:

But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.

For date values of my data coming from cube, excel consider them as 'General'

    2.1. But value here is same as it was in simple test.

    2.2. 'Date Filter' works just fine.

    2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell. 

    2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.

    2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

So, I don't know, what is the puzzle piece that I am missing.

1. Date filter works, group does not work

2. Excel consider it as 'General' string.

3. SSAS does not allow to change 'NameColumn' to Date.

View 2 Replies View Related

Analysis :: Running Analysis Service In Terms Of Port Usage?

May 30, 2015

I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.

View 2 Replies View Related

Analysis :: Creating Roles In Analysis Service Without AD Account

Aug 6, 2015

I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.

View 3 Replies View Related

Use Cubes From Analysis Services 2005 To Analysis Services 2000

Oct 17, 2007

Hi,

I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?

If yes, what is the procedure? The result of migration is the same in the two different versions?


If not, how can I solve this problem?

Thanks in advance.

View 3 Replies View Related

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 View Related

Stm Inside Sql Query

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

Can I Use IF Inside A Query?

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

IF Inside Of Where Clause

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

Is There A Way Do Use If Inside Where Clause?

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

Calling SP Inside The SP

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

New Session Inside SP?

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

Getting Servername Inside XP

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

Use ADO Inside ODBC?

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

Loop Inside SP

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

Using IF Inside SELECT ?

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

Cannot Use TOP Inside An Recursive SQL

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







Copyrights 2005-15 www.BigResource.com, All rights reserved