Hello All;
I was wondering if there is a way to make decisions on a particular cell in a
report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
(SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
can be expanded on Cell's content so as to get the Effect of CASE ot IF
ElseIF Else as follows:
If (SOMTHING) Then (DoSomething)
(ElseIF DOSomethingElse1)
(ElseIF DoSomethingElse2)
Else
(DoOtherwise)
Thank you.
--
Message posted via http://www.sqlmonster.comThe best way is to write some code and call it.
In Report properties go to the code page and enter a VisualBasic .Net
function eg:
Function SetColour(ByVal N As Double) As String
Select N
Case 0
Return "White"
Case >0
Return "Blue"
Case Else
Return "Red"
End Select
End Function
then call the function in your expression to set the colour of a cell eg:
=Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
"James Woo via SQLMonster.com" wrote:
> Hello All;
> I was wondering if there is a way to make decisions on a particular cell in a
> report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> can be expanded on Cell's content so as to get the Effect of CASE ot IF
> ElseIF Else as follows:
> If (SOMTHING) Then (DoSomething)
> (ElseIF DOSomethingElse1)
> (ElseIF DoSomethingElse2)
> Else
> (DoOtherwise)
> Thank you.
> --
> Message posted via http://www.sqlmonster.com
>|||Mary .. Thank you :), very nice code.
--
Message posted via http://www.sqlmonster.com|||Based on the information below I have been able to create code, however I
need to test for a value between 2 numbers..ex
Case < 366
Return "1 yr or less"
Case >0 and <731
Return "1-2 years"
I am running into an issue with line 3. Is it possible? Am I using the
wrong syntax? If this is not the best approach what would that be?
Thank you for any guidance.
"Mary Bray [SQL Server MVP]" wrote:
> The best way is to write some code and call it.
> In Report properties go to the code page and enter a VisualBasic .Net
> function eg:
> Function SetColour(ByVal N As Double) As String
> Select N
> Case 0
> Return "White"
> Case >0
> Return "Blue"
> Case Else
> Return "Red"
> End Select
> End Function
> then call the function in your expression to set the colour of a cell eg:
> =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> "James Woo via SQLMonster.com" wrote:
> > Hello All;
> >
> > I was wondering if there is a way to make decisions on a particular cell in a
> > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > ElseIF Else as follows:
> > If (SOMTHING) Then (DoSomething)
> > (ElseIF DOSomethingElse1)
> > (ElseIF DoSomethingElse2)
> > Else
> > (DoOtherwise)
> > Thank you.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||I was able to find some information, this may helps others...
Case < 366
Return "1 yr or less"
Case 0 To 731
Return "1-2 years"
"DigitalVixen" wrote:
> Based on the information below I have been able to create code, however I
> need to test for a value between 2 numbers..ex
> Case < 366
> Return "1 yr or less"
> Case >0 and <731
> Return "1-2 years"
> I am running into an issue with line 3. Is it possible? Am I using the
> wrong syntax? If this is not the best approach what would that be?
> Thank you for any guidance.
> "Mary Bray [SQL Server MVP]" wrote:
> > The best way is to write some code and call it.
> > In Report properties go to the code page and enter a VisualBasic .Net
> > function eg:
> > Function SetColour(ByVal N As Double) As String
> > Select N
> > Case 0
> > Return "White"
> > Case >0
> > Return "Blue"
> > Case Else
> > Return "Red"
> > End Select
> > End Function
> >
> > then call the function in your expression to set the colour of a cell eg:
> >
> > =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> >
> > "James Woo via SQLMonster.com" wrote:
> >
> > > Hello All;
> > >
> > > I was wondering if there is a way to make decisions on a particular cell in a
> > > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > > ElseIF Else as follows:
> > > If (SOMTHING) Then (DoSomething)
> > > (ElseIF DOSomethingElse1)
> > > (ElseIF DoSomethingElse2)
> > > Else
> > > (DoOtherwise)
> > > Thank you.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Monday, March 26, 2012
Proper syntax for bulk insert?
Hi,
I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.
How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?
Here is what I'm doing so far:
CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO
Thanks for any help,SELECT * INTO myDB..Table FROM oneDB..Table|||Thanks Brett,
Now it's giving me an error that says 'object Individual is already in the database.'
Have I placed the select * in the right place?
CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)
as
select * into individual from FROMGDPersonnelByDivision
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO
Thanks,|||insert into db1..Individual (fields)
select fields
from db2..Individual
This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?|||Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...|||insert into db1..Individual (fields)
select fields
from db2..Individual
This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?
fields...oye...|||OK, OK. I am being lazy. I admit it. ;-)
Still, it is slightly better than
insert into table
select *
from other_table|||Thanks guys, I DO need all the help I can get...
MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.
At this point, I don't mind slow, I just want to see it work. So now I've got:
CREATE Procedure Insert_From_Personnel
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
select emp_num,frst_name,last_name,[division id]
from FROMGDPersonnelByDivision
GO
I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?
Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.
One last thing... how can I say if it DOESN'T exist INSERT in the SP?
Thanks for your help!|||if not exists (select...)|||I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision
What the heck am I missing?!
CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name,last_name,[division id])
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
from FROMGDPersonnelByDivision
GO
Can I have one more hint, please?|||Yeah, I agree, the combination is finite and you're close ;)
CREATE Procedure Insert_From_Personnel
as
if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
else
print 'Record already exists'
return (0)
GO|||rdjabarov,
Help me out here. Why isn't this working?
When I use your example (simplified):
CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
GO
It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.
After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.
Thanks for any help!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.
-PatP|||Hmmmm... OK,
The select is there. It makes sense in english;
if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table
>> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)
Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?
Thanks,|||How about:INSERT into individual (
IndividualID, FirstName, LastName
, DivisionID
) SELECT
emp_num, frst_name, last_name
, [division id]
FROM FROMGDPersonnelByDivision
WHERE NOT EXSISTS (SELECT *
FROM individual AS b
WHERE b.InidividualID = FROMGDPersonnelByDivision.emp_num)-PatP|||Pat! Pat! It worked!
I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.
Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)
I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.
How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?
Here is what I'm doing so far:
CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO
Thanks for any help,SELECT * INTO myDB..Table FROM oneDB..Table|||Thanks Brett,
Now it's giving me an error that says 'object Individual is already in the database.'
Have I placed the select * in the right place?
CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)
as
select * into individual from FROMGDPersonnelByDivision
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO
Thanks,|||insert into db1..Individual (fields)
select fields
from db2..Individual
This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?|||Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...|||insert into db1..Individual (fields)
select fields
from db2..Individual
This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?
fields...oye...|||OK, OK. I am being lazy. I admit it. ;-)
Still, it is slightly better than
insert into table
select *
from other_table|||Thanks guys, I DO need all the help I can get...
MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.
At this point, I don't mind slow, I just want to see it work. So now I've got:
CREATE Procedure Insert_From_Personnel
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
select emp_num,frst_name,last_name,[division id]
from FROMGDPersonnelByDivision
GO
I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?
Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.
One last thing... how can I say if it DOESN'T exist INSERT in the SP?
Thanks for your help!|||if not exists (select...)|||I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision
What the heck am I missing?!
CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name,last_name,[division id])
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
from FROMGDPersonnelByDivision
GO
Can I have one more hint, please?|||Yeah, I agree, the combination is finite and you're close ;)
CREATE Procedure Insert_From_Personnel
as
if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
else
print 'Record already exists'
return (0)
GO|||rdjabarov,
Help me out here. Why isn't this working?
When I use your example (simplified):
CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
GO
It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.
After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.
Thanks for any help!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.
-PatP|||Hmmmm... OK,
The select is there. It makes sense in english;
if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table
>> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)
Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?
Thanks,|||How about:INSERT into individual (
IndividualID, FirstName, LastName
, DivisionID
) SELECT
emp_num, frst_name, last_name
, [division id]
FROM FROMGDPersonnelByDivision
WHERE NOT EXSISTS (SELECT *
FROM individual AS b
WHERE b.InidividualID = FROMGDPersonnelByDivision.emp_num)-PatP|||Pat! Pat! It worked!
I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.
Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)
Friday, March 23, 2012
Proper Case Syntax on Name Column
Hello,
I have a Column called Name varchar(50). Data was imported from an excel
spreadsheet in all caps. How can proper case all fields in this column that
is in CAPS?The LOWER function (see BOL) can be used in an UPDATE statement to change
the contents of this field to lower case.
HTH
J
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column
> that
> is in CAPS?|||If you mean literally "Proper" case (betty sue changed to Betty Sue), you ha
ve
to roll your own using the Upper and Lower functions. Here's one I dug with
a
quick Google search:
[url]http://www.novicksoftware.com/UDFofW
/Vol1/T-SQL-UDF-Volume-1-Number-47-proper-c
ase.htm[/url]
Thomas
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column th
at
> is in CAPS?|||And whatever solution you find, you have to realize that there will still be
errors.
My last name can be spelled D'anjou d'Anjou or D'Anjou.
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23Yh2NxRbFHA.2996@.TK2MSFTNGP10.phx.gbl...
> If you mean literally "Proper" case (betty sue changed to Betty Sue), you
> have to roll your own using the Upper and Lower functions. Here's one I
> dug with a quick Google search:
> [url]http://www.novicksoftware.com/UDFofW/Vol1/T-SQL-UDF-Volume-1-Number-47-proper
-case.htm[/url]
>
> Thomas
>
>
> "Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
> news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
>
I have a Column called Name varchar(50). Data was imported from an excel
spreadsheet in all caps. How can proper case all fields in this column that
is in CAPS?The LOWER function (see BOL) can be used in an UPDATE statement to change
the contents of this field to lower case.
HTH
J
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column
> that
> is in CAPS?|||If you mean literally "Proper" case (betty sue changed to Betty Sue), you ha
ve
to roll your own using the Upper and Lower functions. Here's one I dug with
a
quick Google search:
[url]http://www.novicksoftware.com/UDFofW
/Vol1/T-SQL-UDF-Volume-1-Number-47-proper-case.htm[/url]
Thomas
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
> Hello,
> I have a Column called Name varchar(50). Data was imported from an excel
> spreadsheet in all caps. How can proper case all fields in this column th
at
> is in CAPS?|||And whatever solution you find, you have to realize that there will still be
errors.
My last name can be spelled D'anjou d'Anjou or D'Anjou.
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23Yh2NxRbFHA.2996@.TK2MSFTNGP10.phx.gbl...
> If you mean literally "Proper" case (betty sue changed to Betty Sue), you
> have to roll your own using the Upper and Lower functions. Here's one I
> dug with a quick Google search:
> [url]http://www.novicksoftware.com/UDFofW
/Vol1/T-SQL-UDF-Volume-1-Number-47-proper-case.htm[/url]
>
> Thomas
>
>
> "Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
> news:36B0E293-69CC-4378-95CC-1557753BA424@.microsoft.com...
>
Saturday, February 25, 2012
Programatically Verifying SQL Syntax
OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"
and transforms it into something that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"
My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?
Any help would be appreciated! Cheers.No, you have to sit down and start programming.
Having a parser generator available helps, if you find a SQL syntax as LALR.|||I was afraid you would say that.
Subscribe to:
Posts (Atom)