i have a query which goes like this:-
select a.col1,b.col2,c.col2
from tab1 a,tab2 b,tab3 c
where
a.col1 *= b.col1 and
a.col2 *= c.col2 and
b.col3 = c.col3
how do I write this query in SQL 92?
also can we replace *= with =* by interchanging the participating tables as in
"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies View RelatedHi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies View RelatedI getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
SELECT distinct whse_zone_id, bay_id
From prod_geneology WHERE whse_zone_id in ('SH','CU','SG')
START WITH unit_id = 'BL7B230811'
CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Any help would be grateful.
Thanks
I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC
Hi,
I'm using a database through ODBC in an application designed on Macromedia Drumbeat 2000, and use the following query for one of the segments:
SELECT DISTINCTROW Books.ISBN, Books.Title, Books.Category, Books.Description, Books.Price, Books.Pubdate, Books.Keywords, Books.UnitWeight, Authors.au_lastname, Authors.au_firstname, Authors.au_midname, Publishers.pub_name, Books.Pub_No
FROM Publishers INNER JOIN (Authors INNER JOIN Books ON Authors.au_id = Books.Au_No) ON Publishers.pub_id = Books.Pub_No
It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:
Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.
Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.
Thanks...
I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = SUBSTR('85',1,2) AND
TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND
CSUM_PROPRD BETWEEN '200610' AND '200611' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_COMP_CODE = INVM_COMP_CODE(+) AND
CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND
(INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE)
FROM ACC_CUST_SUM as CSUM
INNER JOIN ACC_CUST_ITEMS as CITM
ON
CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND
CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM
ON
CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND
CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND
(INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND
CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND
LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND
CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
I have a string like below
DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'
i want to convert the string into rows like
AAAAAA
KKKKK
LLLL
AAAA
This query from access does not work in sql server 2000. How do I write this in sql to run?
SELECT First(tri_ProcMast.ddesc) AS FirstOfddesc, tri_ProcMast.proccd
FROM tri_ProcMast
GROUP BY tri_ProcMast.proccd;
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
View 1 Replies View RelatedI am struggling rewriting my query from MS Access' IIF, Then to SQLServers TSQL language. I am hoping some one can give me someguidance. I believe I have the first portion of the query correct butdo believe this requires a "NESTED" argument. This is where I amlost.My Original MS ACCESS Query reads--SELECT DISTINCTROW REGION_TRAFIC.*,IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTRA_VENDOR1])))),IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTER_VENDOR1]))))) AS CPM,[CPM]*[MOU] AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESC;I have tried to re-write this in SQL SERVER as --SELET DISTINCT REGION TRAFIC.*,CASEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTRA_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTRA_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTRA_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTRA_VENDOR1ELSEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTER_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTER_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTER_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTER_VENDOR1ENDAS CPMCPM*MOU AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESCMy challenge is the Case portion of the query and the nesting! I amnot sure if I have the correct syntax or even chose the correctargument for my purpose.Any guidance is appreciated.
View 9 Replies View RelatedHow would I convert this query to ANSI 92 standard?
select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)
Hi ,
I have a question for all , please help me out.
the question is : i have a query in Oracle like this
select count(*), to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') from ISL_USERS
group by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy')
order by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy');
i want to convert this into SQLserver but in SQL server equlent function for TO_CHAR is Datename ..when i have given this i am getting error . please give me the tip on this issue .
thanks
shekhar
--------------------------------------------------------------------------------
|
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
I have the following query:
(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')
I need to convert this into a case statement.
I tried various ways but did not get the result that I was after
Thanks,
Laura
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
THE_Big_One1.STC_SCAN,
[Code] ...
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
what all these things I need to take when I convert it to as a SQL query for report generation?
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
[code]....
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
View 4 Replies View RelatedI am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test]
(
@numberOfMonthsint
)
AS
BEGIN
SET NOCOUNT ON
set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like:
- TRY/CATCH block
- Will not let me use my input parm (@numberOfMonths int)
- When I hard code in my input parm (select @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1
SELECT @Count'
EXEC(@S)
I am trying to execute the above query but it is throwing me an error.
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
I have a select statement like this:
select IsVerified from AppForm
the IsVerified returns 'True' ...can I convert that value to 'Yes' by using some sort of function?
I have an anonymous PL/SQL block that I'd like to Convert into T-Sql and im completly stumped on 2 things:
What is the equvilent of a Cursor and how do I declare one?
How do I declare an anonymous block?
my code wrote:
Declare
-- Type Declarations
Type ct_UConstr_Cur is REF CURSOR;
v_Begin Integer := 0;
v_End Integer := 0;
v_PageSize Integer :=0;
c_Data ct_UConstr_Cur;
Begin
v_PageSize := :PageSize;
v_Begin := ( v_PageSize * :PageNumber) - 1;
v_End := v_Begin + v_PageSize;
Open c_Data For
Select * from (
Select A, B, C, ROWNUM as RN from Foo;
) where RN >= v_Begin AND RN < v_End;
:cur := c_Data;
END;
/
Does any one know of a tool SQL 7 has for doing this kind of conversion?
View 2 Replies View Relatedok i am very very new at this, ive downloaded the SQL 2005 trial and i can not seem to figure this out. I have a .xls i want to make a active sql page out of. please please help me with this
the .xls is here http://www.mediafire.com/?5jtgzcxb232
feel free to email me @
bevanglynn@gmail.com
thanks
Hi there,
Here at my work(in Germany) i have a big problem:
Once a week there is a sqlscript, which should run and store data to a txt file.
There is a tablle in which imagedata is stored in RAW format(that one with small boxes, Qmarks and dots...).
Now i want to export it to the txtfile as HEX, so i can decode it by php further.
With the thumbnail, also stores there everything is fine, the SQL looks that way:
SELECt hex(thumbnail) FROM picTable where id_pic=54985
The Data is then displayed as HEX. But the standart picture is to big. When i use the statement:
SELECt hex(bigpic) FROM picTable where id_pic=54985
i got the error message:
An error occurred while running the query.
The data type, length, or value of argument 1 of HEX is invalid.
(SQL code = -171, SQL state = 42815)
After some research i found that the maximum lenght is 16 336 bytes for the HEX-operation. The picture is nearly 30 000 bytes... :shocked:
Have anyone an idea how to fix that?
I am not able here to accsess to the database direct...
I have been working for days on it.