Ok I am stumped on this. i tried an inner join where posted_jrnl_line as a
and posted_jrnl_line as b, also tried subqueries where (select jrnlId [then
conditions]) to no avail...
I have two queries that execute perfectly. the two queries are retrieving
data from one table but the conditions vary between the queries.
I would like to roll the two queries into one queries so I can create a
report for users.
Query #1
select
jrnl_id as 'System Jrnl',
jrnl_user_alpha_fld_3 as 'Proj Jrnl',
SUM(trans_amt) as 'Proj Jrnl Total'
from
DBSjepc.dbo.posted_jrnl_line
where
dbs_gl_account = '3475'
and jrnl_origin_code = 'PJ'
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_user_alpha_fld_3 like 'TBC%'
Group by
jrnl_id, jrnl_user_alpha_fld_3)
So the output of Query 1 is
System Jrnl | Proj Jrnl | Proj Jrnl Total
Query #2
select jrnl_id as 'Ldr Jrnl',
jrnl_user_alpha_fld_3 as 'Ldr None',
SUM(trans_amt) as 'Ldr Jrnl Total'
from DBSjepc.dbo.posted_jrnl_line
where dbs_gl_account = '3475'
and not jrnl_origin_code in ('PJ','PR')
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_id like 'TBC%'
Group by jrnl_id,jrnl_user_alpha_fld_3
So the output of Query 2 is
Ldr Jrnl | Ldr None | Ldr Jrnl Total
I would like the output to be
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
Total
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1I noticed that the WHERE criteria is not the same. Which criteria you you
want?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message
news:621d8e5ca05d2@.uwe...
> Ok I am stumped on this. i tried an inner join where posted_jrnl_line as
> a
> and posted_jrnl_line as b, also tried subqueries where (select jrnlId
> [then
> conditions]) to no avail...
> I have two queries that execute perfectly. the two queries are retrieving
> data from one table but the conditions vary between the queries.
> I would like to roll the two queries into one queries so I can create a
> report for users.
> Query #1
> select
> jrnl_id as 'System Jrnl',
> jrnl_user_alpha_fld_3 as 'Proj Jrnl',
> SUM(trans_amt) as 'Proj Jrnl Total'
> from
> DBSjepc.dbo.posted_jrnl_line
> where
> dbs_gl_account = '3475'
> and jrnl_origin_code = 'PJ'
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_user_alpha_fld_3 like 'TBC%'
> Group by
> jrnl_id, jrnl_user_alpha_fld_3)
> So the output of Query 1 is
> System Jrnl | Proj Jrnl | Proj Jrnl Total
>
> Query #2
> select jrnl_id as 'Ldr Jrnl',
> jrnl_user_alpha_fld_3 as 'Ldr None',
> SUM(trans_amt) as 'Ldr Jrnl Total'
> from DBSjepc.dbo.posted_jrnl_line
> where dbs_gl_account = '3475'
> and not jrnl_origin_code in ('PJ','PR')
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_id like 'TBC%'
> Group by jrnl_id,jrnl_user_alpha_fld_3
> So the output of Query 2 is
> Ldr Jrnl | Ldr None | Ldr Jrnl Total
> I would like the output to be
> System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
> Total
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi arnie,
that is correct the where clauses are different by the jrnl_origin field.
Query #1 must contain PJ for its output and Query #2 must NOT contain PJ and
PR for its output
Arnie Rowland wrote:
>I noticed that the WHERE criteria is not the same. Which criteria you you
>want?
>
>[quoted text clipped - 50 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||You have in effect two distinctly different queries. Your desired output pro
vides nothing in common between the two queries.
Is all the data on a single line somehow related?
Are you trying for:
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
OR
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data data data data
If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even A
ccess.
From the limited information, I don't see how you will get to the second exa
mple. If you would send in the table DDL and some represtentative sample dat
a in the form of INSERT statements -you may be able to get more help.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message news:621dd08c3fcbe@.uwe...darkred">
> Hi arnie,
>
> that is correct the where clauses are different by the jrnl_origin field.
> Query #1 must contain PJ for its output and Query #2 must NOT contain PJ a
nd
> PR for its output
>
> Arnie Rowland wrote:
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi Arnie,
Actually a mix but Option #1 is what is what I am trying to strive for. the
output does have a relation but not a database one. Users enter a project
journal must use the same journal ID when making their ledger journal.
This is desired.
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data data NULL data
However if placed on two lines that is desirable as well: see below
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
Arnie Rowland wrote:
>You have in effect two distinctly different queries. Your desired output pr
ovides nothing in common between the two queries.
>Is all the data on a single line somehow related?
>Are you trying for:
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data NULL NULL NULL
>NULL NULL NULL data data data
>OR
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data data data data
>If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even
Access.
>From the limited information, I don't see how you will get to the second ex
ample. If you would send in the table DDL and some represtentative sample da
ta in the form of INSERT statements -you may be able to get more help.
>Regards,
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1
