Hi everyone,
I would like to get the SQL statement or best SQL approach to get a result
set that provide one line for every custinvoicetrans record AND WITH THE
COSTVALUE (entered+adjustment). I think some of you have problem done this
a lot of times so I hope you can help me.
Now I 'know' the basic theories of SQL etc and so have made an attempt but
would like a little help if possible.
I have gone to Axapta and found how Axapta calcs the cost and have tried to
translate that into an SQL statement (see psuedo below, feel free to make it
into real SQL). The 3 main problems I have are/foresee:
1) Converting into real SQL syntax (this I just need to get refreshed so any
refresher tips would be great)
2) How to bring back 1 result line if there are several inventtrans records
attached to one custinvoicetrans (the group by, sum and whether can use
these functions together I am not so clear on and so this is my main
challenge).
3) How to sum costamount and adjustment fields together the best way
My psuedo attempt:
select itemid, invoiceid, sum(lineamountMST)
from custinvoicetrans
inner join inventtrans
Select sum(costamount), sum(entered
where inventTrans.inventTransId ==
custinvoicetrans.transId &&
inventTrans.dateFinancial ==
custinvoicetrans.invoiceDate &&
inventTrans.invoiceId ==
custinvoicetrans.invoiceNum &&
inventTrans.qty != 0
&&
inventTrans.packingSlipReturned == 0
&&
inventTrans.statusReceipt <= StatusReceipt::Purchased
&&
inventTrans.statusIssue <= StatusIssue::Sold
>From my understanding this will give more than one result line if there are
more than one inventtrans record, so how to get the inventtrans summed up
and just one result line per custinvoicetrans returned?
I hope someone can make sense of what I am trying and hopefully then help me
on my way
Thanks
James
{
-----Original Message-----
From: Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Varden Morris
Sent: 08 August 2005 02:02
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: RE: [Axapta-Knowledge-Village] Re: SQL script to do record count
There is actually a way to display the tables name and the number of records
in each in Axapta (Both version 2.1 and 3.1). I do not have access to Axapta
at the moment but I think the path is Tools - > Development -> Number of
records. Unfortunately the screen that comes up is not an ordinary grid that
allows you to copy and paste the data for easy analysis but it can help in
some way. I Actual wrote a routine once that does the same think but allows
you to copy and paste the data. The routine made use of the dict classes to
get a list of all the tables in Axapta. You can combine this with a class in
Axapta (Sorry I forget the name) that allows you to use the native code of
the backend database to do the record count. You must remember to filter by
DATAAREAID when using this class.
All the best,
Varden Morris
James Flavell <[EMAIL PROTECTED]> wrote:
Thanks but my axpata is version 2.1 and no such report exists unfortunately
Thanks
James
-----Original Message-----
From: Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Sonny Wibawa
Adi
Sent: 05 August 2005 10:36
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: RE: [Axapta-Knowledge-Village] Re: SQL script to do record count
Hi James Flavell,
There is a menu in Axapta to obtain that information.
The menu is Administration | Reports | Size of company accounts. The report
name is SysCompanySize report.
Best regards,
Sonny Wibawa Adi, MBSCP, MCAD.NET, MCSD.NET, MCP
--- "Suresh Kumar K."
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> If you just need the list of tables and the number
> of records in it, I
> think all u have to do is get the values of the
> column TABLE_NAME from
> the table USER_TABLES. Based on this info all you
> have to do is a
> COUNT(*) for those tables. You will end up with the
> returned value of
> record counts for those tables. I currently don't
> have Oracle installed,
> else I could have send the script.
>
> Regards,
>
> Suresh.
>
>
>
>
>
> ________________________________
>
> From: Axapta-Knowledge-Village@yahoogroups.com
> [mailto:[EMAIL PROTECTED] On
> Behalf Of
> cdlmalherbe01
> Sent: Thursday, August 04, 2005 5:04 PM
> To: Axapta-Knowledge-Village@yahoogroups.com
> Subject: [Axapta-Knowledge-Village] Re: SQL script
> to do record count
>
>
>
> Hi James,
>
> The following is crude and inefficient, but does the
> job.
>
> create table ##TableRecordCount
> (TableName varchar(256) not null,
> RecordCount bigint not null)
> declare @TableName varchar(256)
> declare @SqlString nvarchar(4000)
>
> declare Tables cursor read_only
> for select [name] from sysobjects where [xtype] in
> ('U')
>
> open Tables
>
> fetch next from
> Tables
> into
> @TableName
>
> while @@fetch_status <> -1
> begin
> if @@fetch_status <> -2
> begin
> set @SqlString = 'insert
> into
> ##TableRecordCount select ' + '''' +
> ltrim(rtrim(@TableName)) + ''''
> + ',count(*) from ' + @TableName
> exec sp_executesql
> @SqlString
> end
>
> fetch next from
> Tables
> into
> @TableName
>
> end
>
> select * from ##TableRecordCount
>
> close Tables
> deallocate Tables
> drop table ##TableRecordCount
>
> Hope it helps!
>
> Christoph
>
> --- In Axapta-Knowledge-Village@yahoogroups.com,
> "James Flavell"
> <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > Does anyone have a SQL script that I can run
> against a Axapta DB
> to get a
> > simple output of table name and record count for
> the table?
> >
> > I need to do a check of record counts from a SQL 7
> to SQL 2000
> upgrade to be
> > sure everything is in order after the upgrade.
> >
> > Thank you very much
> > James
>
>
>
>
> Sharing the knowledge on Axapta.
>
>
>
>
> ________________________________
>
> YAHOO! GROUPS LINKS
>
>
>
> * Visit your group "Axapta-Knowledge-Village
>
<http://groups.yahoo.com/group/Axapta-Knowledge-Village>
> " on the web.
>
> * To unsubscribe from this group, send an email to:
>
> [EMAIL PROTECTED]
>
<mailto:[EMAIL PROTECTED]
> ubscribe>
>
> * Your use of Yahoo! Groups is subject to the
> Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/> .
>
>
>
> ________________________________
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Sharing the knowledge on Axapta.
Yahoo! Groups Links
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Sharing the knowledge on Axapta.
SPONSORED LINKS Business finance course Business to business finance Small
business finance
Business finance consultant Business finance magazine Business finance
schools
YAHOO! GROUPS LINKS
Visit your group "Axapta-Knowledge-Village" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12hddf4cv/M=362343.6886682.7839641.1493532/D=groups/S=1705001380:TM/Y=YAHOO/EXP=1123866224/A=2894354/R=0/SIG=11qvf79s7/*http://http://www.globalgiving.com/cb/cidi/c_darfur.html";>Help
Sudanese refugees rebuild their lives through GlobalGiving</a>.</font>
--------------------------------------------------------------------~->
Sharing the knowledge on Axapta.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
