Concatenating fields: view

Showing posts with label view. Show all posts

Tuesday, March 20, 2012

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:
Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:
Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!

Conditionally extraction of data from a view

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Phil Brammer wrote:
Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

Phil Brammer wrote:
Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

Thanks Phil for your help!

Conditionally CREATE a VIEW in a script

Hi,

I would like to create a view depending on a condition check first. However, I do not seem to the able to put a 'CREATE VIEW' within an IF statement. The following example demonstates what I am trying to achieve (please excuse the triviality of the example):

IF NOT col_length('authors','city') IS NULL

BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
END
ELSE
BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
END

When I try to parse/run this I get the following syntax error:

"Incorrect syntax near the keyword 'VIEW'."

Any help would be much appreciated.

Thanks.

Try the code below.

Chris

Code Snippet

DECLARE @.sqlstring NVARCHAR(4000)

IF NOT col_length('authors', 'city') IS NULL
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address + '', '' + city) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END
ELSE
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END

|||

I think this looks misguided. Rather than changing the view that is is created dynamically, I think you need to change the view permanently so that both views can be represented by a singular view that uses CASE construct. Hang on and if I don't get you an example, I imagine someone else will.

Maybe something like this:

create view testView
as

select au_fname + ' ' + au_lname
as fullName,
address
+ case when len(rtrim(city)) = 0
then ''
else ', ' + city
end
as address
from authors

go

select * from testView

/*
fullName address
--
Johnson White 10932 Bigge Rd., Menlo Park
Marjorie Green 309 63rd St. #411, Oakland
Cheryl Carson 589 Darwin Ln., Berkeley
*/

|||

Hi Chris,

I had thought about doing that but the real view is quite large and I was trying to avoid dealing with string manipulation but I suppose its just two single quotes for ant existing single quotes.

Thanks.

Smoc

|||

Hi Kent,

Thanks for the response but that will not work if the column does not exist in the table which is the reason I want to conditionally create 1 of 2 possible views. In the simplistic example, I want to handle the situation when the column 'city' may not be in the authors table.

I realise that i could use the col_length function instead to achieve the result you have proposed. I was just wondering why I could have two 'clean' view definitions in a script contained within an IF statement.

Regards,

Smoc

|||

Just thinking out loud really, but could you programatically add the City column to the authors table if the column doesn't exist? That way, going forward, you'd only have one version of the View to maintain.

Chris

|||

Hi Chris,

We have an application that is using a database that we have no control over and no authority to change. We have discovered some differences between schemas of different clients who have this database. The differences are not critical and we hope to handle it at the view level. Other than that we would do as you suggested.

I'm just supprised that I can do a DROP command but not a Create View command in an IF statement.

Smoc

|||

You can't create view/procedure/function/trigger inside or mid of your batch.

These create scripts should be the first line of the batch.

In IF batch you can put only the Drop view/procedure/function/trigger.

The only possible way is using dynmaic sql.

|||

Thanks for the clarification.

I will probably use the dynamic sql that you have suggested and as was also suggested in an earlier thread.

Thanks.

|||There is a neat trick to achieve just what you want

. Check out this example:

-- If column doesn't exists, does not create the view that use it
IF col_length('authors','city') IS NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off
go
-- If column exists, does not create the view without it
IF col_length('authors','city') IS not NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off

You only need to carefully choose your conditions because they have to be "reversed", in a way. Still, it is a proven and reliable approach.

Conditional visibility

I have a table with US dollar amounts. The user wants to see these
amounts by default, but would like the ability to view their original
currency amounts.
If the original currency amount = the US dollar amount, I don't want
the user to be able to make that row viewable. How can I prevent them
from even seeing the plus sign that would indicate additional data?
Thanks,
MikeWhile , as you know, you can make a row or column conditionally visible with
an expression on the visibility property. But the Toggle column can not be
turned off or on, just shown or hiddent...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1116612976.587559.300370@.f14g2000cwb.googlegroups.com...
>I have a table with US dollar amounts. The user wants to see these
> amounts by default, but would like the ability to view their original
> currency amounts.
> If the original currency amount = the US dollar amount, I don't want
> the user to be able to make that row viewable. How can I prevent them
> from even seeing the plus sign that would indicate additional data?
> Thanks,
> Mike
>

