Database Discussion Boards

Re: ORA-00936:missing expression

Wendelius15-Jul-08 7:17

Wendelius

15-Jul-08 7:17

Try to use : instead of @. So in your example it would be like:

OracleStmt = "insert into TABLE2_HDB1 (PURPOSE) values(:PURPOSE)";
...
cmd.Parameters.Add(new OleDbParameter(":PURPOSE", OleDbType.VarChar , 20));
cmd.Parameters[":PURPOSE"].Value = textBox6.Text;

I'm not sure that if this correct when using OleDBConnection, but it works with native OracleConnection

Mika

Query Problem in Sql Server 2000

BalasubramanianK14-Jul-08 19:27

BalasubramanianK

14-Jul-08 19:27

Hi,

My Sql Server 2000 Table 'Feedback' structure is look like this.....Code, Q1, Q2, Q3 and records are like...

Code Q1 Q2 Q3
1 Good Good Average
2 Poor Average Average
3 Good Excellent Average
4 Excellent Good Good
etc.....

I need to write a single query, which will give result look like.

Excellent Good Average Poor
Q1 1 2 0 1
Q2 1 2 1 0
Q3 0 1 3 0

Actually, this table is used to get the feedback from the users. User has given their feedback by selecting options. I need the details that how many persons answered Excellent for Question1 (Q1), Question2 (Q2), etc...As well as How many answered Good for Q1,Q2,Q3, etc....I need this format. Please help me.

Balasubramanian K.

to create pivot table

prithaa14-Jul-08 19:20

prithaa

14-Jul-08 19:20

Hello,
I have written the following query in SQL Server but did not work saying error with the word PIVOT but worked with MS-Access
but I cannot create a table with the select statement.I want the Pivot Table to go in a table but the INTO [Table] doesn't work.

Following is the query

TRANSFORM Sum(Cap) AS AvgOfCap;
SELECT JobWorker FROM Cap GROUP BY JobWorker order by JobWorker PIVOT TimePeriod;

Regards
Prithaa

Re: to create pivot table

TheFM23415-Jul-08 3:07

TheFM234

15-Jul-08 3:07

What version of SQL Server are you using?

Calculate last week working days [modified]

Member 387988114-Jul-08 18:59

Member 3879881

14-Jul-08 18:59

Hi guys i want to find the last week is which saturday...,

It mean its 2nd or 4th saturday or odd saturday...,

Becoz based on that i want to calculate workdays

If its even(2nd or 4th) saturday then i want to show workdays 5

else if its odd(1st or 3rd) saturday then i have to show workdays 6 how to do that?,

Plz help me

Thanks & Regards,
NeW OnE,
please don't forget to vote on the post

modified on Tuesday, July 15, 2008 1:09 AM

Calculate last week working days [modified]

Member 387988114-Jul-08 18:59

Member 3879881

14-Jul-08 18:59

i want to find the last week is which saturday...,

It mean its 2nd or 4th saturday or odd saturday...,

Becoz based on that i want to calculate workdays

If its even(2nd or 4th) saturday then i want to show workdays 5

else if its odd(1st or 3rd) saturday then i have to show workdays 6 how to do that?,

Plz help me

Thanks & Regards,
NeW OnE,
please don't forget to vote on the post

modified on Tuesday, July 15, 2008 1:12 AM

Re: Calculate last week working days

dan!sh 14-Jul-08 19:01

dan!sh

14-Jul-08 19:01

New one wrote:
please don't forget to vote on the post

done

Re: Calculate last week working days

tsaunders15-Jul-08 14:19

tsaunders

15-Jul-08 14:19

--This will return the week of the month
select datediff(week,(getdate()-datepart(dd,getdate())+1),getdate())+1

--This will return the first day of the week
SELECT DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)

--This will return the last day of the week
SELECT DATEADD(wk, DATEDIFF(wk, 5, getdate()), 5)

--This will return the Saturday date of the week
SELECT DATEADD(wk, DATEDIFF(wk, 4, getdate()), 4)

Hope this works for you Smile | :)

Re: Calculate last week working days

tsaunders15-Jul-08 14:34

tsaunders

15-Jul-08 14:34

By the way, what are you doing if there is 5 weeks in a month

Declare @Date datetime
SET @Date='2008-07-28'
select
case
When (datediff(week,(@Date -datepart(dd,@Date)+1),@Date)+1) in (1,3) then 'Workdays 6'
When (datediff(week,(@Date -datepart(dd,@Date)+1),@Date)+1) in (2,4) then 'Workdays 5'
When (datediff(week,(@Date -datepart(dd,@Date)+1),@Date)+1)=5 then 'What Now'
End

SQL Server 2000 Desktop Edition Deployment issue.

rajawaqas14-Jul-08 17:35

rajawaqas

14-Jul-08 17:35

I have to develop an application using SQL Server2000. It is a client server application having one server and at most 10 clients. Does SQL Server 2000 Desktop Edition support Client Server Architecture and what is maximum number of clients that may connect concurrently.
I wish not to use SQL Server Enterprise Edition as it requires Server operating system for its installation but my application is targeted to run on Windows XP Professional

