[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1018
  • Last Modified:

Cross join not working

Hi,
 
 I'm trying the below code, but it throws an error.
 Here is the code:
SELECT DISTINCT LEGAL_CD,LEGAL_C
                             ,COUNTY_CD,LDSS
                    
FROM
  (SELECT PICKLIST_VALUE_CD LEGAL_CD,VALUE_TX LEGAL_C
     FROM TB_PICKLIST_VALUES
    WHERE PICKLIST_VALUE_CD IN ('6038','8142')
	  AND PICKLIST_TYPE_ID = 107
  ) LG

 CROSS JOIN
  (SELECT PICKLIST_VALUE_CD COUNTY_CD,VALUE_TX LDSS
     FROM TB_PICKLIST_VALUES
	WHERE PICKLIST_TYPE_ID = 104
  ) CY

Open in new window


and here is the error.
Exception: DBD, [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token "CROSS" was found following "YPE_ID = 107    ) LG".  Expected tokens may include:  "<space>".  SQLSTATE=42601
State: 42601

Open in new window


Can someone kindly help to get it to work?
0
pvsbandi
Asked:
pvsbandi
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi pvsbandi,

For most of the DB2 implementations, CROSS JOIN is not a supported context.

Are you really trying to do a full cross join here, or generate a cartesian based on a key?


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:


I believe that you can produce a full cross join with the SQL below.  (You can on some DB2 implementations.)

Be careful though.  If you have a lot of rows, the cartesian product can overwhelm your system.  10,000 rows in each table will produce a 100,000,000 row result set.


Kent

SELECT *
FROM table1 t1
INNER JOIN table2 t2
  ON 1=1

Open in new window

0
 
momi_sabagCommented:
the equivelent of cross join in db2 is to not specify any join condition:

SELECT DISTINCT LEGAL_CD,LEGAL_C
                             ,COUNTY_CD,LDSS            
FROM
  (SELECT PICKLIST_VALUE_CD LEGAL_CD,VALUE_TX LEGAL_C
     FROM TB_PICKLIST_VALUES
    WHERE PICKLIST_VALUE_CD IN ('6038','8142')
        AND PICKLIST_TYPE_ID = 107
  ) LG
,
  (SELECT PICKLIST_VALUE_CD COUNTY_CD,VALUE_TX LDSS
     FROM TB_PICKLIST_VALUES
      WHERE PICKLIST_TYPE_ID = 104
  ) CY
0
 
pvsbandiAuthor Commented:
Thank You Both!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now