Database

Re: SQL 2008 Workgroup - how do CALs work?

1-Jun-09 1:14

There may not be a LMBTFY yet but try this[^], 4-5th result looks promising

Never underestimate the power of human stupidity
RAH

aastudent1-Jun-09 1:26

aastudent

1-Jun-09 1:26

Lots of general info - unfortunately nothing specific to what I am asking. Thanks for the link though

problem in executing stored procedure

souravghosh1831-May-09 20:12

souravghosh18

31-May-09 20:12

I am executing a stored procedure written in oracle from my web page.The procedure has 5 input variable and 2 output variable.But when i am executing the procedure it gives an error.

"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

i ma using following code,

cmdproc.CommandType = CommandType.StoredProcedure
cmdproc.CommandText = "avlmfg.PROC_TUBE_COIL_LINK"
Dim tub_batch As OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2)
Dim tub_aufnr As OracleParameter = cmdproc.Parameters.Add("ls_aufnr", OracleDbType.Varchar2)
Dim tub_matnr As OracleParameter = cmdproc.Parameters.Add("ls_mill_matnr", OracleDbType.Varchar2)
Dim tub_nos As OracleParameter = cmdproc.Parameters.Add("ln_no_of_tubes", OracleDbType.Int64)
Dim tub_arbpl As OracleParameter = cmdproc.Parameters.Add("ls_arbpl", OracleDbType.Varchar2)
Dim tub_error_flag As OracleParameter = cmdproc.Parameters.Add("LS_ERROR_FLAG", OracleDbType.Varchar2)
Dim tub_data1 As OracleParameter = cmdproc.Parameters.Add("data1", OracleDbType.Varchar2)
tub_batch.Direction = ParameterDirection.Input
tub_aufnr.Direction = ParameterDirection.Input
tub_matnr.Direction = ParameterDirection.Input
tub_nos.Direction = ParameterDirection.Input
tub_arbpl.Direction = ParameterDirection.Input
tub_error_flag.Direction = ParameterDirection.Output
tub_data1.Direction = ParameterDirection.Output

tub_batch.Value = txtChargR1.Text.ToUpper()
tub_aufnr.Value = txtPO.Text.Trim
tub_matnr.Value = txtMatnr1.Text.Trim
tub_nos.Value = schqty
tub_arbpl.Value = arbpl
cmdproc.ExecuteNonQuery()

Thanx in advance,

Re: problem in executing stored procedure

Aman Bhullar6-Jun-09 1:50

Aman Bhullar

6-Jun-09 1:50

souravghosh18 wrote:
Dim tub_batch As OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2)

Try adding the length of the varcher2 variable as

OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2, 100)

100 will be the lenght of varchar you have defined in database

Regards
Aman Bhullar
www.arlivesupport.com[^]

Re: problem in executing stored procedure

Niladri_Biswas25-Jun-09 5:57

Niladri_Biswas

25-Jun-09 5:57

In some fields the parameter length mismatch is happening
Smile | :)

Niladri Biswas

CONTAINS not working in my full text search

Brendan Vogt31-May-09 5:13

Brendan Vogt

31-May-09 5:13

Hi,

I just added full text searching to a table, and the text in the ProductLongDescription column is:

On the go. For all purpose Industrial and Outdoor purposes. Half Hose. Available in Grey or Navy Blue.

When I use the following statement then the record above is returned:

SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'go');

When I use the following statement then nothing is returned, but you can see that "for" is in the sentence as well:

SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'for');

What am I doing wrong? Please can someone help me?

Thanks
Brendan

Re: CONTAINS not working in my full text search

Satish Pai31-May-09 19:49

Satish Pai

31-May-09 19:49

Hi Brendan

"a","the","an", "for".. etc are noise words. If you view the Messages can see a information

Informational: The full-text search condition contained noise word(s).

if u execute the same query searching for Outdoor then the query runs properly.

Thanks & Regards
Satish Pai B

line break not working in crystal reports ?

kindman_nb30-May-09 21:58

kindman_nb

30-May-09 21:58

I'm using vs2008 , I try to view multi line at crystalreport textobject as next

<br />
Dim s As String<br />
s = s &amp; "this is first line " &amp; Chr(13) + Chr(10)<br />
s = s &amp; "this is second line "<br />
Dim rpt As New CrystalReport1<br />
<br />
Dim txt As TextObject<br />
<br />
txt = rpt.Section2.ReportObjects("Text1")<br />
txt.Text = s<br />
<br />

but it isn't working ,
it print the text at single line ,, despite of when I export the report to doc file, I fount that it's printed at two lines ..

what can I do
Thanks

jooooo

Re: line break not working in crystal reports ?

Rajesh Anuhya1-Jun-09 0:03

Rajesh Anuhya

1-Jun-09 0:03

try this one.

s = s & "this is first line " & vbCrLf

Rajesh B --> A Poor Workman Blames His Tools <--

Help with SQL query

Joe_P30-May-09 10:30

