Create a view from a SELECT Statement 1

[WizZerD]

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

 

[WizZerD]

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

 

[readingcoops]

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.

 

[WizZerD]

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

 

[readingcoops]

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.

 

[WizZerD]

Great, Now I am getting results ... Thanks !!!