nesting limit exceeded...but I don't understand why.

I have written a recursive function that generates an XML hierarchy. I have gone through the data being selected and I have verified that the hierarchy is only 19 levels at its deepest. (there are several thousand records involved, and 1 level may have several hundred records). But, I am receiving this error:
"Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the function I have written:

ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(SELECT s.ID As "@.id",
s.TITLE as "@.title",
s.CLASS_ID as "@.clsID",
CASE WHEN s.PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(s.ID)
END
FROM SCENE s
WHERE s.PARENT_ID = @.SceneID
FOR XML PATH('Scene'), TYPE)
END

Does anyone have any suggestions as to why I may be getting this error, or how to debug for it or work around it?

Thank you for any advice you can give.

Do you have any records in which ID = PARENT_ID?

Run SELECT ID, PARENT_ID FROM SCENE WHERE ID = PARENT_ID and see.

If so, you might want to consider adding a check constraint to the table that would forbid such a circumstance. Another alternative might be to exit the function if the function detects ID = PARENT_ID.

|||

When I mockup with:

create table dbo.scene
( id integer,
parent_id integer,
class_id integer,
title varchar(20)
)
go

insert into dbo.scene values (1, null, 1, 'This is a test')
insert into dbo.scene values (2, 1, 1, 'Record #2')
insert into dbo.scene values (3, 2, 1, 'Record #3')

and run:

select dbo.fn_WPMTREE (1) as [the Scene]

I get:

-- the Scene
-- -
-- <Scene id="2" title="Record #2" clsID="1"><Scene id="3" title="Record #3" clsID="1" /></Scene>

Is this what you expect?

|||Absolutely brilliant.

I had a scene that had a PARENT_ID = ID. And, that caused the infinite loop. I thought it was a loop being created somewhere, but I thought it was of the type Scene1.Parent_ID = Scene2.ID; Scene2.Parent_ID = Scene1.ID.
But, it was even more direct than that.

Thanks so much!
Sincerely.
roger

Nested table concept in SQL Server

Hi all,

What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?

Eg in Oracle :

SELECT t.* FROM TABLE(nested_table_datatype) t;

( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )

Please give the equivalent for above...

Thanks,
Samthank $deity, there is no equivalent in sql server, for nested tables are evil|||MS-SQL is a relational database. Oracle is a database with an SQL-like command language. There is a significant difference, neither one is inherantly better or worse than the other. They aren't comparable.

No relational database can have anything like Oracle's nested tables. Nested tables implicitly violate the first normal form.

In a relational database like MS-SQL, you can do exactly the same thing as a nested table by using a foreign key relationship. Create a second table using the same schema you would use for an Oracle nested table, adding a "link" column. Include the value from the "link" column in the parent row, so that you can join the main table to the logically "nested" table.

-PatPsql

NESTED SQL QUESTION and aggregate function

I have have three tables that I need to pull information from, and
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:
Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)
Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)
Location (
LocationID int (PK)
LocationName varchar(50)
)
Here's the data I need displayed as it would be shown in a "flat"
return:
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate
would return results:
Joe Inspector DEARBORN 2006-02-09 10:00:07
Joe Inspector DEARBORN 2006-02-09 10:10:04
Joe Inspector DEARBORN 2006-02-09 10:19:19
John Smith ANN ARBOR 2006-02-09 14:20:35
John Smith DEXTER 2006-02-09 14:21:38
Jane Doe CLINTON 2006-02-09 11:40:49
Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:
Joe Inspector DEARBORN 2006-02-09 10:00:07
John Smith ANN ARBOR 2006-02-09 14:20:35
Jane Doe CLINTON 2006-02-09 11:40:49
Another Guy <NULL> <NULL>
Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)
I'll be passing in the date (not the time) as a parameter.
I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.
Any help or insight will be greatly appreciated.
Christiani think this will work...
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate
post DDL and insert statement for more clear solutions|||SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection
"kaczmar2@.hotmail.com" wrote:

> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
> Inspections (
> InspectionID int (PK),
> LocationID int (FK),
> InspectorID int (FK),
> InspectionDate datetime
> )
> Inspectors (
> InspectorID int (PK)
> InspectorName varchar(50)
> )
> Location (
> LocationID int (PK)
> LocationName varchar(50)
> )
> Here's the data I need displayed as it would be shown in a "flat"
> return:
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
> would return results:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> Joe Inspector DEARBORN 2006-02-09 10:10:04
> Joe Inspector DEARBORN 2006-02-09 10:19:19
> John Smith ANN ARBOR 2006-02-09 14:20:35
> John Smith DEXTER 2006-02-09 14:21:38
> Jane Doe CLINTON 2006-02-09 11:40:49
> Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> John Smith ANN ARBOR 2006-02-09 14:20:35
> Jane Doe CLINTON 2006-02-09 11:40:49
> Another Guy <NULL> <NULL>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
> I'll be passing in the date (not the time) as a parameter.
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
> Any help or insight will be greatly appreciated.
> Christian
>|||Sorry, that last JOIN should be LEFT, not INNER.
--
"Mark Williams" wrote:
> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Tod
ay')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection
"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
> --
> "kaczmar2@.hotmail.com" wrote:
>|||Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:
Tony Inspector ANN ARBOR 16:13:40
Tony Inspector YPSILANTI 17:19:08
Joe Schmoe PLAINWELL 13:12:39
Jane Doe GRAND RAPIDS 11:42:27
Jane Doe GRANDVILLE 12:48:00
Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.
Mark Williams wrote:
> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
> "Mark Williams" wrote:
>|||Terribly sorry,
SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
"kaczmar2@.hotmail.com" wrote:

> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
> Tony Inspector ANN ARBOR 16:13:40
> Tony Inspector YPSILANTI 17:19:08
> Joe Schmoe PLAINWELL 13:12:39
> Jane Doe GRAND RAPIDS 11:42:27
> Jane Doe GRANDVILLE 12:48:00
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
> Mark Williams wrote:
>|||Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."
This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.
I have attached the DDL and INSERT statements as requested:
CREATE TABLE [dbo].[Inspections] (
[InspectionID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[InspectorID] [int] NOT NULL ,
[InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Inspectors] (
[InspectorID] [int] NOT NULL ,
[InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspections] ADD
CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
(
[InspectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspectors] ADD
CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
(
[InspectorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] ADD
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY]
GO
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')
INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')
Thank you very much for your help.|||If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!
You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!
Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for
SELECT inspector_name, location_name, MIN(inspection_date)
-- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
FROM Inspectors AS P
LEFT OUTER JOIN
Inspections AS I
ON P.inspector_id = I.inspector_id
LEFT OUTER JOIN
Locations AS L
ON I.location_id = L.Location_id
WHERE I.inspection_date '2006-02-09')
AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;|||Mark-
Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:
SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')
FROM Inspectors p
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
FROM Inspections i
WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
GROUP BY i.InspectorId /* name */
) t1
INNER JOIN
(
SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
FROM Inspections i
INNER JOIN Location l ON i.LocationId = l.LocationId
) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
Thanks again for your help!!!|||CELKO-
Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.sql

nested loop function

I want to know how to create a recursive loop/function in SQL, I can't seem to figure out how to do it.

The database table I am working with is simply the following:

SeedID, ThisParentSeedID

1, 0

2, 1

3, 1

4, 2

5, 4

6, 5

7, 6

8, 7

9, 7

10, 7

11, 10

12, 0

13, 0

14, 0

The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).

I want to know how to create a SQL script that is "object oriented" in that I will not have to create as many levels of nested scripts as there are nested "children" in the data.

What I am wanting to figure out is, with a single script, "which sub-children are assigned to [@.SeedID]"? So if this script was called, and @.SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @.SeedID = 12, it would return null. If @.SeedID = 7, it would return (8,9,10,11)

I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

CTE (Common Table expression) is best suited for your needs. Here you go

Declare @.ID as INT
SET @.ID = 7

;WITH myCTE AS
(
SELECT SeedID, ParentID FROM Seeds where SeedID = @.ID
UNION ALL
SELECT Seeds.SeedID, Seeds.ParentID From Seeds INNER JOIN myCte
ON Seeds.ParentID = myCte.SeedID
)
SELECT SeedID FROM myCTE where SeedID <> @.ID

|||

--Create the functionCREATE FUNCTION dbo.udf_GetChildren (@.parentIdint )RETURNSVarchar(100)ASBEGINDECLARE @.Childvarchar(100)SELECT @.Child =coalesce(@.child,'') + (casewhen @.childisnot nullthen','else''end ) +convert(varchar,SeedId)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdSELECT @.Child = @.Child + dbo.udf_GetChildren(seedid)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdReturnCoalesce(@.child,'')END--Call the functionSELECT dbo.udf_GetChildren(1)

Nested IIF statement in jump to URL function

Afternoon
I am trying to do a "jump to URL" expression that works as follows:
=IIF(isnothing( A FIELD ) , iif crm url etc etc...... , iif crm url etc etc
........)
But as yet can not get it to work.
Has anyone tryed something simular?
Thanks
Steve DAnother option would be likely to use a computed column when extracitng the
data...
Else it's best to tell us anyway waht is the result you hvae. It should
work.
--
Patrice
"Steve Dearman" <steve.dearman@.grant.co.uk> a écrit dans le message de news:
uUvrLGQpGHA.4116@.TK2MSFTNGP03.phx.gbl...
> Afternoon
> I am trying to do a "jump to URL" expression that works as follows:
> =IIF(isnothing( A FIELD ) , iif crm url etc etc...... , iif crm url etc
> etc ........)
> But as yet can not get it to work.
> Has anyone tryed something simular?
> Thanks
> Steve D
>|||I'm using the jump to Url box in the properties of the cell im using, when i
run the report i get no errors but also the link doesn't show up at all
which would point to my multiple iif statement being invalid for this use.
Anyone else managed to do this some other way?
"Patrice" <scribe@.chez.com> wrote in message
news:%238EWsTRpGHA.3584@.TK2MSFTNGP05.phx.gbl...
> Another option would be likely to use a computed column when extracitng
> the data...
> Else it's best to tell us anyway waht is the result you hvae. It should
> work.
> --
> Patrice
> "Steve Dearman" <steve.dearman@.grant.co.uk> a écrit dans le message de
> news: uUvrLGQpGHA.4116@.TK2MSFTNGP03.phx.gbl...
>> Afternoon
>> I am trying to do a "jump to URL" expression that works as follows:
>> =IIF(isnothing( A FIELD ) , iif crm url etc etc...... , iif crm url etc
>> etc ........)
>> But as yet can not get it to work.
>> Has anyone tryed something simular?
>> Thanks
>> Steve D
>|||I sorted it out my self, thanks anyway.
When your using more than one IIF statement in the jump to url property you
need to use the full field name eg.
First(Fields!'field name'.Value , " ' dataset name' ") other wise it wont
work.
May work without first but i have not tryed it.
Steve D
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:eLDlQTYpGHA.2400@.TK2MSFTNGP03.phx.gbl...
> I'm using the jump to Url box in the properties of the cell im using, when
> i run the report i get no errors but also the link doesn't show up at all
> which would point to my multiple iif statement being invalid for this use.
> Anyone else managed to do this some other way?
>
> "Patrice" <scribe@.chez.com> wrote in message
> news:%238EWsTRpGHA.3584@.TK2MSFTNGP05.phx.gbl...
>> Another option would be likely to use a computed column when extracitng
>> the data...
>> Else it's best to tell us anyway waht is the result you hvae. It should
>> work.
>> --
>> Patrice
>> "Steve Dearman" <steve.dearman@.grant.co.uk> a écrit dans le message de
>> news: uUvrLGQpGHA.4116@.TK2MSFTNGP03.phx.gbl...
>> Afternoon
>> I am trying to do a "jump to URL" expression that works as follows:
>> =IIF(isnothing( A FIELD ) , iif crm url etc etc...... , iif crm url etc
>> etc ........)
>> But as yet can not get it to work.
>> Has anyone tryed something simular?
>> Thanks
>> Steve D
>>
>

nested ifs in custom code

Is it not possible to have nested ifs in a custom code function? I keep getting an error message when I try it.Never mind. Sorry!

Nested IF statement and Declare problem

This is prolly more of a gut check, but needed to know if this looks right.
I am making another Scalar function..
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int(1)
AS
BEGIN
Declare @.intOffset int
If (left(tradesymbol, 1) = '@.')
If (isnumeric(left(right(tradesymbol, 6), 1))
@.intOffset = 1
If left(tradesymbol, 1) = '+'
If isnumeric(left(right(tradesymbol, 6), 1)
@.intOffset = 1
IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
If isnumeric(left(right(tradesymbol, 5), 1)
@.intOffset = 1
RETURN @.intOffset
END
I was getting an error because I was using the 'then' statement in there
(remember..i'm a VB programmer... and I did check out the bol site..lol)
I took the 'then' statements out. and now the only error that comes up is th
e:
'Oncorrect syntax near '@.intOffset' ' Error.
I'm not sure if this is because of the way that it's being used in the
function, or if i've got something bass ackwards.
Thanks for your input!
~Doc
www.krushradio.com - Internet Radio for the rest of usTry replacing
@.intOffset = 1
with
SET @.intOffset = 1
HTH
Vern
"Daniel Regalia" wrote:

> This is prolly more of a gut check, but needed to know if this looks right
.
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @.intOffset int
> If (left(tradesymbol, 1) = '@.')
> If (isnumeric(left(right(tradesymbol, 6), 1))
> @.intOffset = 1
> If left(tradesymbol, 1) = '+'
> If isnumeric(left(right(tradesymbol, 6), 1)
> @.intOffset = 1
> IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
> If isnumeric(left(right(tradesymbol, 5), 1)
> @.intOffset = 1
> RETURN @.intOffset
> END
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
> I took the 'then' statements out. and now the only error that comes up is
the:
> 'Oncorrect syntax near '@.intOffset' ' Error.
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
> Thanks for your input!
> ~Doc
> --
> www.krushradio.com - Internet Radio for the rest of us|||Not one.. there are lots of changes :)
no offences.
Here is the function.. Hope this helps.
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @.intOffset int
If (left(@.tradeSymbol, 1) = '@.')
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
If left(@.tradeSymbol, 1) = '+'
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
IF left(@.tradeSymbol, 1) <>'@.' and left(@.tradeSymbol, 1) <> '+'
If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
set @.intOffset = 1
RETURN @.intOffset
END|||Gave it a shot....it didn't like it
Incorrect syntax near the keyword 'Set'
If (left(tradesymbol, 1) = '@.')
If (isnumeric(left(right(tradesymbol, 6), 1))
Set @.intOffset = 1
If left(tradesymbol, 1) = '+'
If isnumeric(left(right(tradesymbol, 6), 1)
Set @.intOffset = 1
IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
If isnumeric(left(right(tradesymbol, 5), 1)
Set @.intOffset = 1
--
www.krushradio.com - Internet Radio for the rest of us
"Vern Rabe" wrote:
> Try replacing
> @.intOffset = 1
> with
> SET @.intOffset = 1
> HTH
> Vern
> "Daniel Regalia" wrote:
>|||I feel it can better be written this way. You can validate better than me.
You should be a procedural logic expert :) Let me know.
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @.intOffset int
Set @.intOffset = 0
If (left(@.tradeSymbol, 1) = '@.') or (left(@.tradeSymbol, 1) = '+')
begin
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
end
else
begin
If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
set @.intOffset = 1
end
RETURN @.intOffset
END|||None Taken...
It's a learning experience for me :D. Just add this question to my beer
tab. Thanks OmniBuzz
~Doc
www.krushradio.com - Internet Radio for the rest of us
"Omnibuzz" wrote:

> Not one.. there are lots of changes :)
> no offences.
> Here is the function.. Hope this helps.
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int
> AS
> BEGIN
> Declare @.intOffset int
> If (left(@.tradeSymbol, 1) = '@.')
> If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
> set @.intOffset = 1
> If left(@.tradeSymbol, 1) = '+'
> If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
> set @.intOffset = 1
> IF left(@.tradeSymbol, 1) <>'@.' and left(@.tradeSymbol, 1) <> '+'
> If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
> set @.intOffset = 1
> RETURN @.intOffset
> END
>|||Sure Sir. I remember the first one you promised too..
Anything for a beer :)
"Daniel Regalia" wrote:
> None Taken...
> It's a learning experience for me :D. Just add this question to my beer
> tab. Thanks OmniBuzz
> ~Doc
>
> --
> www.krushradio.com - Internet Radio for the rest of us
>
> "Omnibuzz" wrote:
>|||One problem is that your parenthesis are not properly matching up. Another,
and this is just personal preference, is you are not using begin and end to
group your if else logic. I prefer to have a begin and end for every if
statement, and indent accordingly. It makes the code easier to follow, and
leaves no confusion as to the order of nested ifs.
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:4618C615-2579-4999-B0BC-3DB935F7527F@.microsoft.com...
> This is prolly more of a gut check, but needed to know if this looks
right.
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @.intOffset int
> If (left(tradesymbol, 1) = '@.')
> If (isnumeric(left(right(tradesymbol, 6), 1))
> @.intOffset = 1
> If left(tradesymbol, 1) = '+'
> If isnumeric(left(right(tradesymbol, 6), 1)
> @.intOffset = 1
> IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
> If isnumeric(left(right(tradesymbol, 5), 1)
> @.intOffset = 1
> RETURN @.intOffset
> END
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
> I took the 'then' statements out. and now the only error that comes up is
the:
> 'Oncorrect syntax near '@.intOffset' ' Error.
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
> Thanks for your input!
> ~Doc
> --
> www.krushradio.com - Internet Radio for the rest of us

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }, [Measures].Orders Received Local)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.

Need to use MID function in SQL

When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?

CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.

I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.

Thanks.

Ernie

You have to combine sql sever string function

like "left" and "right" to achive you requirements

I think the equivalent of vb mid function is the "substring" function

This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.

USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname 

|||

create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')

one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test

and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test

and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks! I appreciate the help.