Database

Re: command to find specific value in select

21-Jul-09 2:08

Use substring to get the characters, and charindex to find the :

declare @a varchar(20)
set @a = 'Telec:144'
select substring(@a,1,charindex(':',@a)-1),substring(@a,charindex(':',@a)+1,len(@a))

will return Telec for the first 1 and 144 for the 2nd

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

dabuskol21-Jul-09 2:20

dabuskol

21-Jul-09 2:20

You are correct... Thanks for the immediate reply you save me on my last minute.

Thanks...

Dabsukol

Re: command to find specific value in select

Ashfield21-Jul-09 4:48

Link Directory Submitter (3016)

21-Jul-09 4:48

No problem

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

maliksp20-Jul-09 23:47

maliksp

20-Jul-09 23:47

Link Directory Submitter is the perfect solution for sending your sites to a multitude of pre-configured online directories. Link Directory Submitter comes with 2700+ link directories to submit to, more than half ranging from PR4 to PR7. Visit us at: http://www.clicknearn.net/3016-53.html

Re: Link Directory Submitter (3016)

Ashfield21-Jul-09 2:11

Re: Stored Procedure logic

21-Jul-09 2:11

If you want to advertise, try paying for it - and if your product was that good you would be found straight away by anyone googling.

And its a rubbish web site, full of spelling and grammatical errors anyway.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Stored Procedure logic

LucBite20-Jul-09 23:40

LucBite

20-Jul-09 23:40

I am using the following stored procedure to insert into two tables and it works fine

ALTER PROCEDURE createEmployee(@loginId VARCHAR (20), @username VARCHAR (25), @passwrd VARCHAR (25),@empId VARCHAR (20),@title varchar(5),@branchNo VARCHAR(20),@Fname VARCHAR(50),@idNo VARCHAR(13),@physicaladdress varchar(50),@province VARCHAR(50),@city VARCHAR(50),@telephone varchar(15),@cellNo VARCHAR(15),@Email VARCHAR(30),@employeetype varchar(15),@salary NUMERIC(11,2))
AS
INSERT INTO LoginInfo VALUES(@loginId,@username,@passwrd)
--SET @loginId=@@IDENTITY
INSERT INTO EMPLOYEE VALUES(@empId,@loginId,@title,@branchNo,@Fname,@idNo,@physicaladdress,@province,@city,@telephone,@cellNo,@Email,@employeetype,@salary);

But Note the @branchNo parameter to be inserted into the EMPLOYEE table. I need to get it from a table named BRANCH based on some criteria. Currently I have hard coded its(@branch parameter)its value.I've tried to use the column level Select subquery but it is not allowed. please give a junior developer some logic

ML Lingwati

J4amieC21-Jul-09 0:10

J4amieC

21-Jul-09 0:10

How about:

INSERT INTO EMPLOYEE 
SELECT
 @empId,
 @loginId,
 @title,
 branchNo,
 @Fname,
 @idNo,
 @physicaladdress,
 @province,
 @city,
 @telephone,
 @cellNo,
 @Email,
 @employeetype,
 @salary
FROM BRANCH
WHERE someCriteria

In case thats a bit confusing, you can pull the branchNo from another table while still using all your variable for the other columns to be inserted.

Avoining duplicates while joining tables

nainakarri20-Jul-09 23:19

nainakarri

20-Jul-09 23:19

Hi

I have 2 tables, one is with primary key and the other is foreign key. There is one to many relation between the two tables.

i have written query by joining them as below.

But as the 2nd table has more than 1 row, duplicates are coming in the output.

Hence i want to query in such a way that no duplicates should come. Is there any way for this?

Is it possible to write as below....

select table1.col1, (loop through all the rows in table2 and form a srting of the values) as col2 from table1

Thanks in Advance

Regards
Naina

Blue_Boy20-Jul-09 23:31

Blue_Boy

20-Jul-09 23:31

You can fix by using distinct or grouping.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

nainakarri21-Jul-09 2:02

nainakarri

21-Jul-09 2:02

No i couldnt do so.....it is not fixed. Still there are duplicates coming out...

Ashfield21-Jul-09 2:14

21-Jul-09 2:14

What you are trying to do is called pivoting. If you google for that you wll find loads of examples.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

nainakarri21-Jul-09 2:35

nainakarri

21-Jul-09 2:35

I dont know about pivot in sqlserver 2005. Thanks for giving me clue. I will check for that.