Looking forward for timely responce please....
Regards
Waqas

rajawaqas

How to Grouped Data weekly monthly and Quarterly

Rameez Raja14-Jul-08 16:59

Rameez Raja

14-Jul-08 16:59

Hi all,

iam facing difficulty in writting query some thing like this.

i have caseno field in Database table and want to query that how much cases submitted weekly monthly and Quarterly.

i have tried but not sucedded because i use varchar(50) field for stroing Date into table

please help me in this regard any hint ?

Regards
Rameez

Re: How to Grouped Data weekly monthly and Quarterly

Mycroft Holmes14-Jul-08 18:07

Mycroft Holmes

14-Jul-08 18:07

rameez Raja wrote:
i use varchar(50) field for stroing Date

Mistake 1 - this going to plague you until you fix the problem - do it NOW. Convert the varchar to a datetime field.

When storing the date make sure you store the DATE only not the date and time.

Use datepart to identify the date segments you require. I would create a view with the additional calculated fields and denormalised your structure for your reporting requirements.

However unless you fix mistake 1 your progress will be delayed as you try to work around the format limitations. Datetime is your friend, he has lots of nice bits to play with.

Never underestimate the power of human stupidity
RAH

Re: How to Grouped Data weekly monthly and Quarterly

Rameez Raja14-Jul-08 23:56

Rameez Raja

14-Jul-08 23:56

Thanks for replying and give me good suggestion .

i have to convert varchar to datetime field and have to store only date part into database table named Date (varchar) or i have to taken Datetime field type for storing date.

please suggest me as i get solution .

then how would be my query?

Best regards
rameez

Re: How to Grouped Data weekly monthly and Quarterly

Mycroft Holmes15-Jul-08 0:10

Mycroft Holmes

15-Jul-08 0:10

Create another column name DateT as a datetime field
Convert your existing date data into the DateT field
When storing date data only store the date (today) unless you need the time component (now).

If you store the time component then you cannot select on '2008-Jul-15' as the actual value may be '2008-Jul-15 04:23:11' with the time component. This will force you to use between or format whenever you use the DateT field.

Try this

SELECT TOP 100
DateT
DATEPART(d,datadate) [Day],
DATEPART(wk,datadate) [wk],
DATEPART(mm,datadate) [mth],
DATENAME(weekday,datadate)
FROM Tablename

Never underestimate the power of human stupidity
RAH

Re: How to Group Data weekly monthly and Quarterly

leoinfo15-Jul-08 4:33

leoinfo

15-Jul-08 4:33

This is an example of grouping data by year, quarter, month, week.

I know some of you will say *what a rubbish* Smile | :)

,
but, believe me, some people are looking at this rubbish and actually learn something.
So ... Don't say *Unhelpful Answer* Wink | ;)

Just copy and test (in SQL 2005)...

/* START - GENERATE RANDOM DATA */ ; 
CREATE TABLE #T (caseno INT IDENTITY(1,1), Date_Cr DATETIME); 
DECLARE @Cases INT; SET @Cases = 10000 ; 
WHILE @Cases > 0 
BEGIN 
  INSERT INTO #T (Date_Cr) SELECT GETDATE()-RAND()*365*4 ; 
  SET @Cases = @Cases - 1 ; 
END ; 
/* END - GENERATE RANDOM DATA */ ; 

/* START - BUILD STATISTICS */ ; 
;WITH case_stats AS ( 
    /* YEAR */
	SELECT Period = YEAR(Date_Cr) * 10000 
		 , Cases  = COUNT(*) 
	FROM #T 
	GROUP BY  
					YEAR(Date_Cr) * 10000 
 
	UNION ALL 
 
    /* QUARTER */
	SELECT Period = YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000 
		 , Cases  = COUNT(*) 
	FROM #T 
	GROUP BY  
					YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000 
 
	UNION ALL 
 
    /* MONTH */
	SELECT Period = YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000  
                    + MONTH(Date_Cr)*10 
		 , Cases  = COUNT(*) 
	FROM #T 
	GROUP BY  
					YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000  
                    + MONTH(Date_Cr)*10 
 
	UNION ALL 
 
    /* WEEK */
	SELECT Period = YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000  
                    + MONTH(Date_Cr)*10
                    + (1 + DATEPART(ww, Date_Cr) - DATEPART(ww, CONVERT(NVARCHAR(7),Date_Cr, 121)+'-01')) 
		 , Cases  = COUNT(*) 
	FROM #T 
	GROUP BY  
					YEAR(Date_Cr) * 10000 
                    + (( MONTH(Date_Cr) + 2 ) / 3)*1000  
                    + MONTH(Date_Cr)*10
                    + (1 + DATEPART(ww, Date_Cr) - DATEPART(ww, CONVERT(NVARCHAR(7),Date_Cr, 121)+'-01')) 
) 
SELECT *  
INTO #S 
FROM case_stats ; 
/* END - BUILD STATISTICS */  ; 
 
 
 
/* START - BUILD REPORTS */  ; 
 
/* STYLE 1 */ ; 
SELECT  
  Period 