Monday, March 19, 2012

Conditional Views

I have a view which has to be versatile enough to perform different calculations. For instance, using the view below I may want the stock level for that particular wine or in another instance I would the need the stock level for all wines that share the same type. Note that I would never need a combonation of both in the same view.

Is there anyway to specify <myVar> before or when calling this view?

CREATE VIEW vwWines
AS

SELECT
tblWine.ID,
tblWine.WineTypeID,
StockQty =
CASE
WHEN <myVar> = 1
THEN tblWine.StockQty
ELSE
(
SELECT
SUM(StockQty)
FROM
tblWine AS tblWineTwo
WHERE
tblWine.WineTypeID = tblWineTwo.WineTypeID
)
END
FROM
tblWine
Note: This view has been simplified and been done on the fly so may contain errorsdo this as a stored procedure and pass the variable in from the application.|||Why? Why not have two views and let the front end decide which one to call, based on the need of the operator? Follow the KISS principle.|||Keep It Simple Stupid? It took me a minute to remember that one.

I do not know. Maybe having one peice of code to maintain instead of 2. It does not really matter. Either way is valid.|||Exactly, I had thought of that but there is other C# code that are executed on the results and to duplicate it would cause more work in the long run.

I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view. It automatically produces the stored procedures and connection code. I wouldn't want to do it this way as I'd have to provide other means of connecting to the database which again isn't good for maintainence.

I'm going to experiment a little more but if I can't crack it, I'm going to go with the method of creating two view just to keep the code cleaner.|||I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view.Dear Lord...please tell me you aren't using NHibernate.
Tools such as this are a bad idea. They inevitably lead to ineffecient code, unscalable applications, and insecure databases.|||Lol, I'm using something called MyGeneration dOOdads (http://www.mygenerationsoftware.com/portal/dOOdads/Overview/tabid/63/Default.aspx) and it seems to have worked find over the past two years over various projects. Obviously its not flexible enough to cover problems like these but for what it provides, it's definitely worth the trade off. It's the #1 downloaded .NET tool on Download.com, apparently.|||"An Amazing 48k Architecture that Supports the 1.1 and 2.0 .NET Framework
Transactions, Dynamic Queries, and a Highly Intuitive API"
Dynamic Queries are to be AVOIDED. Tools such as this violate the most basic principles of database application design. They do so in the name of short-term development gains, and at the expense of long-term quality.|||But surely you would require the use of dynamic queries even without the help of an ORM tool, for example, an advanced search form. Or am I thinking of the wrong sort of dynamic queries?|||No, you would not need dynamic queries for an automated search form. And if dynamic sql is required it should be constructed with a stored procedure, not by an interface or middle-tier, which should not even have access to the underlying tables.

Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!

Sunday, March 11, 2012

Conditional select in view

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointerAs you have posted a question in the SQL server Article section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by VirDesi

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointer

try:

select dmart_db_pointer.dbname, cnt_a, cnt_b from
dmart_db_pointer left join
(select 'dmart_a' as dbname, count(*) as cnt _afrom dmart_a.upload.person_data) dmart_a on dmart_a.dbname = dmart_db_pointer.dbname
left join (select 'dmart_b' as dbname, count(*) as cnt_b from dmart_b.upload.person_data) dmart_b on dmart_b.dbname = dmart_db_pointer.dbname

Wednesday, March 7, 2012

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

 =iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

 =iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

>L<

|||Thanks, that worked!|||

hi,

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Saturday, February 25, 2012

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password
,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional Expression - i.e., IIF in Access

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);

But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END|||Originally posted by Brett Kaiser
SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END