Regards
Naina

Ashfield21-Jul-09 4:43

Approach for CRUD transactions of a dynamic form

21-Jul-09 4:43

Pleased to help.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Member 288307020-Jul-09 23:04

Member 2883070

20-Jul-09 23:04

I am creating a C# web application to create dynamic forms. The user can define the form, generate its layout. This form would be availble for transaction.

I am having a query in saving the transaction details of the dynamic form. Can we build seperate tables for each form or can we use the same table to store data of all the forms. Which would be the effecient way?

The DB that we are using is Oracle 10g.

Thanks in advance.

Insert Query

sachees12320-Jul-09 22:07

sachees123

20-Jul-09 22:07

Friends,

I stored my 200 values in an array and that 200 values represent one record.

It is easy to insert 'n' record, that i did N no. of times with MAX of 20 values but this is first time im supposed to insert this large no. of values.

So donno how to do...

HELP!!!

Re: Insert Query

DoctorMick21-Jul-09 0:23

DoctorMick

21-Jul-09 0:23

I'd look at your database design and see if a table with 200 columns is really necessary. If it is, you can create an SP with 200 parameters or generate a massive insert statement (both of which are not good solutions!)

Re: Insert Query

Ashfield21-Jul-09 2:01

21-Jul-09 2:01

You could pass the values into the stored proc as xml and work from that.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Insert Query

PIEBALDconsult21-Jul-09 5:18

SQL server 2005 Reporting Services

21-Jul-09 5:18

I prefer a method that will handle generating the query and parameters automatically, that isn't hard-coded to any particular table.

Aman786Singh20-Jul-09 21:10

Aman786Singh

20-Jul-09 21:10

Hi All,

I am new to reporting services in SQL Server 2005. I have been able to create a report using Microsoft's SQL Server Bussiness Intelligent Development IDE.
The problem is when I access the report from a web browser it asks me for Reposting server's username and password. I want to share these reports across my LAN creating limited users who can just view the reports and do nothing else.

Please suggest how to go about it.
Further more in my IIS when I go and browse the Report$Machinename it sometime does not load properly with all the components it should show. It behave correctly only randomly.

Regards,
Sandeep

UPSERT implementation

PIEBALDconsult20-Jul-09 10:36

Re: UPSERT implementation

20-Jul-09 10:36

I was looking for an implementation of "UPSERT" that I could execute with one call to ExecuteNonQuery against SQL Server 2000*. Not being familiar with EXISTS and HAVING, but being very familiar with JOIN, I wrote the following:

INSERT INTO MyTable ( Id )
SELECT T1.Id
FROM ( SELECT Id = @Id ) T1
LEFT OUTER JOIN MyTable T2
ON T1.Id = T2.Id
WHERE T2.Id IS NULL
;
UPDATE MyTable
SET Field1 = @Field1
    ...
WHERE Id = @Id

It works, and, for the particular task, I'm not concerned about scalability**, but I thought I'd post here to get feedback on whether or not there are commonly-used implementations which are held to be superior.

* I try to be database agnostic, so I prefer solutions that don't rely on a particular database system.

** This particular table has only the Id and one other field, and there will be fewer than 1000 records. However, the general case should be considered.

--CELKO--20-Jul-09 14:28

--CELKO--

20-Jul-09 14:28

DB2, Oracle and SQL Server all support the MERGE statement now and it is ANSI Standardm That is pretty agnostic Smile | :)

Re: UPSERT implementation

David Skelly21-Jul-09 2:19

David Skelly

21-Jul-09 2:19

I thought MERGE was new in SQL Server 2008? In which case, it won't work with older versions of SQL Server. The OP specifically says he is using SQL Server 2000.

Re: UPSERT implementation

PIEBALDconsult21-Jul-09 5:07

Is there an SQL Formatter based on Celko's SQL PROGRAMMING STYLE?

21-Jul-09 5:07

Right, I use a bunch of different databases, rarely the latest and greatest. Sigh | :sigh:

--CELKO--20-Jul-09 7:27

--CELKO--

20-Jul-09 7:27

Is there an SQL Formatter based on Celko's SQL PROGRAMMING STYLE? Someone has probably written something that can follow my rules, but I have not seen it yet.

Re: Is there an SQL Formatter based on Celko's SQL PROGRAMMING STYLE?

PIEBALDconsult20-Jul-09 10:38