12 Replies Latest reply: Oct 11, 2012 10:19 AM by 967038 RSS

    what's query or function will help me for this issus please ?

    967038
      Hi
      I attached here jpg which will show you there 3 table A , b And c

      And i needed know how i can make Function or any thing else for know which i add into cell question mark

      Pls look into this attached and advise me

      Take your time



      http://imageshack.us/f/853/examplees.jpg/

      Edited by: 964035 on Oct 9, 2012 1:09 AM

      Edited by: 964035 on Oct 9, 2012 2:26 AM
        • 1. Re: what's query or function will help me for this issus please ?
          Fran
          is this an exercise and you want that we resolve this for you? ASAP??? oh my god...
          • 2. Re: what's query or function will help me for this issus please ?
            Purvesh K
            964035 wrote:
            Hi
            I attached here jpg which will show you there 3 table A , b And c
            How about mentioning the same in words or better and most recommended to post the Scripts (table Structures, Sample Inserts and the Expected Results) for us to help. I am sure many people might not be able to access the links due to Company Firewalls.
            And i needed know how i can make Function or any thing else for know which i add into cell question mark

            Pls look into this attached and advise me asap ...
            You need Advise ASAP? Please hire a Consultant that shall help you round the clock. This is a forum of Volunteers who are not Hired/Paid to perform this; but do have their job or other activities to be performed. Hence, It is very rude of You to ask for Help ASAP.
            • 3. Re: what's query or function will help me for this issus please ?
              967038
              I did not imagine when I post this question here that the word ASAP will shed all the attention that and my question will not cast any consideration

              If you do not want to help, it is better to you Silence
              • 4. Re: what's query or function will help me for this issus please ?
                Purvesh K
                964035 wrote:
                I did not imagine when I post this question here that the word ASAP will shed all the attention that and my question will not cast any consideration

                If you do not want to help, it is better to you Silence
                Are you actually asking another 1000 volunteers to keep Mum just because you persist to be ignorant of the etiquettes when asking a Question?

                It does not take much efforts to help others if Others are wanting to help you...

                Such a Pity!!!
                • 5. Re: what's query or function will help me for this issus please ?
                  John Stegeman
                  a). Lose the ASAP
                  b). instead of making us go to a web page with a picture, give us the SQL to create and load the tables plus give us a specification of what you want, rather than making us guess from a picture
                  c). Don't do a). or b). here, but in the proper forum {forum:id=75}

                  Yes, people will jump all over you if you say ASAP or URGENT or anything which implies the forum volunteers should give more attention to your question over any other question
                  • 6. Re: what's query or function will help me for this issus please ?
                    967038
                    Forget ASAP word and take your time for help if you can
                    if you can't help or you don't want to help , pls let who can or want to help for see my question

                    and i promise i will take care in future when i add any post here ...
                    • 7. Re: what's query or function will help me for this issus please ?
                      John Stegeman
                      As I said...

                      1 - this is not the right forum. Use the SQL and PL/SQL one for which I provided you a link
                      2 - when you do post there, give people the SQL to create and populate the tables and describe what you are trying to accomplish. If you insist on posting pictures we can insist on pointing out that it's not helpful

                      Think about it - you are asking someone for help - you should make it as easy as possible for them to help
                      • 8. Re: what's query or function will help me for this issus please ?
                        Purvesh K
                        964035 wrote:
                        Forget ASAP word and take your time for help if you can
                        if you can't help or you don't want to help , pls let who can or want to help for see my question

                        and i promise i will take care in future when i add any post here ...
                        Much Promising!!!

                        Now, Can you you post the data in the Image (for which you posted link)? I am bound my company firewalls, and hence cannot access the link.

                        If you can, please post the
                        1. Table Structures (In form of Create Table or With Clause)
                        2. Sample Data (In form or=f Insert into table or Select from dual)
                        3. Expected Result from the Sample Data
                        4. If it involves any Logic to derive the Expected Data, then an elaborate description shall definitely be helpful.
                        • 9. Re: what's query or function will help me for this issus please ?
                          967038
                          Below the cods for create the table and insert some data as example

                          I needed know how i can Booking The item from stock for each order and how i can know the status of item it's will be available or not so we should do the order purchase for item



                          Create Table Color (                                   
                               Order Number(5) Not Null ,                              
                               Color Varchar2(8) Not Null ,                              
                               Qty Number (8) Not Null )                              

                          Create Table Recipe (                                   
                               Color Varchar2(8) Not Null ,                              
                               Item Varchar2(8) Not Null ,                              
                               Recipe Number (8) Not Null )                              

                          Create Table Stock (                                   
                               Item Varchar2(8) Not Null Constraint Item_PK PRIMARY KEY,               
                               Qty_Of_Stock Number (8) Not Null )               

                          Insert into Color VALUES ( 1500 ,     Red'     ,10);
                          Insert into Color VALUES ( 1500 ,     yellow'     ,15);
                          Insert into Color VALUES ( 1500 ,     Green'     ,8);
                          Insert into Color VALUES ( 1500 ,     Blue'     ,7);
                          Insert into Color VALUES ( 1600 ,     yellow'     ,6);
                          Insert into Color VALUES ( 1600 ,     Green'     ,7);
                          Insert into Color VALUES ( 1600 ,     Blue'     ,8);
                          Insert into Color VALUES ( 1700 ,     Red'     ,10);
                          Insert into Color VALUES ( 1700 ,     Blue'     ,15);
                          Insert into Color VALUES ( 1800 ,     Green'     ,16);
                          Insert into Color VALUES ( 1800 ,     yellow'     ,9);



                          Insert into Recipe VALUES ( 'Red','A',0.25);
                          Insert into Recipe VALUES ( 'Red','B',0.3);
                          Insert into Recipe VALUES ( 'Red','C',0.2);
                          Insert into Recipe VALUES ( 'Red','D',0.25);
                          Insert into Recipe VALUES ( 'Yellow','C',0.1);
                          Insert into Recipe VALUES ( 'Yellow','D',0.3);
                          Insert into Recipe VALUES ( 'Yellow','E',0.2);
                          Insert into Recipe VALUES ( 'Yellow','F',0.4);
                          Insert into Recipe VALUES ( 'Green','C',0.25);
                          Insert into Recipe VALUES ( 'Green','D',0.35);
                          Insert into Recipe VALUES ( 'Green','E',0.2);
                          Insert into Recipe VALUES ( 'Green','G','0.1);
                          Insert into Recipe VALUES ( 'Green','H',0.1);
                          Insert into Recipe VALUES ( 'Blue','A',0.35);
                          Insert into Recipe VALUES ( 'Blue','B',0.35);
                          Insert into Recipe VALUES ( 'Blue','C',0.3);


                          Insert into Stock VALUES ( 'A',50);               
                          Insert into Stock VALUES ( 'B',60);               
                          Insert into Stock VALUES ( 'C',40);               
                          Insert into Stock VALUES ( 'D',30);               
                          Insert into Stock VALUES ( 'E',20);               
                          Insert into Stock VALUES ( 'F',10);               
                          Insert into Stock VALUES ( 'H',25);               
                          Insert into Stock VALUES ( 'G',15);               

                          Take your time my dear ...
                          • 10. Re: what's query or function will help me for this issus please ?
                            Purvesh K
                            964035 wrote:
                            Below the cods for create the table and insert some data as example

                            I needed know how i can Booking The item from stock for each order and how i can know the status of item it's will be available or not so we should do the order purchase for item
                            Thanks for the Data.

                            You missed the Expected Output and the Logic behind achieving the Expected Output.

                            However, Below is the answer with regards to the explanation provided by you. Have a look at the data and add what is your expected output. It eases to verify if the sql conforms with the requirement.

                            Also, i was unable to understand the actual requirement. It would be beneficial if you can explain in more details.
                            select c.ord "Order", c.color, c.qty "Available Quantity", r.item, s.qty_of_stock "Required Quantity"
                              from color c
                              join recipe r
                                on upper(c.color) = upper(r.color)
                              join stock s
                                on r.item = s.item
                            where s.qty_of_stock <= c.qty;
                            
                            Order                  COLOR    Available Quantity     ITEM     Required Quantity      
                            ---------------------- -------- ---------------------- -------- ---------------------- 
                            1500                   yellow   15                     F        10                     
                            1800                   Green    16                     G        15
                            • 11. Re: what's query or function will help me for this issus please ?
                              967038
                              i needed a query show me data as below
                                   
                              -------------------------------------------------------------------------------------------------
                              Item          Stock          Booking          Balance          Required
                              --------------------------------------------------------------------------------------------------
                              A          50          15.5                   34.5     
                              B          60          16.5                   43.5     
                              C          40          23.75          16.25     
                              D          30          24.85          5.15     
                              E          20          12.2                   7.8     
                              F          10          12                   -2                   2
                              H          25          3.1                   21.9     
                              G          15          3.1                   11.9     
                              Edited by: 964035 on Oct 9, 2012 7:34 AM
                              • 12. Re: what's query or function will help me for this issus please ?
                                967038
                                i sill awaiting any advise please