Hi Team,
   I am trying to do an export of specific fields from a table mixed with fixed data.

{sql}
SCRIPT
BEGIN
execute immediate 'export table Parts to "Export.csv" in store "Exports"
(
PartNo,
Description,
"B" as Buy,
''S'' as Sell,
''I'' as Inventory,
11800 as AssetAcct,
41770 as IncomeAcct,
54000 as COSAcct,
'''' as ItemPicture,   /* empty field */
Description,
''X'' as UDOS,
'''' as CL1,      /* empty field */
CostPrice)
where blocked = False';
END
{sql}

   If I put B into double single quotes it doesn't like it.  With B in double quotes it seems to like it but doesn't like
the as after it.

   Is this doable in a script at all?

   If not, what is my alternative please?

Regards & TIA,
Ian

Ian


Never having tried it before I've just tested and it looks as though you can only export columns not insert a constant as you're trying to do. I can see two solutions:


1. create an intermediate temporary or memory table
2. create some computed columns for your table

unfortunately the WHERE clause isn't allowed either so a temporary or in-memory table is the way to go

Roy Lambert

Hi Roy,
   Or, I could geberate it OK in a Query and then simply scan through the Query and fill a filestream.
   This is what I have now done and it works fine abd quickly.  One has to remember that you can'y have commas in the
text fields, causes havoc when read into excel.

Ian

Ian


>    Or, I could geberate it OK in a Query and then simply scan through the Query and fill a filestream.
>    This is what I have now done and it works fine abd quickly. One has to remember that you can'y have commas in the
>text fields, causes havoc when read into excel.

Yup that'll work, or you could use a view

Roy

Ian,

Can I see the structure of the Parts table please?

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

> Ian,
>
> Can I see the structure of the Parts table please?

Hi Fernando,
   Sure..

CREATE TABLE "Parts"
(
"BLOCKED" BOOLEAN DEFAULT false NOT NULL,
"PartNo" VARCHAR(20) COLLATE "ANSI",
"Description" VARCHAR(70) COLLATE "ANSI",
"SupplierCode" VARCHAR(2) COLLATE "ANSI",
"SupplierPrice" DECIMAL(19,4) DEFAULT 0.00 NOT NULL,
"UOS" VARCHAR(2) COLLATE "ANSI",
"BIN" VARCHAR(20) COLLATE "ANSI",
"COSTPRICE" DECIMAL(19,4) DEFAULT 0.00 NOT NULL,
"SELLPRICE" DECIMAL(19,4) DEFAULT 0.00 NOT NULL,
"DealerPrice" DECIMAL(19,4) DEFAULT 0.00 NOT NULL,
"WTYQTY" SMALLINT DEFAULT 0 NOT NULL,
"WTYMINQTY" SMALLINT DEFAULT 0 NOT NULL,
"WTYMAXQTY" SMALLINT DEFAULT 0 NOT NULL,
"COYQTY" SMALLINT DEFAULT 0 NOT NULL,
"COYMINQTY" SMALLINT DEFAULT 0 NOT NULL,
"COYMAXQTY" SMALLINT DEFAULT 0 NOT NULL,
"UNLIMITED" BOOLEAN DEFAULT false NOT NULL,
"APPLIES_TO" VARCHAR(50) COLLATE "ANSI",
"AccountNo" INTEGER,
"RESERVED" DECIMAL,
"SUPPLIER1" INTEGER,
"SUPPLIER2" INTEGER,
"SUPPLIER3" INTEGER,
"BusCode" CHAR(1) COLLATE "ANSI" NOT NULL,
"RtnQty" SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT "PARTNO" UNIQUE ("PartNo", "BusCode")
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

You could crate a view and add your constants, do calculations and use a WHERE clause.

Richard

Ian,

As already suggested you can both use a View or a Temporary Table.
In my opinion a temporary table would be better because Views are persistent and temporary tables are not, so they are deleted when the session closes if something goes wrong. Also, Temporary Tables are only visible in the same session that created them and thats a huge advantage in a multi-user environment.

Here is the script:

SCRIPT
BEGIN
  DECLARE N INTEGER;
  
  -- Delete the temporary table if it exists
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO ? FROM Information.TemporaryTables WHERE Name = ''tmpParts''' USING N ;
  IF N <> 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE tmpParts' ;
  END IF;

  -- Create temporary table to export
  EXECUTE IMMEDIATE '
    CREATE TEMPORARY TABLE "tmpParts" AS
      (
       SELECT
         PartNo,
         Description,
         ''B'' AS Buy,
         ''S'' AS Sell,
         ''I'' AS Inventory,
         11800 AS AssetAcct,
         41770 AS IncomeAcct,
         54000 AS COSAcct,
         '''' AS ItemPicture,
         Description,
         ''X'' AS UDOS,
         NULL AS CL1,
         CostPrice
       FROM
         Parts
       WHERE
         Blocked = FALSE
      )
    WITH DATA
  ';

  -- Export data
  EXECUTE IMMEDIATE 'EXPORT TABLE tmpParts TO "Export.csv" IN STORE "Exports" ' ;
  EXECUTE IMMEDIATE 'DROP TABLE tmpParts' ;
END

--
Fernando Dias
[Team Elevate]

Hi Fernando,
   That's excellent.  Thank you.
Regards,
Ian

Hi All,
   Thank you for your inputs.
   I ended up having to do it via a Query & FileStream.  Turns out MYOB doesn't like .csv file with commas in the
description text/field.  Should have realised right up front.

Regards,
Ian