Database

Jörgen Andersson29-Oct-09 23:45

Jörgen Andersson

29-Oct-09 23:45

The (+) makes it an outer join.

Jörgen Andersson29-Oct-09 23:05

Jörgen Andersson

29-Oct-09 23:05

M Riaz Bashir wrote:
Kindly let me know how may I convert following Oracle sql into MS-Access ?

Why, would anyone ever move from oracle to access?
Anyway, here goes.

SELECT A.SNO, A.CODE, A.QTY, A.RATE, B.NAME
FROM DETAIL A RIGHT JOIN MASTER B
WHERE A.CODE=B.CODE

David Skelly30-Oct-09 3:08

David Skelly

30-Oct-09 3:08

I think it's a left join, not a right join. I'm not an Oracle expert but I have a feeling the (+) goes on the side of the join which is not required (not sure of the technical term for that). In other words

a.code = b.code(+)

is a left outer join and

a.code(+) = b.code

is a right outer join.

I'm not 100% sure about that so don't take my word for it.

Jörgen Andersson30-Oct-09 3:25

Jörgen Andersson

30-Oct-09 3:25

Damn!

I need a coffee. Or actually rather a beer. Or both.

Problem with Execute query by EXEC(@string)

Chris Meech30-Oct-09 9:54

Chris Meech

30-Oct-09 9:54

a.code = b.code(+)

The (+) says any a.code value will match a NULL b.code value. Meaning you get all the a.code values.

Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

nainakarri29-Oct-09 1:31

29-Oct-09 1:31

Hi
I have written a stored procedure where i am taking the select query into a variable @string and executing the query.

i am generating the query dynamically based on the SP inputs. If the query is below 4000 chars , there is no prob with it. If it exceeds then query is not executing. Kindly help....

Thanks in advance
Naina

Naina

Covean29-Oct-09 1:52

Covean

29-Oct-09 1:52

If I'm right you will not be able to query/search for data/text that is larger than 4000 chars.
All column types above text(4000), like NVarChar(max) can't be a query parameter.

I hope somebody can confirm that.

Greetings
Covean

nainakarri29-Oct-09 2:02

29-Oct-09 2:02

Hi thanks for the reply..... just did a small change by declaring as varchar(MAX) and its accepting more than 4000. Thank you

Regards
Naina

Naina

Covean29-Oct-09 2:04

Covean

29-Oct-09 2:04

No problem and thank you for confirming that I'm wrong.
And good to know. Laugh | :laugh:

Greetings
Covean

Mycroft Holmes29-Oct-09 2:06

29-Oct-09 2:06

You may be limited to 8000 character, the max of varchar, not sure if varchar(max) overcomes this limitation.

Never underestimate the power of human stupidity
RAH

nainakarri29-Oct-09 2:18

29-Oct-09 2:18

if i give nvarchar(8000) it says that i have crosses the linit of 4000 characters. i am using sqlserver 2005. and now i noticed that varchar(MAX) is taking only 4000 characters.

Regards
Naina

Naina

Mycroft Holmes29-Oct-09 2:24

29-Oct-09 2:24

use varchar(8000)

Never underestimate the power of human stupidity
RAH

nainakarri29-Oct-09 2:35

29-Oct-09 2:35

i declared as varchar(8000) and assigned a query to it.

but when i tried to print the length and string, it shows only till 4000 chars and the length also its showing only 4000. Frown | :(

Regards
Naina

Naina

soni uma29-Oct-09 2:44

soni uma

29-Oct-09 2:44

Hi

use nvarchar(8000)

umesh

nainakarri29-Oct-09 2:47

29-Oct-09 2:47

The size (8000) given to the parameter '@sql' exceeds the maximum allowed (4000). is the error if i use nvarchar(8000) Frown | :(

Regards
Naina

Naina

Abhishek Sur29-Oct-09 11:58

Abhishek Sur

29-Oct-09 11:58

Yes... Hightest value of NVARCHAR is 4000 and VARCHAR is 8000

BUT NVARCHAR takes large CHARSET.. So use NVARCHAR only when you need. Smile | :)

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Microsoft Bing MAP using Javascript
CLR objects in SQL Server 2005
Uncommon C# Keywords/xml>

nainakarri29-Oct-09 18:34

29-Oct-09 18:34

I finally got the answere..... Smile | :)

