SQL Client & editing a SQL statement

[chef]

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

 

[Spacecadet]

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

 

[chef]

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

 

[Fred Bloggs]

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!

 

[chef]

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

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.

 

[where did my id go?]

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?

 

[chef]

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.

 

[lilelvis2000]

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...

 

[chef]

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..

 

[Spacecadet]

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