what is the function name or the logical name for freezing data? 1

[Dausa67]

End result: Chart
Data is for vendor performance.

I have built my query to pull all open orders for outside sales for 4 different vendors. I have added a formula to tell me how many days early or late each line item is. The report that I have now is for the month of March. I want run the report on the first day of each month to capture a snapshot of the current trend. My current Chart has 1 bar for each of the vendors. When I run this again on April 1st I want it to keep the current snap shot for March and put a bar beside it reflecting current trend for April. In the end, “management” wants the report to reflect up to 12 months of activity for these 4 vendors.

What function or SQL clause do I use to create this chart snapshot of each month? I know this has a name and it's not Oscar Meyer.


Clay

 

[Thargy]

My car is broken. Can you tell me how to fix it please?

Regards

T

 

[Dausa67]

I guess that is an attempt at sarcasm....

That's a good one... Crickets

Clay

 

[Thargy]

Sarcasm no, desperation - yes.

It's like this, I enjoy tackling Oracle problems, but to do so, I, (and everybody else) need a certain essential minimum of data.

So, how about posting a question along the lines of:-

I am extracting data from a table
(create table statement posted)
which contains the following sample data
(explicit insert statements of anonymised data posted).
I have the following query
(query text posted)
which does the following business function (blah blah) for March. I want to extend it to work for April and produce the following output
(sample output posted).

Your OP is typical of so many, in that the author knew what he meant when he/she wrote it. The rest of us are not privy to the information that you are, ergo we have to be provided with it.

Given such information, Dagon could probably do this in his sleep, whilst I look on pretending to know of which I speak. ok?

Regards

T

 

[Dausa67]

Ok. Actually I am glad you told me that so that I can make this as easy as possible for those trying to help because as you can see.... I NEED HELP!!!

