Mysql Temporary Views - NAME COLLISION

More on this,

This view will not be run locally, it will be run globally with multiple users connecting at the same time.

what im hoping is to put the VIEW into crystal reports, and every time the view is run , it is given a new name, so the next person that connects in one second later, running the same VIEW for a different date period, gets a NEW TEMPORARY VIEW NAME so multiple users can connect at the same time, with out collision or error.

Thankyou. (hope that makes sense!)

>What if 2 people want to run this VIEW at the SAME time
the second one will overwrite the first one, if it was CREATE OR REPLACE.
without that, the second will fail as the view already exists.

the question is: why do you use CREATE VIEW to run the query?

apart from that: to avoid the collision, you have to create the view with a "unique" name, for example using the session id as part of the view name.

Hi Angel,

I'm not sure i understand your answer.

>What if 2 people want to run this VIEW at the SAME time
the second one will overwrite the first one

The second one doesn't over write the first one, it gets an error 'View already exists.'

WHY i want to create a VIEW, i would have to have a convo over the phone with you for about 20 minutes to help you understand why, but basically, i cant total, all my feilds in one quer, so i create a view, and then sum the feilds, and then again, to get the results i need.  From there, i want to look at exporting that VIEW into Crystal reports. as a table, which i have tested and works, but the problem is, What if 2 people want to run the report ( or the View) the view has a name for eg,  TABLE_1  if a second person wants to run this weekly summary, they will get the error, 'table already exists.'

I have tested this, in the borwser, by running 2 instances of the view. 1 wonks, the other does not.

I wanted to know how to avoid the name collision, is there a command in sql to assign a TEMP name for my VIEW every instance it is run.

The View name is unique, but if 2 or more users connect in at the same time, to run this report OR any report, Obviously this is not going to work.

Why dont i just write it all in crystals? Because im also writing the sql query for a WINDOWSPOS system, which will use the same query, If i do one in Crystals, and one in sql, we have proved it leaves too much room for error, and is twice as much work.

I hope that helps you understand, My question is still, is there a command to assign a temp name to a view, or else how do people aviod name collisions.

If you ahve any idea on how to do this better, i am always up for constr5uctive critasizm as i am only learning by trail and error how to do this.

Thanks
Jason

W E E K L Y     S U M M A R Y ;


DROP VIEW weekly_summary;

CREATE VIEW weekly_summary AS


  SELECT  Transdate AS DATE, stationnumber AS STATION,
         SUM((cashsale + chargesale + newlayby))                   AS 1_GROSS_SALES,
         SUM(discount)                                             AS DISCOUNT,
         SUM((cashsale + chargesale + newlayby) - discount)        AS 2_NETT_SALES,

             SUM(chargesale)                                            AS NETT_CHARGE_SALES,
             SUM(unpdnewlayby)                                          AS UNPAID_NEW_LAYBY,
             SUM(credNred)                                              AS CREDIT_NOTES_REDEEMED,
             SUM(tradein + giftvred)                                    AS TI_AND_GIFT_VOUCHERS_REDEEMED,

                SUM(paymtexistlayby)                                        AS PAYMENTS_FROM_OLD_LAYBY,
                SUM(credNiss)                                               AS CREDIT_NOTES_ISSUED,
                SUM(giftViss)                                               AS GIFT_VOUCHERS_ISSUED,

                    SUM(incvalue)                                                 AS OTHER_INCOME,
                    SUM(expvalue)                                                 AS EXPENSE,


   SUM(cash - changegiven)    AS CASH,
   SUM(cheque)                AS CHEQUE,
   SUM(cardvalue)             AS EFTPOS,
   SUM(bankcardvalue2)        AS AMEX,
   SUM(bankcardvalue3)        AS DINE,
   SUM(bankcardvalue4)        AS EMPTY,
   SUM(bankcardvalue5)        AS CNTRGIFT,

   SUM(eodclosefloat)         AS EOD_FLOAT


FROM summary
WHERE storenumber = 2
AND transdate BETWEEN '2010-02-01' AND '2010-02-07'
AND (transtypn =1 or 3 or 6 or 7)
GROUP BY DAY(transdate);



