Constants to Sets

Hi,

I guess that's an easy one... I'm quite sure that my brain is gone for weekend... And I'm still getting used to MDX...

I'm using the min function to find out the minimum of two measures, so basically:

min({[Measures].[MeasureA],[Measures].[MeasureB]})

This works perfectly. But what I need is to have something like that:

min({[Measures].[MeasureA],[Measures].[MeasureB],0})

So that when A and B are both greater than 0 the result is 0. OK, I can do that with iif but that's not nice... There must be a way to add a constant to a set... I even need something like that:

min ({sum(...),sum(...),0})

I guess it's the same problem and the same solution...

Any help is very appreciated...

is this what you mean?

func(1,2) -> 0

func(-1,2) -> -1

func(-1,-2) -> -2

its not much prettier than iif -

Min(Min(0, a), Min(0, b))

|||

Well, you understood what I'm looking for... However I'm quite sure that this doesn't work...

The MDX min function expects a set. What you provide is value (as far as I know normally called an expression), that doesn't seam to work at all...

|||

sorry. . mdx is not my forte. . .

from the help -

min(Set-Expression, [Numeric-Expression]) evaluates Numeric over Set

can't you make a set from two constants?

Evalute Min Zero over a, And Min Zero over b,

'set' results and min -

Min( { Min({a},0), Min({b},0) } )

|||

ught. . Im not thinking. . .

isnt it

Min({a,b},0)

|||That was my first try... Doesn't work... The result is always 0, no idea why...|||

Hi Thomas,

How about using a calculated measure for the constant lower limit, like in this Adventure Works example:

>>

With Member [Measures].[LowLimitOrder] as 2500

Member [Measures].[MinOrderCount] as

Min({[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[LowLimitOrder]})

select {[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[MinOrderCount]} on 0,

[Product].[Product Categories].[Category].Members on 1

from [Adventure Works]

Internet Order Count Reseller Order Count MinOrderCount
Accessories 18,208 1,315 1315
Bikes 15,205 3,153 2500
Clothing 7,461 2,410 2410
Components (null) 2,646 2500

>>

|||

Deepak,

once again... THANKS for your superb support! Works like a charme...

Constants in TSQL

Hello,
Is there such a thing as constants in TSQL (similar to constants in
other languages)? I've looked in books online and can't find any
references to them.
ThanksHi,
Create a Master table which stores all the Constants. Table name will be
some thing like SYSCONTROL
Create table SYSCONTROL(Controlname Varchar(100), ControlValue
numeric(20,3))
Eg:-
insert into SYSCONTROL Values('Launchdate','08/10/2005')
Thanks
Hari
SQL Server MVP
"Frank Rizzo" <nospam@.nospam.com> wrote in message
news:OSvKkjHoFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Is there such a thing as constants in TSQL (similar to constants in other
> languages)? I've looked in books online and can't find any references to
> them.
> Thanks|||You can use DECLARE and SET/SELECT combination to define and assign a
constant value to the "constant"
DECLARE @.COLOR varchar(30)
SET @.COLOR = 'red' or SELECT @.COLOR ='red'
"Frank Rizzo" wrote:

> Hello,
> Is there such a thing as constants in TSQL (similar to constants in
> other languages)? I've looked in books online and can't find any
> references to them.
> Thanks
>|||Coskun T. wrote:
> You can use DECLARE and SET/SELECT combination to define and assign a
> constant value to the "constant"
> DECLARE @.COLOR varchar(30)
> SET @.COLOR = 'red' or SELECT @.COLOR ='red'
That's what I do now. It just seems like a PITA.
I'd settle for at least
declare @.color varchar(30) = 'red'
>
> "Frank Rizzo" wrote:
>|||You can build a table of constants within a schema using Standard SQL
like this:
CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
.);
INSERT INTO Constants DEFAULT VALUES;
The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one row.
In full SQL-92, you can create such as table as a VIEW with a row
constructor:
CREATE VIEW Constant (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);|||> In full SQL-92, you can create such as table as a VIEW with a row
> constructor:
> CREATE VIEW Constant (pi, e, phi, ..)
> AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);
You can accomplish this in SQL Server as well, using below technique:
CREATE VIEW Constant
AS
SELECT
CAST(3.141592653 AS float) AS pi
,CAST(2.718281828 AS float) AS e
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124026681.166459.209960@.o13g2000cwo.googlegroups.com...
> You can build a table of constants within a schema using Standard SQL
> like this:
> CREATE TABLE Constant
> (lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
> CHECK (lock = 'X'),
> pi REAL DEFAULT 3.141592653 NOT NULL,
> e REAL DEFAULT 2.718281828 NOT NULL,
> phi REAL DEFAULT 1.618033988 NOT NULL,
> ..);
> INSERT INTO Constants DEFAULT VALUES;
> The insertion creates one row, so the table ought to have a singular
> name. The "lock" column assures you that there is always only one row.
> In full SQL-92, you can create such as table as a VIEW with a row
> constructor:
> CREATE VIEW Constant (pi, e, phi, ..)
> AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);
>

CONSTANTS in bcp .fmt files

Hi,
How can we insert CONSTANTS in table columns using bcp's .fmt files? In case SQLLoader, we can specify CONSTANTs in .ctl files. How can it be done in case of bcp utility?
Thanks in advance
VijayYou can't! BCP does not support this.|||Originally posted by Paul Young
You can't! BCP does not support this.

Any workaround used normally?

Thanks|||Either add the data to your file before BCPing or run an update script to add the data after BCPing the file.|||Originally posted by vijay_kumar74
Any workaround used normally?

Thanks

What do you mean by Constant?

UPDATE myTable SET col1 = 'Something'

???????????????|||Yes, I think I'll go by updating table columns with the "CONSTANT" (e.g. $PROGRAM_NAME) values after running bcp.

Thanks for the help!|||You can check %ERRORLEVEL% to see if the BCP went okay and then follow up with an OSQL command to update the table. Not eloquent but functional.

Constant variables

Hi!
Is there a way to create constants (i.e. constant variables) in stored
procedures? Basically I'm looking for the T-SQL counterpart of Oracle
PL/SQL's
"x_var constant integer := 999;"-type declarations.
Thx,
AgostonHi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>|||No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> > Is there a way to create constants (i.e. constant variables) in stored
> > procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> > PL/SQL's
> > "x_var constant integer := 999;"-type declarations.
> >
> > Thx,
> > Agoston
> >
> >
>