Well, when I do that, I get
"The Query Designer does not support the CASE SQL construct."

Can you even use CASE in a view?|||Also, I need to assign an alias to that column.|||What are you using?

Aren't you using query analyzer?

If you're using Access you may need to make it a PASS THRU query

SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol|||I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help|||What do you mean in VIEW?

Are you doing this in Enterprise Manager?

I would recommend against that.|||Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql ;)|||What s/he said...

Use QA though for SQL Server development...

You'll have a lot less headaches...

Tuesday, February 14, 2012

Conception of my cube

Hello,
During my internship, I had to create a cube for sales based on only one dB (of an ERP).
So I've created a view grouping information I need.
So I've only one "table" (a view actually) which is my fact table and my only dimension table.
Actually, the dimension are the attributes of my sole dimesion.

The problem is that I've to write my report and I don't know if I've made the good choice. I can also create a dimension table with Customer, for countries ... but the result would be the same.

Thanks in advance for your advices.

Hey there,

If you are happy with the performance, the SQL is maintainable and you will only ever have one cube for your company, then your solution will be adequate.

However, you may find that you run into performance, scalability and maintenance issues as your data grows and if you need to start working with other business processes (e.g. order processing or invoicing).

By having a single view you are bypassing some of the automated work a cube / Analysis Services can give you. Also with the view, you may end up replicating some logic for bringing back dimension values (e.g. country). If you were to have another view for a new cube, you would have to replicate all the SQL for extracting the country information to return in your flattened view.

By using dedicated dimension tables, you will gain re-use of dimension content (conformed dimensions), requiring only a single extract from the source system.

There are numerous other benefits of having a more structured data warehouse/data extract process, such as change tracking, referential integrity checking, introduction of surrogate keys (non source system dependent keys if you are dealing with multiple data sources).

Hope that made sense,

Jonathon

Concept for View

Hi,
Could someone please explain to me when the view is updatable and when it
is not? I am under the impression that if the two tables are joined togethe
r
in the view, then the view is not updatable... is it correct?
ThanksSee the CREATE VIEW in the Books Online <tsqlref.chm::/ts_create2_30hj.htm>
for a detailed description of when a view is updatable.
For an updatable view containing joins, INSERT/UPDATE/DELETE statements are
allowed as long as only one underlying base table is affected. For example:
CREATE TABLE Table1
(
Col1a int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Col1b int NOT NULL
)
GO
CREATE TABLE Table2
(
Col2a int NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
Col2b int NOT NULL
)
GO
CREATE VIEW MyView
AS
SELECT Col1a, Col1b, Col2a, Col2b
FROM Table1 AS t1
JOIN Table2 AS t2 ON
t2.Col2a = t1.Col1a
GO
--succeeds
UPDATE MyView
SET Col1b = 1
GO
--fails
UPDATE MyView
SET Col1b = 1, Col2b = 1
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:864C5D55-3A5D-4F97-ABF1-9DAE7B057B33@.microsoft.com...
> Hi,
> Could someone please explain to me when the view is updatable and when it
> is not? I am under the impression that if the two tables are joined
> together
> in the view, then the view is not updatable... is it correct?
> Thanks

Sunday, February 12, 2012

Concatinate without Nulls

I am building a view and I want to return a combined field of all that make
up a full address (Address1, Address2, City, State and ZipCode). If any of
the fields are Null it returns a null. Do I have to use something like
COALESCE on each one as any of them can be null. Thanks.
DavidUse Isnull(field_name, '') to return an empty string from a null.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:#63iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> I am building a view and I want to return a combined field of all that
make
> up a full address (Address1, Address2, City, State and ZipCode). If any
of
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>|||>> Do I have to use something like COALESCE on each one as any of them can
That is one simple and recommended way to avoid NULLs being returned.
Anith|||You can also use ISNULL.
Example:
select isnull(lastname + ', ', '') + isnull(firstname)
from employees
go
AMB
"David C" wrote:

