• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

  • You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
  • You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
  • If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Previously on "SQL Client & editing a SQL statement"

Collapse

  • chef
    replied
    Originally posted by Spacecadet View Post
    You don't say

    I could have fixed that blind folded and with hands tied behind my back. As it, was it was so badly worded I didn't even bother asking
    i wont apply for that DB Admin role then..

    Leave a comment:


  • Spacecadet
    replied
    Originally posted by chef View Post
    i donta speaka dee lingo of DB..
    You don't say

    I could have fixed that blind folded and with hands tied behind my back. As it, was it was so badly worded I didn't even bother asking

    Leave a comment:


  • chef
    replied
    Originally posted by lilelvis2000 View Post
    Gosh. All you had to say was you had a union of two SQL statements and you needed to know which row came from which SQL statement.

    Yes, just add a constant column "A", "B" so on, to each statement

    glad you got it sorted. Must say I was completely bamboozled...but it doesn't take much to that...

    i donta speaka dee lingo of DB..

    Leave a comment:


  • lilelvis2000
    replied
    Gosh. All you had to say was you had a union of two SQL statements and you needed to know which row came from which SQL statement.

    Yes, just add a constant column "A", "B" so on, to each statement

    glad you got it sorted. Must say I was completely bamboozled...but it doesn't take much to that...

    Leave a comment:


  • chef
    replied
    ok, just FYI, i got it all sorted

    very simple when you know how.. Oracle SQL Developer for viewing and editing the SQL statement of the View form..

    as for the editing it was a simple addition of

    0 AS Direction

    in the top case statement and

    1 as Direction

    in the bottom one.. done

    thanks for the input though folks.

    Leave a comment:


  • where did my id go?
    replied
    TOAD from quest always used to be excellent for oracle work.

    Sorry I'm not clear what you're asking with respect to your query. Give us an example?

    Leave a comment:


  • chef
    replied
    ok so here is the SQL statement, all i need to do is somehow identify if the join direction is case a -> case b or case b -> case a

    i was thinking to add a fixed value column in each case statement such as direction = 1 for the upper case statement and direction = 0 so that i can then flag it when i view the results. is that possible?

    the end goal is to colour code the background in the table results list for those results that are case a -> case b differently from case b -> case a

    Code:
    CREATE OR REPLACE VIEW ACTIVITY AS
    select
    'ID1'||SUBSTR(con.zzSys_RequestID,4) as zzSys_RequestID,
    con.Concat_ID_1 AS Concat_ID,
    con.Concat_Status,
    (CASE con.Concat_Type WHEN 'Derive' THEN 'Derived tickets' WHEN 'Relation' THEN 'I belong to' WHEN 'Sequence' THEN 'Successor' WHEN 'ProcessSequence' THEN 'Process successor' WHEN 'Process' THEN 'Process tasks' ELSE con.Concat_Type END) AS Concat_Type,
    tic.Ticket_GUID,
    tic.Ticket_ID,
    tic.Ticket_Typ,
    Tic.Ticket_TypAttribut,
    tic.zzSys_CreateDate,
    tic.Ticket_Owner,
    tic.Ticket_ShortDescription,
    tic.Ticket_Priority,
    tic.Ticket_StatusRB,
    tic.Ticket_AssignedToGroup,
    tic.Ticket_AssignedToIndividual,
    tic.Ticket_Formular,
    tic.Ticket_View,
    tic.Scheduling_Start,
    tic.Scheduling_End,
    tic.DYG_ClientGroup,
    DYG_ClientID,
    tic.DYG_ClientVisibilityGroup
    from cism_bas_ticketconcat con, cism_bas_data_ticket tic
    where con.Concat_ID_2=tic.Ticket_GUID
    union
    all
    select
    'ID2'||SUBSTR(con.zzSys_RequestID,4) as zzSys_RequestID,
    con.Concat_ID_2 AS Concat_ID,
    con.Concat_Status,
    (CASE con.Concat_Type WHEN 'Derive' THEN 'Derived from ticket' WHEN 'Relation' THEN 'Belong to me' WHEN 'Sequence' THEN 'Predecessor' WHEN 'ProcessSequence' THEN 'Process predecessor' WHEN 'Process' THEN 'Process master' ELSE con.Concat_Type END) AS Concat_Type,
    tic.Ticket_GUID,
    tic.Ticket_ID,
    tic.Ticket_Typ,
    Tic.Ticket_TypAttribut,
    tic.zzSys_CreateDate,
    tic.Ticket_Owner,
    tic.Ticket_ShortDescription,
    tic.Ticket_Priority,
    tic.Ticket_StatusRB,
    tic.Ticket_AssignedToGroup,
    tic.Ticket_AssignedToIndividual,
    tic.Ticket_Formular,
    tic.Ticket_View,
    tic.Scheduling_Start,
    tic.Scheduling_End,
    tic.DYG_ClientGroup,
    DYG_ClientID,
    tic.DYG_ClientVisibilityGroup
    from cism_bas_ticketconcat con, cism_bas_data_ticket tic
    where con.Concat_ID_1=tic.Ticket_GUID
    As i see it if i had 3 related records a -> b -> c

    this select statement would pull the results of result 1 = a -> b and result 2 = b->c
    what i need to do is identify if its the left or right part of the relationship i.e "from a" or "to b", the flagging and colour coding is the easy, its getting the SQL statement in a form so that i can flag it is the problem.
    Last edited by chef; 29 August 2008, 12:29.

    Leave a comment:


  • Fred Bloggs
    replied
    I've used MS Access on live Oracle db's before now. It's scary when you're not 100% sure what you're doing!

    Leave a comment:


  • chef
    replied
    thanks spacecadet,

    you were beaten in replies by a friend who recommended Squirrel, it's installed, have managed to install the oracle thin driver, have logged on and have found the view I need to edit, now the fun part.. how to view the view SQL statement and edit it.. manual reading time and a crash course in squirrel / SQL i think..

    haha have found just the thing here

    Chef in < learning on the fly > mode

    Leave a comment:


  • Spacecadet
    replied
    i recommend this one to clients who want a quick and dirty SQL viewer:
    http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp

    Leave a comment:


  • chef
    started a topic SQL Client & editing a SQL statement

    SQL Client & editing a SQL statement

    ok, I know nothing about SQL and to solve this problem I have I need to edit a SQL statement.. the db admin is on holiday but i have full admin rights to the test server so if i screw up its no big deal

    In short I have 2 case statements joined with a union, what i need to do is add is a simple value of 1 in the first statement and 0 in the second in order for me to use my system to establish whether the join direction is A -> B or B -> A.

    I have 2 questions:

    1) can somebody recommend an easy to use SQL viewer

    2) is adding a fixed value in the case statements as simple as value 1 value 0? maybe before I ask q2 I should atleast try and work this out for myself
Working...
X