ALTER VIEW total_weekly_summary AS

    SELECT *,
            2_NETT_SALES                  -
            NETT_CHARGE_SALES             -
            UNPAID_NEW_LAYBY              -
            CREDIT_NOTES_REDEEMED         -
            TI_AND_GIFT_VOUCHERS_REDEEMED +
            PAYMENTS_FROM_OLD_LAYBY       +
            CREDIT_NOTES_ISSUED           +
            GIFT_VOUCHERS_ISSUED                                                 AS RECIEPTS,

            2_NETT_SALES                  -
            NETT_CHARGE_SALES             -
            UNPAID_NEW_LAYBY              -
            CREDIT_NOTES_REDEEMED         -
            TI_AND_GIFT_VOUCHERS_REDEEMED +
            PAYMENTS_FROM_OLD_LAYBY       +
            CREDIT_NOTES_ISSUED           +
            GIFT_VOUCHERS_ISSUED          +
            OTHER_INCOME -
            EXPENSE                                                              AS TOTAL_FUNDS,

            CASH                          +
            CHEQUE                        +
            EFTPOS                        +
            AMEX                          +
            DINE                          +
            EMPTY                         +
            CNTRGIFT                                                             AS AMOUNT_BANKED,


       (SELECT (SUM(1_GROSS_SALES))     from weekly_summary) AS GT_GROSS_SALES,
       (SELECT (SUM(DISCOUNT))          from weekly_summary) AS GT_DISCOUNT,
       (SELECT (SUM(2_NETT_SALES))      from weekly_summary) AS GT_NETT_SALES,

               (SELECT (SUM(NETT_CHARGE_SALES))             from weekly_summary) AS GT_NETT_CHARGESALES,
               (SELECT (SUM(UNPAID_NEW_LAYBY))              from weekly_summary) AS GT_UNPAID_NEW_LAYBY,
               (SELECT (SUM(CREDIT_NOTES_REDEEMED))         from weekly_summary) AS GT_CREDIT_NOTES_REDEEMED,
               (SELECT (SUM(TI_AND_GIFT_VOUCHERS_REDEEMED)) from weekly_summary) AS GT_TI_AND_GIFT_VOUCHERS_REDEEMED,

                        (SELECT (SUM(PAYMENTS_FROM_OLD_LAYBY))                                  from weekly_summary) AS GT_PAYMENTS_FROM_OLD_LAYBY,
                        (SELECT (SUM(CREDIT_NOTES_ISSUED))                                      from weekly_summary) AS GT_CREDIT_NOTES_ISSUED,
                        (SELECT (SUM(GIFT_VOUCHERS_ISSUED))                                     from weekly_summary) AS GT_GIFT_VOUCHERS_ISSUED,

                                (SELECT (SUM(OTHER_INCOME))        from weekly_summary)     AS GT_OTHER_INCOME,
                                (SELECT (SUM(EXPENSE))             from weekly_summary)     AS GT_EXPENSE,


      (SELECT (SUM(CASH))      from weekly_summary)    AS GT_CASH,
      (SELECT (SUM(CHEQUE))    from weekly_summary)    AS GT_CHEQUE,
      (SELECT (SUM(EFTPOS))    from weekly_summary)    AS GT_EFTPOS,
      (SELECT (SUM(AMEX))      from weekly_summary)    AS GT_AMEX,
      (SELECT (SUM(DINE))      from weekly_summary)    AS GT_DINE,
      (SELECT (SUM(EMPTY))     from weekly_summary)    AS GT_EMPTY,
      (SELECT (SUM(CNTRGIFT))  from weekly_summary)    AS GT_CNTRGIFT


  from weekly_summary;

CREATE TABLE final_weekly_summary AS

SELECT *, AMOUNT_BANKED - RECIEPTS as OVER_UNDER,
          (SELECT (SUM(RECIEPTS))       from total_weekly_summary)  AS GT_RECIEPTS,
          (SELECT (SUM(TOTAL_FUNDS))    from total_weekly_summary)  AS GT_TOTAL_FUNDS,
          (SELECT (SUM(AMOUNT_BANKED))  from total_weekly_summary)  AS GT_AMOUNT_BANKED

           from total_weekly_summary;

SELECT

      DATE,
      STATION,

            1_GROSS_SALES, GT_GROSS_SALES,
            DISCOUNT, GT_DISCOUNT,
            2_NETT_SALES, GT_NETT_SALES,

            NETT_CHARGE_SALES, GT_NETT_CHARGESALES,
            UNPAID_NEW_LAYBY, GT_UNPAID_NEW_LAYBY,
            CREDIT_NOTES_REDEEMED, GT_CREDIT_NOTES_REDEEMED,

            TI_AND_GIFT_VOUCHERS_REDEEMED, GT_TI_AND_GIFT_VOUCHERS_REDEEMED,
            PAYMENTS_FROM_OLD_LAYBY, GT_PAYMENTS_FROM_OLD_LAYBY,
            CREDIT_NOTES_ISSUED, GT_CREDIT_NOTES_ISSUED,

            GIFT_VOUCHERS_ISSUED, GT_GIFT_VOUCHERS_ISSUED,
            RECIEPTS, GT_RECIEPTS,

            OTHER_INCOME, GT_OTHER_INCOME,
            EXPENSE, GT_EXPENSE,
            TOTAL_FUNDS, GT_TOTAL_FUNDS,

    CASH, GT_CASH
    CHEQUE, GT_CHEQUE,
    EFTPOS, GT_EFTPOS,
    AMEX, GT_AMEX,
    DINE, GT_DINE,
    EMPTY, GT_EMPTY,
    CNTRGIFT, GT_CNTRGIFT,

    AMOUNT_BANKED, GT_AMOUNT_BANKED,
    OVER_UNDER, (SELECT (SUM(OVER_UNDER)) from final_weekly_summary) AS GT_OVER_UNDER,
    EOD_FLOAT


FROM final_weekly_summary;

Select allOpen in new window

I think thats pretty cool !!

You are correct, the View will not be the same for everyone,

With my code snippet above where and How do i insert
>create a table VIEW_CREATIONS ( requested_by varchar(100), requested_dt date, id int auto_increment );

so i can test it,

Thanks heaps for your time and help so far !

HI i asked a question, if you could show me, but since, i have sort of learnt how to implement what you have said.

I would like to keep this question open

Re my comment here

>why not CREATE the view once and for all?
>if no (which is what I presume):
>You HAVE to get a distinct view name for everybody creating a view.

Why i need a distinct name, is because mulitple users may run, but also, because i need parameters for all the where clauses, so the view will never be run with the same parameters.

I have crystal reports in which i want to put the views into, but i am not sure how, would  you have any ideas?

could you give me any advice on storing views, or temp tables, or stored procedures, what is the best way for storing but also calling and letting users enter parameters,  into crystal reports.

Thanks why i believe they have to have different view, SP, or table names?

Any furth help would be great!
Thanks Very much
 

Hi guys, ill just have to learn more and work it out, then ask the question again if i need to

Thanks.