> I am building a view and I want to return a combined field of all that mak
e
> up a full address (Address1, Address2, City, State and ZipCode). If any o
f
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>
>|||You could also investigate the session option
CONCAT_NULL_YIELDS_NULL
Most client tools set this value to ON to give you the behavior you're
seeing. But if want nulls to be treated as empty strings during
concatenation operations, you can
SET CONCAT_NULL_YIELDS_NULL OFF
The setting (as with all SET options) only applies to the current
connection, or, if you set it in a stored procedure, it applies to that
procedure.
Changing this option will also invalidate the use of indexed views or
indexes on computed columns.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%2363iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>I am building a view and I want to return a combined field of all that make
>up a full address (Address1, Address2, City, State and ZipCode). If any of
>the fields are Null it returns a null. Do I have to use something like
>COALESCE on each one as any of them can be null. Thanks.
> David
>

Concatenation of two columns

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.This is only a guess, but:SELECT RTrim(first_name) + ',' + last_name
FROM Address-PatP|||Thank you for the prompt reply, it worked!

Friday, February 10, 2012

Concatenating Fields - Null Problem

I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
one of the fields is null, the whole value comes out at null. Can anyone
give me a hint on how to still show fld1 and fld2 if fld3 is null?
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemPut coalesce or ISNULL around your fields
select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as BigField
from table
this is for character data
for ints use this
select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
from table
http://sqlservercode.blogspot.com/
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||SELECT COALESCE(col1,'')+COALESCE(col2,'')+COAL
ESCE(col3,'') FROM whatever
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||The IsNull() function returns an alternate value when the supplied value is
NULL.
isnull(fld1,'') + isnull(fld2,'') + isnull(fld3,'')
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Hi,
Try using IsNull function.
Example: IsNull(fld1,'Null')
If fld1 is null, it will be replaced with string 'Null'.
--
*** Sent via Developersdex http://www.examnotes.net ***|||hi "chuckdfoster",
hope this helps
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All
expressions must be of the same type or must be implicitly convertible to th
e
same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
..
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
Examples
In this example, the wages table is shown to include three columns with
information about an employee's yearly wage: hourly_wage, salary, and
commission. However, an employee receives only one type of pay. To determine
the total amount paid to all employees, use the COALESCE function to receive
only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
--
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000
(12 row(s) affected)
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Try
SELECT
ISNULL(fld1,'') + ISNULL(fld2,'')+ISNULL(fld3,'')
FROM YourTable
You can also use the COALESCE function instead of ISNULL.
If you are interested in the differences, have a look at
http://toponewithties.blogspot.com/...es.blogspot.com
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Thanks,
That worked perfect. I knew there had to be an easy way.
Thanks,
Chuck Foster
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:C2F6C12E-56AC-4C5C-9E4D-2AF873081BBD@.microsoft.com...
> Put coalesce or ISNULL around your fields
> select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as
> BigField
> from table
> this is for character data
> for ints use this
> select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
> from table
>
> http://sqlservercode.blogspot.com/
> "chuckdfoster" wrote:
>

Tuesday, March 20, 2012

Conditionally extraction of data from a view

Conditionally extraction of data from a view

Conditionally CREATE a VIEW in a script

Conditional visibility

Monday, March 19, 2012

Conditional Views

Conditional statements in Views

Sunday, March 11, 2012

Conditional select in view

Wednesday, March 7, 2012

Conditional formatting based on second dataset

Conditional formatting based on second dataset

Saturday, February 25, 2012

Conditional expressions - isnull(A) OR isnull(B)

Conditional expressions - isnull(A) OR isnull(B)

Conditional Expression - i.e., IIF in Access

Tuesday, February 14, 2012

Conception of my cube

Concept for View

Sunday, February 12, 2012

Concatinate without Nulls

Concatenation of two columns

Friday, February 10, 2012

Concatenating Fields - Null Problem

Concatenating fields

Blog Archive

About Me