, STUFF(   STUFF(   STUFF(   STUFF( Period ,1,0,'Y') ,6,0,'Q') ,8,0,'M') ,11,0,'W')  
, Cases 
FROM #S 
ORDER BY LEFT(Period,4) DESC, Period  ; 
 
/* STYLE 2 */ ; 
SELECT Period,  
	  Year    = 'Y '+CASE WHEN RIGHT(Period, 4) ='0000'                            
						THEN         LEFT(Period,4)         ELSE NULL END  
	, Quarter = 'Q '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 3)='000' 
						THEN RIGHT(  LEFT(Period,5) ,1)     ELSE NULL END  
	, Month   = 'M '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 3)<>'000' AND RIGHT(Period, 1)='0'  
						THEN RIGHT(  LEFT(Period,7) ,2)     ELSE NULL END  
	, Week    = 'W '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 1)<>'0'  
						THEN        RIGHT(Period,1)         ELSE NULL END  
	, Cases  
FROM #S  
ORDER BY LEFT(Period,4) DESC, Period ;  
 
/* END - BUILD REPORTS */ ; 
 
 
DROP TABLE #S ;   
DROP TABLE #T ;

How to set the timer for SSIS package

nagveni14-Jul-08 12:39

nagveni

14-Jul-08 12:39

Hi All,

Iam new to SQL Server. Could you please help me in writing the script to set the timer for SSIS package for every 10 seconds(say).

Thanks in advance.

Regards,
nag

Re: How to set the timer for SSIS package

Marek Grzenkowicz15-Jul-08 22:58

Marek Grzenkowicz

15-Jul-08 22:58

What do you mean?
Do you want this package to execute every 10 seconds?

Re: How to set the timer for SSIS package

nagveni16-Jul-08 10:05

nagveni

16-Jul-08 10:05

Yes. I need that package to be executed by itself for every 10 seconds.

Thanks,
Veni

Re: How to set the timer for SSIS package

Marek Grzenkowicz19-Jul-08 9:50

Marek Grzenkowicz

19-Jul-08 9:50

Maybe you could use this trick[^].

But the idea of an SSIS package executed every 10 seconds keeps bugging me. What kind of package is it? What does it do? Does it take less to 10 seconds to execute it?

convert datetime format

tai-fun14-Jul-08 11:01

tai-fun

14-Jul-08 11:01

(i can speak some engish so i hope you are understand o my writes)
using sql server 2005;

how to convertdatetime as yyyy/mm/dd hh:mm

i write a user defined function as below but that function result show as 2008-07-14 23:07:00.000.... i want result of function show as 2008-07-14 23:07 or 14-07-2008 23:07

CREATE FUNCTION [dbo].[TarihDuzenle] (@TARIH DATETIME)
RETURNS DATETIME
AS
BEGIN
SET @TARIH = CONVERT(
DATETIME, CONVERT(VARCHAR, DATEPART(YYYY,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(DD,@TARIH)) + ' '+
CONVERT(VARCHAR, DATEPART(HH,@TARIH)) + ':'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) + ':00'
)
RETURN @TARIH
END

Re: convert datetime format

Arindam Tewary14-Jul-08 22:09

Arindam Tewary

14-Jul-08 22:09

Try this

select convert(varchar,getdate(),120)

It returns date in yyyy-mm-dd hh:mi:ss(24h) format. Smile | :)

Thanks,
Arindam D Tewary

Re: convert datetime format

tai-fun15-Jul-08 1:15

tai-fun

15-Jul-08 1:15

thanks for your answer.

is possible not show second?

Re: convert datetime format

leoinfo15-Jul-08 5:35

leoinfo

15-Jul-08 5:35

select convert(varchar<big>(16)</big>,getdate(),120)

modified on Tuesday, July 15, 2008 9:41 PM

BULK INSERT and collation problem

msx2314-Jul-08 10:56

msx23

14-Jul-08 10:56

I'm trying to use BULK INSERT to import a text file and then check its contents against a table in the database. I'm getting a problem when I compare the results however.

I'm making a temp table to hold the text file's contents

CREATE TABLE #Temp(Code varchar(5), number varchar(2))

Then using BULK INSERT to get the contents in

BULK INSERT #Temp FROM 'PATH.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

And then checking if any matches occur

SELECT Code, (CASE WHEN
				(SELECT Count(DBTable.Code) From DBTableWHERE DBTable.Code= #Temp.Code) > 0	
			 THEN "Yes"
			 ELSE "No" END) AS FLAG
From #TempCCServs

The problem is that I get the following error message now:
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

Anyone have an idea what I'm doing wrong?
Thanks for any help.

Re: BULK INSERT and collation problem

Syed Mehroz Alam15-Jul-08 1:08

Syed Mehroz Alam

15-Jul-08 1:08

The two columns in the comparison differ in collation settings.

The following code applies sames collation to both sides before the comparison.
SELECT Count(DBTable.Code) From DBTable WHERE DBTable.Code collate Latin1_General_CI_AS = #Temp.Code collate Latin1_General_CI_AS

Hope that helps.

Regards,
Syed Mehroz Alam

My Articles

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.