please check

There are 2 tables -
say table 1 - child table - say child
table 2 - parent table - say parent
Assume that each child only resides on a single parent and each parent
may contain multiple children....
Can we write a query for finding how many children are on each and
every parent...?Why are you starting a new thread for the same question?
<chump1708@.yahoo.com> wrote in message
news:1134656162.207875.44300@.o13g2000cwo.googlegroups.com...
> There are 2 tables -
> say table 1 - child table - say child
> table 2 - parent table - say parent
>
> Assume that each child only resides on a single parent and each parent
> may contain multiple children....
> Can we write a query for finding how many children are on each and
> every parent...?
>|||well...thats bcoz the earlier question was confusing and typed wrongly..|||chump1708@.yahoo.com wrote:

> well...thats bcoz the earlier question was confusing and typed
> wrongly..
Did you try my query?
I don't quite understand why the others are giving links to handle this
with self-referenced tables. You said you have 2 tables: one with
children and and with parents. So IMHO there is no need for that.
HTH,
Stijn Verrept.|||Dear Stijin,
select cid, (select count(*) from childTable where gid = cid) as
ChildCount from parentTable
I got with this...why do u use 'as' word ?
and ChildCount - ? - what do u mean how 2 implement this?
All i have is
2 tables
table1 - child ('gid') and
table 2 - parent ('cid'). Each child only resides on a single
parent and each parent may contain multiple children. How do would you
find how many children are on each and every parent?
CAN U PLEASE REWRITE THE CODE>>>>>>|||chump1708@.yahoo.com wrote:

> I got with this...why do u use 'as' word ?
> and ChildCount - ? - what do u mean how 2 implement this?
> All i have is
> 2 tables
> table1 - child ('gid') and
> table 2 - parent ('cid'). Each child only resides on a single
> parent and each parent may contain multiple children. How do would you
> find how many children are on each and every parent?
> CAN U PLEASE REWRITE THE CODE>>>>>>
No need to shout!
It is completely impossible for us to rewrite correct code for you if
you do not provide us with the DLL: check this before asking a
question: www.aspfaq.com/5006
AS is to give the result column a name.
Kind regards,
Stijn Verrept.|||Seriously, have you any experience with T-SQL?
The AS keyword precedes the column alias to be used as the name of the
column in the result set.
ML
http://milambda.blogspot.com/|||select parent, count(1)
from parent p
join child c on p.parentId = c.parentId
group by parent
<chump1708@.yahoo.com> wrote in message
news:1134656162.207875.44300@.o13g2000cwo.googlegroups.com...
> There are 2 tables -
> say table 1 - child table - say child
> table 2 - parent table - say parent
>
> Assume that each child only resides on a single parent and each parent
> may contain multiple children....
> Can we write a query for finding how many children are on each and
> every parent...?
>|||if you want the parent with zero children do the following
select parent, count(1)
from parent p
left join child c on p.parentId = c.parentId
group by parent
"JI" <jidawgs@.hotmail.com> wrote in message
news:%23bFvOhaAGHA.208@.tk2msftngp13.phx.gbl...
> select parent, count(1)
> from parent p
> join child c on p.parentId = c.parentId
> group by parent
> <chump1708@.yahoo.com> wrote in message
> news:1134656162.207875.44300@.o13g2000cwo.googlegroups.com...
>sql

please check

There are 2 tables -
say table 1 - child table - say child
table 2 - parent table - say parent
Assume that each child only resides on a single parent and each parent
may contain multiple children....
Can we write a query for finding how many children are on each and
every parent...?How many times?
ML
http://milambda.blogspot.com/

Please any one try to solve this. its Interesting.

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

Quote:

Originally Posted by Anu139

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

hi all,
i got the answer for this.

declare @.name varchar(50)

select @.name = coalesce( @.name,'' )+ col1 from tab_name

select @.name 'Name'

Placing multiple records on a single line (variables)

Hi, I am new to Crystal Reports, but I know Basic and other programming. I have Crystal Reports XI and am pulling data from our ERP/MRP system, Epicor Vista (Progress DB).

I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).

Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.

Table1 "JobMtl"
Field "JobComplete":String
Field "JobNum":String
Table2 "JobOper"
Field "OpComplete":Boolean
Field "OprSeq":Number

{JobMtl.JobComplete}
False
True
{JobMtl.JobNum}
2010
2011
{JobOper.Complete}
False
True
{JobOper.OprSeq}
10
20
30
40

Let's say I dragged all 4 fields into a report. It would look like this.

JobNum JobComplete OprSeq OpComplete
2010 False 10 True
2010 False 20 True
2010 False 30 False
2010 False 40 False
2011 False 10 True
2011 False 20 False
2011 False 30 False

I would it to read like this

JobNum JobComplete PrevOp CurrOp NextOp
2010 False 20 30 40
2011 False 10 20 30

**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.

Thanks,
Anthony

My email is ls1z282002_at_yahoo.com (replace "_at_" => "@.") if you would a *.RPT with the data I've shown.Check your e-mail.|||Come on
We all want to see the solution|||Here's what I got from her.