30-May-09 10:30

declare @BatchHeader table
(bhid int, status int)
insert into @BatchHeader values (1, 10)
insert into @BatchHeader values (2, 10)
insert into @BatchHeader values (3, 10)
insert into @BatchHeader values (4, 10)
insert into @BatchHeader values (5, 15)
insert into @BatchHeader values (6, 15)
insert into @BatchHeader values (7, 15)

select * from @BatchHeader

declare @BatchHeaderReference table
(parentid int, child int)

insert into @BatchHeaderReference values (1,2)
insert into @BatchHeaderReference values (1,3)
insert into @BatchHeaderReference values (1,4)
insert into @BatchHeaderReference values (1,5)
insert into @BatchHeaderReference values (1,6)
insert into @BatchHeaderReference values (1,7)

select * from @BatchHeaderReference

@BatchHeaderReference.ParentId and Child are the FKs to @BatchHeader.bhid

The output is this:
@BatchHeader
bhid Status
1 10
2 10
3 10
4 10
5 15
6 15
7 15

@BatchHeaderReference
Parentid Child
1 2
1 3
1 4
1 5
1 6
1 7

I want to write a query to fetch unique @BatchHeader rows only if the parent and child have status = 10. So in this case the query shoudnt return any @BatchHeader rows (bcos bhid 5,6,7 have status 15). But if I update the status for bhid=5,6,7 to 10 then the query should return bhid=1.

I tried something like this but it returns bhid=1
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

Please help.

Thanks,
Joe

Mycroft Holmes30-May-09 23:06

30-May-09 23:06

I had to go look at this properly IE in TSQL. @BatchHeaderReference joins ALL records to the header record 1, therefore header records 2-7 do not take part in the statement. no link to the @BatchHeaderReference table!

Joe_P wrote:
oin @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid

What database are you using, I have never seen readpast

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 4:11

31-May-09 4:11

I am using SQL Server as the database. I have copied and pasted the query as-is but for simplicity, I changed the tables to table variables, so you see the (readpast) in the query that fetches rows from a table variable.

I have to achieve this in a single SQL, so cant use any complex logic of additional table variables.

Thanks,
Joe

Mycroft Holmes31-May-09 11:10

31-May-09 11:10

You missed the point, the problem is in your join. All reference records join to hdr record 1 and are therefore valid.

parentid int (1) = bhid int (1) therefore status will always be 10

@BatchHeaderReference
Parentid Child
1	2
1	3
1	4
1	5
1	6
1	7

Is correct, status 15 never comes into it and the 2-7 are NOT FROM THE HDR table. I think your data structure is screwed, if your status was on the reference table then the filtering would work

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 11:23

31-May-09 11:23

I understand, but I cant have a status on the BatchHeaderReference table. This table is just to figureout the parent/child relationship and the status of these should be retained on the BatchHeader table only.

So only if all the header and child rows from HDR table are in status 10 then the query should return the id.

Thanks,
Joe

Mycroft Holmes31-May-09 11:36

31-May-09 11:36

Joe_P wrote:
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

One begins to see the light....
Change the joins to INNER JOIN and add the filter (=10) on BH2 join rather than the where clause

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 14:08

31-May-09 14:08

Thanks for the reply Mycroft Holmes, but no luck.

Tried this
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id and bh1.Status = 10
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10

And
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10
Where
bh1.Status = 10

Mycroft Holmes31-May-09 17:24

31-May-09 17:24

Try this

Select * 
From @BatchHeaderReference bhr
	INNER Join @BatchHeader bh1 on bhr.Child = bh1.bhid
WHERE bhr.child IN (SELECT bhid FROM @BatchHeader WHERE status = 10)

Never underestimate the power of human stupidity
RAH

smcnulty20004-Jun-09 1:24

smcnulty2000

4-Jun-09 1:24

The way I read this problem all of the children must have the status of 10. If the parent has any children without the status ten then it doesn't come back.

This makes it a bear because you have to do something to match all children with all children who have a status of 10 and only return the parent if the children all come back as status 10.

So I'd use a counting mechanism.

select distinct x.parentid 
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount 
from @BatchHeaderReference bhr
) x
 where x.cnt=x.truecount

I'm more of an oracle guy but the above will work.

Going from the inside out, which is usually the best way to deal with these problems, you create a count of all children. Then a count of all 'true' children- those with a status of 10. Then you put all of that inside of a wrapper which we call X.
From there we treat X as another table.

If you need to also check to see if the parent has a status of 10 then you just change it to this.

select distinct x.parentid  
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount, 
 (select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.parentid) pstatus
from @BatchHeaderReference bhr  
  
) x
 where x.cnt=x.truecount 
 and x.pstatus=10

Here you get the parent status, calling it pstatus, and then just check it at the last stage.

edit: the reason MAX is used is to stop certain errors from creeping in if you have the same value show up but multiple times. If you expect different values for status against a given id then you'd have to take another approach because this would be incorrect.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.