INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Create a view from a SELECT Statement

Create a view from a SELECT Statement

(OP)
Hi I have run the following Select statement and got a result:
SELECT @LEFT(EPISOD.PRODNR,2)||'-'||@MID(EPISOD.PRODNR,3,2)||'/'||@RIGHT(EPISOD.PRODNR,4) AS Prodnr,EPISOD.EPISODKOD AS Avs,UPPSKICK.UPPSKICKSDATUM AS Datum,BAND.BANDNR AS Matid, PROD.SVENSK AS Titel,@IF(@EXACT(TILLAGGSTYPID,10),'1','') AS Overs, @IF(@EXACT(TILLAGGSTYPID,11),'1','') AS PrgmTxt,BEST.MANUELL AS Man, BEST.OPPEN AS Opp, BEST.DOLD AS Dold,UPPSKICKSRAD.ANMARKNING,ANVANDARE.SIGN,KUND.NAMN AS Kanal
FROM ANVANDARE, UPPSKICK, UPPSKICKSRAD, EPISOD, BAND, PROD, BEST, UTLAGGNING, KUND WHERE
    (ANVANDARE.ID = UPPSKICK.ANVANDARID) AND
    (UPPSKICK.ID=UPPSKICKSRAD.UPPSKICKSID) AND
    (EPISOD.ID = UPPSKICKSRAD.EPISODID) AND
    (EPISOD.EPISODKOD = BAND.EPISODKOD) AND
    (EPISOD.PRODNR = BAND.PRODNR) AND
    (PROD.PRODNR = BAND.PRODNR) AND
    (UPPSKICKSRAD.BESTID = BEST.ID) AND
    (BEST.ID = UTLAGGNING.BESTID) AND
    (BEST.SANDENHET = KUND.ID) AND
    (PROD.SVENSK Is Not Null) AND
    (ANVANDARE.SIGN='123456') AND
    (UPPSKICK.UPPSKICKSDATUM='7/18/2003') AND
    (KUND.NAMN='JOEBLOGGS')
ORDER BY EPISOD.PRODNR;

If I add the Line:
CREATE VIEW TestView AS
above the SELECT statement, I get the following error:

ORDER BY EPISOD.PRODNR
^
Error: 00134 SQL CNO ERROR.SQL NOT FOUND LOOKING UP ERROR 00901

Any ideas ?
/Nigel

RE: Create a view from a SELECT Statement

(OP)
If I remove the ORDER By line, I get the following error:
SELECT @LEFT(EPISOD.PRODNR,2)||'-'||@MID(EPISOD.PRODNR,3,2)||'/'||@RIGHT(
     ^
Error: 00134 SQL CNO ERROR.SQL NOT FOUND LOOKING UP ERROR 00330

Why does it fail on the third set of ||´s ??? not the first?

btw.. SQLBase V 7.5.1

Thanks again for any help.
/Nigel

RE: Create a view from a SELECT Statement

If you check the help for the create view syntax it says you cannot use order by in a create view statement.  Once the view is created then select - order by to get the desired result.

RE: Create a view from a SELECT Statement

(OP)
Thanks readingcoops.
I have removed the order by line, but am still getting:
Error: 00134 SQL CNO ERROR.SQL NOT FOUND LOOKING UP ERROR 00330
for the third set of || which I use to build up a string.
If I remove the column with the ||, I get an error at each of my @IF statements.
Doe this mean that you can't have calculated fields in a view ?
/Nigel

RE: Create a view from a SELECT Statement

Morning wizzard, at least it is here.  You have got the syntax of the create slightly wrong here I think.  You need to do a

CREATE VIEW viewname (col1, col2, col3, ....)
AS SELECT ........

For example my statement here

CREATE VIEW GDS (NUMBER, SEG) AS SELECT CSH.TICKET_NO,
@IF(@EXACT(CSI.SEGMENT_NO, 1), 1, 7 )
FROM CON_SALES_HDR CSH, CON_SALES_ITIN CSI WHERE CSH.TICKET_NO=CSI.TICKET_NO

works correctly.  Good luck.

RE: Create a view from a SELECT Statement

(OP)
Great, Now I am getting results ... Thanks !!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close