There is a second report that somone from another forum helped me on. I actually need to combine both of these into one because I like the report from SvB_NY because she mad the operations a single String. So I need to do some combining of the two.

I have another question that I'm posting below this.

Anthony|||After showing this to everyone at work, they of course asked for more detail in the report :)

What we have is for each operation {JobOper.OprSeq} it may be an Outsourced (There is a boolean field {JobOper.OprSeq}) that states whether that Operation is outsourced. If it is outsourced they want me to list the PONum & POLn. I know this is simple and I thought so too! I even got it too work! So here's the snag...if the Job does not have any outsourced operations it gets skipped in my report! My reasoning is our ERP software doesn't actually make a index for PO's if that job does not have any PO's against it. Makes sense to me. So how do I handle this?

In the report I attached there should be 3 jobs
2010
2011 => Not shown because no outsourced operations
2012

Am I going to have to create 2 seperate reports, save all the information in arrays. Then match up the arrays with some code and print out a report?

Thanks,
Anthony|||SvB_NY, Thanks for all the help!

I got my report to work and I published it into our ERP software. I appreciate all the help and attached is the final product, if anyone cares to look.

I did never get the PO's to work right because I found out we have multiple PO's to a given operation and that created multiple records for the operation, so my logic for getting the "Prev,Curr,Next" operation didn't work. This is OK though because I was running out of room for the data and I had to have a big comment field. If it was dire for us I'm sure I could get it to work, or I actually I would have posted the question here :)

Anthony

PL/SQL help - split an address

I have a single address column that i want to split.
For example I have an address with carriage returns like:

address
----------
Administration Tech Services

1234 Elm Avenue

West Building

I would like a SELECT query to split this address column into 3 like:
address address2 address3
---- ---- ----
Administration Tech Services 1234 Elm Avenue West BuildingYou may need to create a function like this:
Create Or Replace Function Get_Addr
(P_Line Number, P_Addr Varchar2)
Return Varchar2 Is
Type Addr_Typ Is Table Of Varchar2(1000);
V_Addr Addr_Typ;
V_Tmp Varchar2(1000);
I Pls_Integer;
J Pls_Integer;
K Pls_Integer;
L Pls_Integer;
Begin
V_Tmp:=Rtrim(P_Addr)||Chr(10);
For I In 1..P_Line Loop
V_Addr(I):='';
End Loop;
I:=0;
L:=Length(V_Tmp);
While (L > 0)
Loop
K:=Instr(V_Tmp,Chr(10));
If K = 0 Then
Exit;
End If;
J:= K-1;
If J > 0 Then
I:=I+1;
V_Addr(I):= Substr(V_Tmp,1,J);
V_Tmp:=Rtrim(Substr(V_Tmp||' ',K+1));
End If;
L:=Length(V_Tmp);
End Loop;
Return V_Addr(P_Line);
End;
/
And use it like:

UPDATE MyAddrTab
SET Address2=Get_Addr(2,Address)
, Address3=Get_Addr(3,Address);
COMMIT;
UPDATE MyAddrTab
SET Address=Get_Addr(1,Address);
COMMIT;
:eek:

PS: You will need to validate parameters!

Pivot fields into a single column

I have a row that looks like this

Year 2006 2007 2008 2009.....etc.

I want a select statement that will get me this

Year

2006

2007

2008

2009

etc.

Try:

Code Snippet

createtable #t (

c1 intnotnull,

c2 intnotnull,

c3 intnotnull,

c4 intnotnull

)

insertinto #t values(2006, 2007, 2008, 2009)

select

case t2.c1

when 1 then t1.c1

when 2 then t1.c2

when 3 then t1.c3

when 4 then t1.c4

endas [Year]

from

#t as t1

crossjoin

(select 1 as [c1] union allselect 2 union allselect 3 union allselect 4)as t2

-- 2005

select

[Year]

from

(select c1, c2, c3, c4from #t)as p

unpivot

([Year] for [c] in(c1, c2, c3, c4))as unpvt

droptable #t

AMB

|||Thanks, but I can't use create table or insert. Appreciate the effort though.|||

WVUProgramer wrote:

Thanks, but I can't use create table or insert. Appreciate the effort though.

You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.

You just need to use the select part of it (possibly modified to meet your exact requirements)|||Okay, thanks.|||

For the first select, it says there's no from clause in the select statement following the cross join.

In the second one, it says unable to parse text.

I plugged in my field names (which are f1 through f4) and my table name where the t# is

Perhaps i'm confused about the and the [Year].

|||

If you copy and paste the script, then execute it, you will see the result is the same you are expecting in your original post.

Can you post the statement you are trying to execute?

AMB

|||

select

case t2.F1

when 1 then t1.F1

when 2 then t1.F2

when 3 then t1.F3

when 4 then t1.F4

end as [Year]

from

table1 as t1

cross join

(select 1 as [F1] union all select 2 union all select 3 union all select 4) as t2

My table name is table1 my fields are F1, F2, F3, F4

Error Message: Error in SELECT clause: expression near 'UNION'.
Missing FROM clause.
Unable to parse query text.

|||

Where are you testing the script: (which application)?

AMB