Database

30-Dec-08 19:34

Jon_Boy wrote:
I'd probably just use an IN statement

I thought of IN in the first place, but there are two differences if you use it. It's considered as an OR structure so if every part must exists, I think it wouldn't be usable. Also if you use IN directly on the table you would get the same car repeatedly if it has several part's that are defined in in-list.

But that was based my original interpretation on the question which later was found out to be wrong.

The need to optimize rises from a bad design.My articles[^]

Query Help

Saiyed Alam24-Dec-08 21:13

Saiyed Alam

24-Dec-08 21:13

Hello,
How can I escape new line or "\n" from a string in select query?
Thanking in Advance
Johnny

Re: Query Help

Wendelius25-Dec-08 7:13

25-Dec-08 7:13

You can use CHAR[^] function with REPLACE[^].

The need to optimize rises from a bad design.My articles[^]

Split function

cbe_pav24-Dec-08 1:43

cbe_pav

24-Dec-08 1:43

Hi all,
I want to split the ~ symbol from a field.so i wrote a split function ..it is wrking correctly when i give

SELECT * from [dbo].[FN_Split]('1~2','~')
1 and 2 splits correctly..

How to use the split function in a sub query??
st_id contains 1~7
SELECT * from [dbo].[FN_Split](select st_id from st_config ,'~'))

it shows the below Errors:
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

pls give me any solution ..
Smile | :)

Re: Split function

Paddy Boyd24-Dec-08 1:53

Paddy Boyd

24-Dec-08 1:53

You are missing an opening bracket. And it'll die if you ever get more than one row from st_config.

Re: Split function

cbe_pav24-Dec-08 17:41

cbe_pav

24-Dec-08 17:41

We gave The brackets but alos it returns the same error

Re: Split function

Niladri_Biswas1-Jul-09 18:33

Niladri_Biswas

1-Jul-09 18:33

This is because of the fact that when you execute "select st_id from st_config", it gives a records set. In that case your function needs to be called for each and every rows which is not happening at present.

So you are getting this error.

Do I thing, use a loop(while) or Cursor and store the "st_id" values in a varchar variable. Next pass the variable to your split fucntion and your job will be done.

But in that case, you will get multiple records(if your original table has many columns).

Else you can do 1 more thing. While you are looping, make a string of all the values from "st_id" and place in a varchar variable.

e.g. st_id
1~2
3~4
5~6

Make it like set newstring = 1~2~3~4~5~6 (where newstring is of varchar type)

Next execute SELECT * from [dbo].[FN_Split](newstring,'~')

Hope this helps
Smile | :)

Niladri Biswas

Sql server 2005

Nath23-Dec-08 22:59

23-Dec-08 22:59

I had two tables, for Table 1 fields are contactId,Str_name and for table 2 contactId,str_phonenumber,Str_phonetype

example :
Table 1:
111 Krishna
112 Ramesh
113 Kishore

Table 2:
111 8389368936398 M1
111 3535353535353 M2
111 6326326326262 L1
111 3263262626326 L2

Now i need like this 111 Krishna 8389368936398 6326326326262
how to join this I just need any of M1 or M2 and L1 or L2 and contactid and Name how to get this. Please anyone can help me i have been strucked with this problem

Thanks

Krish - KP24-Dec-08 0:08

Krish - KP

24-Dec-08 0:08

You need to write a procedure for getting your required output

Regards
KP

Wendelius24-Dec-08 1:30

24-Dec-08 1:30

You can join the same table several times. Just use different aliases. Something like:

select table1.contactId,
       table1.Str_name,
       alias1.str_phonenumber,
       alias2.str_phonenumber
from   table1,
       table2 alias1,
       table2 alias2
where  alias1.contactId = table1.contactId
and    alias2.contactId = table1.contactId
and    alias1.Str_phonetype = 'M1'
and    alias2.Str_phonetype = 'L1'

Most likely you want to use outer join if there are no matching rows in table2.

The need to optimize rises from a bad design.My articles[^]

Nath24-Dec-08 1:54

24-Dec-08 1:54

Thank u very much for ur suggestion which it works

Wendelius24-Dec-08 2:09

24-Dec-08 2:09

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

Nath25-Dec-08 18:31

25-Dec-08 18:31

sir when there is no matching rows in table2, i have to retrieve the names from table1 where str_phonenumber are null. I tried with outer join getting errors please suggest me how to do

Thanks for Your Help

Wendelius25-Dec-08 21:50

25-Dec-08 21:50

Nath wrote:
I tried with outer join getting errors please suggest me how to do

What is the query like, could you post it? Also what's the error?

The need to optimize rises from a bad design.My articles[^]

Nath25-Dec-08 22:35

25-Dec-08 22:35

error was cleared but is not displaying record which str_phonenumber values are null

Wendelius25-Dec-08 22:43

25-Dec-08 22:43

I don't see why str_phonenumber would affect since it wasn't part of the join. Without seeing your query, it's quite impossible to say what's wrong with it.

The need to optimize rises from a bad design.My articles[^]

Nath25-Dec-08 22:39

25-Dec-08 22:39

by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database

Wendelius25-Dec-08 23:11

25-Dec-08 23:11

Nath wrote:
the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you

Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.

The need to optimize rises from a bad design.My articles[^]

Nath25-Dec-08 23:45

25-Dec-08 23:45

select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber,
m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l,trans_address_phones m
where t.int_contactid=l.int_contactid and t.int_contactid=m.int_contactid and
l.str_phonetype='L1' and m.str_phonetype='M1'

error is incorrect near the join

and I tried with single alias also

select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber from
--m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l on
t.int_contactid=l.int_contactid where l.str_phonetype='L1'

here is the same problem thats the reason, whether it is possible to work with join, Help me sir

Wendelius26-Dec-08 1:06

PLSQL ? ORA-01401: inserted value too large for column

26-Dec-08 1:06

You're on the right track. If you modify the query like the following, do you get right results

select t.str_firstname,
       t.str_lastname,
       l.str_phonetype,
       l.str_phonenumber 
from trans_contacts t 
     left outer join 
     trans_address_phones l 
     on (   t.int_contactid = l.int_contactid 
        and l.str_phonetype = 'L1')

The need to optimize rises from a bad design.My articles[^]

Reddy Prakash23-Dec-08 21:44

Reddy Prakash

23-Dec-08 21:44

How to detect column that raises this error in PLSQL ? ORA-01401: inserted value too large for column

Re: PLSQL ? ORA-01401: inserted value too large for column

Reddy Prakash23-Dec-08 22:00

Reddy Prakash

23-Dec-08 22:00

i am using oracle 9i, in which it is restricted to 'ORA-01401: inserted value too large for column' where as in orale 10g the information is provided 'ORA-12899: value too large for column "STC_USER"."DEPTTAB"."DNAME" (actual: 15,maximum: 14)'

Re: PLSQL ? ORA-01401: inserted value too large for column

Wendelius23-Dec-08 22:07