Complex Code

[Tim...]

Hi.

I don't understand the process you are talking about.

Tell you what. Post me:

- The CREATE TABLE statement to create the table, rather than a DESC.
- The INSERT statements to set up some test data that is representative of your data.
- An explanation of where the start and end dates are coming from. Are they parameters in the query, or something else.
- A mock-up of the output you would expect to see from the query, based on the test data you post. I mean literally what SQL*Plus is meant to output if you were able to write the query.

This is what I expect for any SQL question like this. You should aim to provide all the setup scripts for the test case. That way it save me having to waste my time setting things up.

Cheers

Tim...

[Tim...]

Hi.

I've told you what to do several times in your other threads. It's hard not to get pissed off when I feel like I'm banging my head against a brick wall! Surround the code with with the CODE tags. You can do this manually or you can use the toolbar. You highlight the text, then click the "Code" button in the toolbar above the text area. This surrounds the code with the CODE tags.

Do not email me code etc. I do not accept questions over email.

I do not need the whole shell script. You asked a specific question regarding an individual query. Lets look at that. Adding the whole shell script will complicate finding the solution. Send the things I asked for and only those things!

This is the second thread you've started like this that has contained me asking for things and you ignoring me. I'm happy to help if you do what I say. If not, then you are just wasting my time and that is not fair on me, or anyone else that wants my help.

Cheers

Tim...

[Tim...]

Hi.

Now the code is finally using the code tags. That's taken weeks to get this far, so I guess I should be grateful!

What I asked for is:

- A CREATE TABLE statement to create the test table. (Haven't really got that because I don't have your schema, so your query to build the test table is meaningless to me.)
- INSERT statements to populate the test table. (See previous point.)
- An explanation of where the start and end dates are coming from. Are they parameters in the query, or something else. (From your script I can see these are hardcoded, so they are effectively constants. Didn't need the whole script. You could have just said they were constants.)
- A mock-up of the output you would expect to see from the query, based on the test data you post. I mean literally what SQL*Plus is meant to output if you were able to write the query. (Your problem is with a query. Let's deal with this in isolation. A query can be run in SQL*Plus and it will give output. Even if you can't write a query, you can fake what the output should look like and thereby give me a clue as to what you are trying to do.

I can see the problem, but I'm not going to say what it is until you provide me with what I asked for. Why? Because I'm trying to teach you how to correctly formulate a question so you don't piss me off again, or anyone else on any other forum. This is really basic stuff. If you can't formulate a reasonable question, then people will be very reluctant to help you.

Tim...

PS. If you had ever bothered to read the sticky note at the top of this forum, it would have been obvious many weeks ago what you should be doing:

viewtopic.php?f=2&t=6261

[Tim...]

Hi.

1) Yes, the script will work, but as I have no partitioned tables in my test database it will give me a big fat nothing! Think about it for a second. I don not have your schema!!!!! This is why I asked for INSERT statement to give me some test data.

2) I didn't not need the long explanation. It doesn't actually say anything different to what you said the first time around. As I said, I understand your problem now, but I want you to provide the information I asked for. All 4 elements. I can imagine you think I am being overly obstructive, but this is the second thread you've started that has gone the same way. If you can't even read my posts and provide the very simple information I am asking for, what does that say about your chances of success in the IT industry?

3) Where is the mockup of the output you would like to see coming from a query? You do not need PL/SQL for the driving cursor. It is a simple SQL statement to give you the result you desire. Think about what you would like the cursor to return really, without having to resort to PL/SQL. Put that output down in text, based on the test data you send me, and post it.

Tim...

PS. I don't not accept emails. Post the inserts on the forum.

[Tim...]

Hi.

No. You have not answered by questions. I still have no CREATE TABLE statement and no INSERT statements. Read my post!!!!!!!!!!!!!!!!

Regarding the mock-up output. You have a cursor defined as "select part_name,tab_name,dat from temp_table;" The output of the DAT column is not what you really want. What you should do, when you have built the test case (CREATE TABLE and a few rows worth of INSERT statements you are going to send me.... Hopefully) is run this query, then replace the data shown in the DAT column, for the data you would really like to see. That will tell me exactly what you are aiming to do.

Like I said, I actually know this already, but I just want you to complete the process of asking a reasonable question once before I die.

Tim...

[Tim...]

Hi.

No it was not posted previously. None of your other posts had CREATE TABLE and INSERT statements in them. I've just gone back and checked. Nothing. All you had was the "CREATE TABLE ... INSERT" statement, which would not work because I do not have your schema, as I explained twice.

This looks like it is real data. I hope for your sake the data gives examples of the situations you are trying to code around. The point of the sample data is it should have rows that exhibit the behvior you are trying to test.

Where is the mock-up of the desired output?

Tim...

PS. I am going out for a couple of hours now. I'll check the thread when I come back to see if you have posted the mock output then.

Tim...

[Tim...]

OMG. I give up!!!!!!!

Let's ignore actually writing some decent code and hack this rubbish to make it work...

This bit of code has so many things wrong with it, it is hard to know where to start...

Code: Select all

         if '31-Oct-' <> '$YE'
            then     
              if part_rec.dat < '31-Oct-'||to_char(To_date(part_rec.dat,'DD-MON-YYYY'),'YYYY')
               then      PARTITION_YR := to_char(To_date(part_rec.dat,'DD-MON-YYYY'),'YYYY')-1; 
               else      PARTITION_YR := to_char(To_date(part_rec.dat,'DD-MON-YYYY'),'YYYY')+1; 
              END IF;
          else PARTITION_YR := to_char(To_date(part_rec.dat,'DD-MON-YYYY'),'YYYY');

The DAT is a DATE column according to your table definition. Why on earth would you TO_DATE a DATE column? It is already a date!

You have a complete shit-storm of implicit conversions going on, trying to make some sense of what you have coded. The results of this is totally dependent on the implicit date format of the database. Any implicit conversion where the exact end product it not know is terrible code. Just correcting the misused and missing conversion functions you get this.

Code: Select all

         if '31-Oct-' <> '$YE'
            then     
              if part_rec.dat < TO_DATE('31-Oct-'||to_char(part_rec.dat,'YYYY'),'DD-MON-YYYY')
               then      PARTITION_YR := TO_NUMBER(TO_CHAR(part_rec.dat,'YYYY'))-1; 
               else      PARTITION_YR := TO_NUMBER(TO_CHAR(part_rec.dat,'YYYY'))+1;
              END IF;
          else PARTITION_YR := TO_NUMBER(TO_CHAR(part_rec.dat,'YYYY'));

This is still an awful solution, but I've lost the will to live now.

Tim...

[Tim...]

Write a single SQL statement that gives you the following columns:

Code: Select all

table_owner, table_name, partition_name, partition_yr

Forget about PL/SQL for the logic. Use a CASE expression. Just do it in a single SQL statement. If you get stuck, post:

- Some sample data that will test all possibilities you are testing for. For the logic in your code, you could probably get away with about 5 or 6 rows. Don't use that data you harvested from you real system. There is too much of it and it doesn't specifically target the issues you should be testing for.
- The query you have so far.
- The output you would like to see if the query did what you wanted it to do and was tun in SQL*Plus. I'm not talking about "alter table...". I'm talking about columns and row from an SQL statement.

Tim...