so here is my attempt at your suggestion. (I honestly don't understand some of what you said so if I do not get it right, please correct me on what I am doing wrong.)


I am extrating data from a table.
SELECT DISTINCT a.end_item,b.field_value po_number, a.order_id, a.additional_information_3 part_no,
INITCAP(a.part_description) part_description, a.status
FROM hms.wip_order a
,(SELECT a.order_id, a.field_value
FROM hmsshop.sd_oper_text_variables@HMSPROD a
WHERE a.order_id IN (SELECT a.order_id
FROM hms.wip_order a
WHERE a.order_id LIKE 'R00%'
AND a.status IN ('N','S'))
AND a.standard_id = 1918 ) b
WHERE a.order_id LIKE 'R00%'
AND a.status IN ('N','S')
AND a.order_id = b.order_id
SELECT "WIP_ORDER"."PART_ID", "WIP_ORDER"."DATE_START_PLAN", "WIP_ORDER"."ORDER_ID"
FROM "HMS"."WIP_ORDER" "WIP_ORDER"


Which contains the sample data below (I also have a ganked up attachment)
What this does is the function of grouping all open orders by vendor and states how many days late they are.

I want to freeze this view so that next month when I run the report again it will automatically capture the current trend for each month.

The sample below is the only thing I can give. I have tried exporting it as html, rtf but all of these formats have different issues that will not let the chart open so you can see it too.

Any suggestions?


DASSAULT AVIATION

49B16384 R00342654 F90 51753 503 12/10/2007 9:26:10AM 4/8/2008 9:26:10AM 351
49B26601 R00499090 F90 51717 509 1/20/2009 12:51:22PM 5/20/2009 12:51:22PM 0
49B26167 R00509798 F90 51724 515 2/11/2009 8:56:30AM 6/11/2009 8:56:30AM 0



Clay

 

[Thargy]

ok,

thanks for a much more informative post.

First of all, it's now my Friday evening, and after a blood-bath on the motorway I am home, but fading fast. Please understand that I may not post again until Monday.

You are running two queries which appear to have no connection. The first appears to be a correlated sub-query

SELECT DISTINCT a.end_item,b.field_value po_number, a.order_id,  a.additional_information_3 part_no,
       INITCAP(a.part_description) part_description, a.status
       FROM hms.wip_order a
      ,(SELECT a.order_id, a.field_value
          FROM hmsshop.sd_oper_text_variables@HMSPROD a
          WHERE a.order_id IN (SELECT a.order_id
                                  FROM hms.wip_order a
                                  WHERE a.order_id LIKE 'R00%'
                                    AND a.status IN ('N','S'))
           AND a.standard_id = 1918    ) b
  WHERE a.order_id LIKE 'R00%'
    AND a.status IN ('N','S')
   AND a.order_id = b.order_id

and the second apparently unconnected query is

SELECT "WIP_ORDER"."PART_ID", "WIP_ORDER"."DATE_START_PLAN", "WIP_ORDER"."ORDER_ID"
 FROM   "HMS"."WIP_ORDER" "WIP_ORDER"

Your post mentions that you are trying to export this in a manner suitable for a chart, and that you want to be able to observe some sort of trend. Is this also a requirement, i.e. do you need this result as comma-separated variables or something similar?

I can't determine the trend you seek because the result data which you posted had no headings, and there was no clear requirement. Can you provide me with an English language narrative of what you're trying to do, so that I can head towards a solution for you.

In order to have a free hand to devise a query, I will need the create table statement and insert statements for the underlying data. Since you may be having difficulty in achieving this, allow me to suggest the following:-

If you can obtain TOAD or download SQL Developer (which is free from Oracle) this will be much easier. Just connect any of these tools to the database in question and they can generate the statements at the click of a button. If you are proficient with sqlplus, then just describe the table and spool the result to a file.

Can you let me know how you get on with accessing the table and the data. If you have further trouble, let me know.

Regards

T

 

[Thargy]

one further thing, what constitutes an open order, and since the first query selects part_description, why is there no description visible in your output?

Regards

T

 

[Dausa67]

Thanks for the reply slayer. I will do my best to answer your questions. I answered your questions below and mid way was interupted with the statement "Oh by the way, we don't need that report now". Nice! Thanks for the attempt to help me. Disregard the following.

Your post mentions that you are trying to export this in a manner suitable for a chart, and that you want to be able to observe some sort of trend. Is this also a requirement, i.e. do you need this result as comma-separated variables or something similar?
If I am understanding your question correctly, yes it is a requirement. They want the chart for management but also want the actual data for problem solving issues by each line item.

I can't determine the trend you seek because the result data which you posted had no headings, and there was no clear requirement. Can you provide me with an English language narrative of what you're trying to do, so that I can head towards a solution for you.
I am using Crystal Reports XI. Management wants a chart that shows "late open orders", "on time open orders", "% of late open orders" for each vendor using a bar chart. They also want this chart to grow as time goes by. Example; next month they want the chart to have 2 bars per customer. One showing for March and one showing for April .

If you can obtain TOAD or download SQL Developer (which is free from Oracle) this will be much easier. Just connect any of these tools to the database in question and they can generate the statements at the click of a button. If you are proficient with sqlplus, then just describe the table and spool the result to a file.
I have SQL Developer which I just downloaded and suck at. When you say statement, are you refering to the report once it is ran or more like the explain feature? I have listed the explain below.


Clay

 

[Thargy]

Da,

you do not suck at SQL Developer, you are simply unfamiliar with it. I have plenty of time for those who admit to needing help, but little for those who presume to know it all. There, after a little cathartic declamation, I feel much better.

Now, since you've taken the trouble to download SQL Developer, but the report is no longer needed, should I abandon this thread, or (as I would suggest) would you like to continue, so that next time something pops up, you're better prepared to handle it?

What I mean by the statement is a table create statement, such as

CREATE TABLE DAUSA_EXAMPLE
(
 DUMMY_COLUMN1 VARCHAR2(50),
 DUMMY_COLUMN2 NUMBER(10,2)
);

Cut and paste the statement above into the sql window of developer. click on the green triangle at the top left (to run it) and you should produce a table in the db. Developer should put up a message saying "Table successfully created" or something similar.

What you need to do is then click on the table, or right click or find some other menu option in developer which will produce the above statement from the db. Then you will know that you've got it right, i.e. from a db table you will know that you can produce a sql statement to create that table.

Then do the same thing "for real" on the table you're attempting to query. SQL Developer may also offer you the ability to generate insert statements for the data in the table, in a similar manner. If you have thousands or millions of rows, just post a sample of say 20 rows, so I can get a feel for the data and its attributes. If any of that comes across as drivel, please let me know.

Regards

T

 

[Dausa67]

T
Sorry it took so long to get back on. Thank you for the encouragement. Not to go all Dr. Phil on you but you are helping me increase my worth at my job which is extremly important and my family and I appreciate you going the extra step to even teach me one thing. Thanks again.

I clicked on open SQL worksheet. I have copy and pasted the statement into the SQL worksheet. It gives me an error message: I do not have privileges to tablespaces. Is this something that I can give myself privileges to or will I have to ask my MIS department?

Just a side note, I have also downloaded developer at home so I may not have the same restrictions at home. Not really sure.

One more note. I have figured out how to connect to the oracle data base here at work and I have been able to create a small query so I am going to list the code below.

select item_id, item_desc, order_max, order_min
from dfj.asi_item_master
where item_id LIKE 'F2XJ510042C7009'

My result did bring back the part number that I chose.




Clay

 

[Thargy]

No, you don't have privileges, you have to get MIS to grant them to you. Before doing so, please read further.

If you use sql developer at home, I presume you've installed oracle locally on your personal computer.

When you installed oracle, you will have provided a system administrator's password, which is the "super-user" password for the db. The password is for the SYS user, which is the most powerful user and the "owner" of the database. At home you can create other users, and then login as those users, create tables, run queries etc.

At work, your DBA's will strictly control who has access to the database, and you will NOT have been given the SYS password, but a much less powerful login. This is to protect you from your own mistakes. For example, with a restricted login, you can't accidentally drop the entire database.

So, to be specific, the login details provided do not enable you to write to the tablespace in question. This is probably a good thing whilst you learn, as you are prevented from accidentally altering production data.

You could try logging in to the SCOTT/TIGER schema, and learning there. Many systems load this example schema, where you can safely play, at no risk to valuable data. Ask your DBA's if this is ok. Alternatively, ask if you can work on a copy of production so that if you make a mistake it doesn't matter.

Until you're sure that your account is incapable of altering production data, don't run any delete, insert or update statements. Ideally, don't even log in to the db.

Let me know how you get on with Oracle at home.

By the way, listing a query as you did in your post is exactly the way one should do it. Many posts start something like "I have a query which isn't doing what I want blah blah" and never post the query - sigh.

Have a go at extracting a create table statement using sql developer, and let me know how you get on. It's great to see your desire to learn.


Regards

T

 

[Dausa67]

T
I downloaded the oracle express database and I was able to create the table. I can't find it anywhere, but it let me create it.

Clay

 

[Thargy]

that's an excellent start. Since I do this for a living I use enterprise version at home (for compatibility with work) but the express edition is intended as a 'lite' version of oracle, from which to begin, so you've got that spot on.

To find the database is counter intuitive, but relatively easy. Look for a folder called oracle, and in one of its subfolders should be a folder called oradata. In there you will find the various files which make up your database.

Since you're safe at home, play around with creating a table, making some insert statements to populate it and then query from it. If you go to the oracle web site there will likely be some tutorials for you.

Rather than waffle on, how about on Monday, generate the create table and insert statements from something at work, and bring the text files home. Then run those files in your personal oracle, and you can try things out. I know that this is "Taking work home with you" but it is probably more useful to solve real problems than to just flounder aimlessly around in oracle.

So, would you like to come up with another problem (and we can start a separate thread) or get something from work?

Like the genie in Aladdin, I await your command o illustrious master....

Regards

T

 

[Dausa67]

I will get straight to it. I was not able to do anything at home this weekend due to building a deck for my mom. So I am at work and hopefully I will be able to do it both at work and at home. I don't mind taking work home at all. If I can learn I will do what it takes.

Looking at the express edition I can not find the folder oracle. Let me tell you my connections first to see if I have the correct database.

Connection Name: (I chose Practice)
Username: system
Role: Default
Connection Type: Basic
Host Name: 127.0.0.1
Port: 1521
SID: xe

Is this correct?

We can start a new thread if you want and we can use my current project too from work. It is yet another report that is over my head.

What would you like for me to call the thread so you can find it?

Clay

 

[Thargy]

da,

it looks about right to me, but I'm looking at TOAD and enterprise edition right now, so I can't say for definite.

Yes, start another thread and see if you can do an exemplary posting. So, you should extract the following statements and include them in the post
1) create table
2) insert statements to populate the table
3) Statement of desired output as per your statement
above, N.B. including column headers.

i.e. your statement of desired output should look a bit like

DASSAULT AVIATION

    49B16384    R00342654    F90 51753 503    12/10/2007   9:26:10AM    4/8/2008   9:26:10AM    351
    49B26601    R00499090    F90 51717 509    1/20/2009  12:51:22PM    5/20/2009  12:51:22PM    0
    49B26167    R00509798    F90 51724 515    2/11/2009   8:56:30AM    6/11/2009   8:56:30AM    0

Regards

T

 

[Dausa67]

What is an exemplary posting?

The new title will be Newbie to SQL.

Clay

 

[Thargy]

one that includes the statements I mentioned above.

Regards

T

 

[Dausa67]

ok. I will work on it. please be patient with me. As you can probably tell I have a lot to learn and I don't always understand what you are asking of me. but I am determined.

I should have it up pretty soon.

Clay

 

[Thargy]

my apologies, if anything is unclear, do pipe up.

I am assuming too much here - mea culpa.

Regards

T

 

[Dausa67]

This is taking longer than I thought. For what ever reason when I downloaded 10g on my work PC it changed my TNS settings. I am trying to do a system restore back to before I put 10g on my PC so I can log on the my database at work. The system restore lets me pick a restore point but it will not go any further. Hopefully I will get it working soon.

Clay