Mysql Temporary Views - NAME COLLISION

I have a VIEW called TABLE_1

It's always called TABLE_1 because my command is

CREATE VIEW TABLE_1 AS,

What if 2 people want to run this VIEW at the SAME time
The first one works, the second one fails because the VIEW NAME already EXISTS

Is there a command to give a VIEW a temporary VIEW NAME everytime it is RUN?

Thankyou.


LVL 8
kingjelyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flytox06Connect With a Mentor Commented:
create a view generical enough to satisfy mysql & crystal usage.

depending on who is using it and where, up to you to add custom WHERE criterias around the already built views.

1) CREATE VIEW <my_generic_view> on MySQL
2) SELECT <yourfields> FROM <my_generic_view> WHERE <filters_depending_the_users_etc...>

thinking about a method which CREATE VIEW everytime it is called is not correct and you are certainly wrong thinking about such a solution. The solution is about different usage, not different views entity.
0
 
kingjelyAuthor Commented:
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!)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kingjelyAuthor Commented:
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;

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
so, the question is:
is the view the same for everybody?

if yes:
  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.
  there is no "temp view name" in mysql (or any other db I know), only temp tables ...

to get a "unique" name, I could imagine this technique:
create a table VIEW_CREATIONS ( requested_by varchar(100), requested_dt date, id int auto_increment );

so, the application that has to create and run the view first inserts a row into that table, fetching back the auto-generated if value.
and then, create the view with the id appended into it's name...
after the view has been run, drop the view

and in the backend, run a cleanup process that drops all the views that still exist which have been requested more than 24 hours ago...

what do you think?
0
 
kingjelyAuthor Commented:

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 !
0
 
kingjelyAuthor Commented:
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
 
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>but also, because i need parameters for all the where clauses, so the view will never be run with the same parameters.
then, don't create a view, in short.
0
 
kingjelyAuthor Commented:
Hi guys, ill just have to learn more and work it out, then ask the question again if i need to

Thanks.
0
All Courses

From novice to tech pro — start learning today.