I declared @string as varchar(8000). But there are nvarchars declared which i am assigning to @string which finally lead to the problem . because of this @string is taking only 4000 charecters, I replaced all nvarchars with varchars and finally its taking morethan 4000 charecters.

Regards
Naina

Naina

how to get week number and startday of the week in sql server 2008

NIRMAL UPADHYAY30-Oct-09 20:04

NIRMAL UPADHYAY

30-Oct-09 20:04

Take @string as varchar(max) and then try executing it
Hope it helps!

chakran28-Oct-09 23:40

Re: how to get week number and startday of the week in sql server 2008

28-Oct-09 23:40

Hi,
I am struggling to get the week number and week day of the particular day. I know we can use Datepart function which is available in sql server 2008 to display the week number of the given date. but my situation is different. for example
Datepart(wk,'2009-12-30') gives 53 weeks
Datepart(wk,'2010-01-02') gives 1. but I wanted to display it as 54.
whenever the year is changing I need to add these week number to the previous year one.

In the below table Bugdate is the input, and weeknumber and startday of the week are the expected outputs.

BugDate ----- weeknumber ---- Startday of the Week
....
12/13/2008---- 50------------- 12/7/2008
12/14/2008---- 51------------- 12/14/2008
12/21/2008---- 52------------- 12/21/2008
12/23/2008---- 52------------- 12/21/2008
12/30/2008---- 53------------- 12/28/2008
1/2/2009------- 54------------- 12/28/2008
1/6/2009------- 55------------- 1/4/2009
...
12/20/2009---- 105------------ 12/20/2009
12/28/2009---- 106------------ 12/27/2009
1/1/2010------ 106------------ 12/27/2009

Can any one help me to solve this problem.
Thanks in advance.

Mycroft Holmes29-Oct-09 0:33

Re: how to get week number and startday of the week in sql server 2008

29-Oct-09 0:33

You could do something like

Select Datepart(wk,'2010-01-02') + ((Datepart(yr,'2010-01-02') - 2009) * 52

OR get the datediff days / 7 and use floor or ceiling to get the number you want (this is more reliable as the week numbers per year do not change.

This is a fairly simple exercise of thinking around corners, you are not flexing you brain enough.

Never underestimate the power of human stupidity
RAH

chakran29-Oct-09 0:45

Re: how to get week number and startday of the week in sql server 2008

29-Oct-09 0:45

Hi,
Thanks for your reply to get the week number. But how to get the first day of the week for the given date?

Thanks,

Mycroft Holmes29-Oct-09 2:03

Re: how to get week number and startday of the week in sql server 2008

29-Oct-09 2:03

Read up on datepart in BOL, there is a dayofweek element that will tell you.

Never underestimate the power of human stupidity
RAH

chakran30-Oct-09 1:09

Re: how to get week number and startday of the week in sql server 2008

30-Oct-09 1:09

Hi,
the query is failing in one situation. eg, when dates are '2011-12-31'
'2012-01-01', this case returning same week number for both the dates but this is wrong. For '2012-01-01' this is the start of the week so it is increment of the previous date week...

Date-------- weekNumber
2011-12-31-- 157
2012-01-01-- 157(wrong result it should be 158)
2012-01-02-- 157(wrong result it should be 158)

Thanks..

Niladri_Biswas30-Oct-09 1:45

Niladri_Biswas

30-Oct-09 1:45

But as per the query which I have given it should be

Date-------- weekNumber
2011-12-31-- 212 and not 157
2012-01-01-- 213 and not 157 or 158
2012-01-02-- 213 and not 157 or 158

And it is correct only. Even I calculated the value.

Only change the case statement by the following lines

case when Datepart(yy,bugdate) = 2009 then Datepart(wk,bugdate) + 53*1
		     when Datepart(yy,bugdate) = 2010 then Datepart(wk,bugdate) + 53*2
		     when Datepart(yy,bugdate) = 2011 then Datepart(wk,bugdate) + 53*3
		     when Datepart(yy,bugdate) = 2012 then Datepart(wk,bugdate) + 53*4
		else Datepart(wk,bugdate)
		end as weeknumber

Niladri Biswas

Re: how to get week number and startday of the week in sql server 2008

chakran30-Oct-09 2:20